How do I insert the formula into the cell when the formula keeps changing with an increase in row?

Question

I have entered these formula in the second row of the Pth column:

=(COUNTIF(A$1:A1,A2)=0)+(COUNTIF(B$1:B1,B2)=0)+(COUNTIF(F$1:F1,F2)=0)

When I drag it to the third row of the Pth column, it gets like this:

 =(COUNTIF(A$1:A2,A3)=0)+(COUNTIF(B$1:B2,B3)=0)+(COUNTIF(F$1:F2,F3)=0)

This is what I do manually. How do I make it using VBA? I have tried in the way below.

cells(Count,"M").formula= "=(COUNTIF(A$1:A1,A2)=0)+(COUNTIF(B$1:B1,B2)=0)+(COUNTIF(F$1:F1,F2)=0)"

But it's not working. It's not changing from

"=(COUNTIF(A$1:A1,A2)=0)+(COUNTIF(B$1:B1,B2)=0)+(COUNTIF(F$1:F1,F2)=0)"

to

"=(COUNTIF(A$1:A2,A3)=0)+(COUNTIF(B$1:B2,B3)=0)+(COUNTIF(F$1:F2,F3)=0)"

How do I insert the formula into the cell when the formula keeps changing with an increase in row?

You could copy/paste the formula directly in VBA; instead of setting the formula to a text value, copy it from a cell in which it's correct, and paste in the target cell. — Joubarc, Aug 22 '11 at 08:30

score 5 · Accepted Answer · answered Aug 22 '11 at 10:17

5

You can do this in one line:

range("P2").Copy Destination:=range("P3:P10")

No need for variables, loops, anything!

answered Aug 22 '11 at 10:17

Gaijinhunter

grantnz · Answer 2 · 2011-08-22T09:57:25.640

2

As suggested by Joubarc

Cells(2, "P").Copy
For Row = 3 To 10
     Cells(Row, "P").Select
     ActiveSheet.Paste
 Next Row

edited Aug 22 '11 at 09:57

answered Aug 22 '11 at 09:07

grantnz

you don't need to select the cells to copy `Cells(2, "P").Copy` should work – JMax Aug 22 '11 at 09:39
Good point. Changed select and then copy to direct cell copy. – grantnz Aug 22 '11 at 09:57
+1 for helpful answer. You can do this in 1 line, though (I posted my solution). – Gaijinhunter Aug 22 '11 at 10:23

2 Answers2