Trouble Creating Formula in a Macro

[Pam]

Hello:

I am trying to creatre formulas in a macro. The sheet may have 1 to n
rows.
I am using the match formula to find the difference in two sheets.
The problem I am running into is how to increment the counter to put
the formula in when I don't know how many rows will be in the
spreadsheet to put the formula in.

=MATCH(C2,'Update'!$B$1:$B$65213,0)
C2 will change for each row, example C2, C3, C4, C5 etc.
It works fine if I put this into the spreadsheet.

Does anyone know how I can implement this into my code for each row
in
the spreadsheet?



Thank You,
Pamela

 

[Alan]

Hi Pam,

Range("A1:A" & Cells(Rows.Count, "D").End(xlUp).Row).FillDown

Change "A1:A" to the correct cell and column that the formula resides.
Change "D" to the column that you want to use as the data. This will fill
the formula to the last cell in the column chosen.

Regards,

Alan

 

[JE McGimpsey]

XL will automatically adjust the cell references if you create the
formulas at once:

Dim n As Long
n = 100
Range("C2").Resize(n, 1).Formula = _
"=MATCH(C2,'Update'!$B$1:$B$65213,0)"