当您有保存值的变量时插入 COUNTIF 公式 [英] Insert COUNTIF formula when you have variable holding the value

问题描述

考虑:

 Cells(2, "Q").Formula = "=COUNTIF(P$1:P1,P2)=0"

当我有一个保存值的变量时，如何插入这些公式?

How do I insert these formulae when I have a variable holding a value?

我必须在 3550 行和 4000 行有时开始公式.这取决于数据.好吧，当我用谷歌搜索它时，我什么也没找到.他们都使用相同的公式，但我需要在特定单元格中插入 countif 函数，可能是 300 或 500 - 这取决于变量值.

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 countif function at a particular cell, may be at 300 or 500 - it depends in the variable value.

 Cells(count,"Q").formula = "=COUNTIF(cells($1,"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?

推荐答案

试试这个:

'case 1: if you know the destination range
Range("Q2").Formula = "=COUNTIF(P$1: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)

参考:Issun 的回答 堆栈溢出问题如何将公式插入到单元格中当公式随着行数的增加而不断变化时?.

Reference: Issun's answer to Stack Overflow question How do I insert the formula into the cell when the formula keeps changing with increase in row?.

这篇关于当您有保存值的变量时插入 COUNTIF 公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！