Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting custom formula
I am trying to paste a custom built formula in a variable location with a VBA
routine. I have to find the first open row at the end of a list, find the row number and build a formula using a reference from that row number and paste it into a cell on that row. Specifically I need to build a formula that looks something like this: assuming the first row found is 10 and I want to test a value in cell A10 (that I just pasted there with the same routine) I have a referenced variable in $C$1, and I want to paste this new formula in C10. The formula looks something like this =if(C$1$A10,"Yes","No"). My list keeps getting longer so I need a dynamic way to build a new formula for each row (hopefully a simple formula that runs fast). Can anyone help with a bit of code for this? Thanks in advance, RDW |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting custom formula
Sub PutInFormula()
Dim lastrow as Long lastrow = cells(rows.count,"C").End(xlup).row + 1 cells(lastrow,"C").formula = _ "=if(C$1$A" & lastrow & ",""Yes"",""No"")" End sub -- Regards, Tom Ogilvy "RD Wirr" wrote: I am trying to paste a custom built formula in a variable location with a VBA routine. I have to find the first open row at the end of a list, find the row number and build a formula using a reference from that row number and paste it into a cell on that row. Specifically I need to build a formula that looks something like this: assuming the first row found is 10 and I want to test a value in cell A10 (that I just pasted there with the same routine) I have a referenced variable in $C$1, and I want to paste this new formula in C10. The formula looks something like this =if(C$1$A10,"Yes","No"). My list keeps getting longer so I need a dynamic way to build a new formula for each row (hopefully a simple formula that runs fast). Can anyone help with a bit of code for this? Thanks in advance, RDW |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting custom formula
That works perfectly. Thanks Tom!
"Tom Ogilvy" wrote: Sub PutInFormula() Dim lastrow as Long lastrow = cells(rows.count,"C").End(xlup).row + 1 cells(lastrow,"C").formula = _ "=if(C$1$A" & lastrow & ",""Yes"",""No"")" End sub -- Regards, Tom Ogilvy "RD Wirr" wrote: I am trying to paste a custom built formula in a variable location with a VBA routine. I have to find the first open row at the end of a list, find the row number and build a formula using a reference from that row number and paste it into a cell on that row. Specifically I need to build a formula that looks something like this: assuming the first row found is 10 and I want to test a value in cell A10 (that I just pasted there with the same routine) I have a referenced variable in $C$1, and I want to paste this new formula in C10. The formula looks something like this =if(C$1$A10,"Yes","No"). My list keeps getting longer so I need a dynamic way to build a new formula for each row (hopefully a simple formula that runs fast). Can anyone help with a bit of code for this? Thanks in advance, RDW |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
retain custom date format "text" when pasting or reformatting data | Excel Discussion (Misc queries) | |||
custom field with custom formula in pivot table! | Excel Worksheet Functions | |||
Custom error bars bug -- copy pasting chart does not update refere | Charts and Charting in Excel | |||
Pasting on Filtered Data Sheets without pasting onto hidden cells | Excel Discussion (Misc queries) | |||
Pasting formula as text | Excel Programming |