vba 当您有保存值的变量时插入 COUNTIF 公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7172222/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert COUNTIF formula when you have variable holding the value
提问by niko
Consider:
考虑：
Cells(2, "Q").Formula = "=COUNTIF(P:P1,P2)=0"
How do I insert these formulae when I have a variable holding a value?
当我有一个保存值的变量时，如何插入这些公式？
I have to start the formula at 3550 row and 4000 row somtimes. It depends on the data. Well, when I googled it, I found nothing. They all used the same formula, but I need to insert the countiffunction at a particular cell, may be at 300 or 500 - it depends in the variable value.
我必须在 3550 行和 4000 行有时开始公式。这取决于数据。好吧，当我用谷歌搜索它时，我什么也没找到。他们都使用相同的公式，但我需要countif在特定单元格中插入函数，可能是 300 或 500 - 这取决于变量值。
Cells(count,"Q").formula = "=COUNTIF(cells(,"P"):cells(count-1,"P"),cells(count,"P))=0"
Is this the way? Well, I tried in some ways but it's ending up higlighting the line with the red colour. How do I insert these formulae with a variable?
这是方法吗？好吧，我尝试了一些方法，但最终以红色突出显示了线条。如何使用变量插入这些公式？
回答by JMax
Try this:
尝试这个：
'case 1: if you know the destination range
Range("Q2").Formula = "=COUNTIF(P:P1,P2)=0"
Range("Q2").Copy Destination:=range("Q3:Q500")
'case 2: if the destination range is a variable
'minRow is a Long >= 1
Range("Q" & minRow + 1).Formula = "=COUNTIF(P$" & minRow & ":P" & minRow & ",P" & minRow + 1 & ")=0"
Range("Q" & minRow + 1).Copy Destination:=Range("Q" & minRow + 1 & ":Q" & maxRow)
Reference: Issun's answerto Stack Overflow question How do I insert the formula into the cell when the formula keeps changing with increase in row?.
参考：Issun对 Stack Overflow 问题的回答当公式随着行数的增加而不断变化时，如何将公式插入到单元格中？.
