1

Consider:

 Cells(2, "Q").Formula = "=COUNTIF(P$1: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 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?

Community
  • 1
  • 1
niko
  • 9,285
  • 27
  • 84
  • 131

1 Answers1

3

Try this:

'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)

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?.

Community
  • 1
  • 1
JMax
  • 26,109
  • 12
  • 69
  • 88
  • .Hey its not going to start from Q3 may be from Q300 I mean ,U have given for the end of the row where to end the copying but not for the start row I mean where to start .the starting row wont be always 3 might be 300 somtimes. Please update ur answer – niko Aug 24 '11 at 08:12
  • 1
    @niko: this was an example, you could have adapted it by yourself. Anyway, i've edited my answer – JMax Aug 24 '11 at 08:15
  • but what about the countif The question was about countif not just destination! – niko Aug 24 '11 at 08:25
  • @Issun thanks for helping me with your answers and I got one last issue about excel vba so Issun be ready for my last question on excel VBA if these question is solved then My project is finished ! thank god please answer my question. – niko Aug 24 '11 at 11:03