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

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

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