Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble Creating Formula in a Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble Creating Formula in a Macro
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 "Pam" wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble Creating Formula in a Macro
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)" In article . com, "Pam" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble creating table | Excel Discussion (Misc queries) | |||
Trouble creating a chart | Excel Programming | |||
Trouble creating DSN-less connection | Excel Programming | |||
trouble w/ creating a VBA function that uses an enum in it's parameters | Excel Programming | |||
having trouble creating chart to show monthly totals | Charts and Charting in Excel |