Insert variable cell in the formula

[Guest]

Hi,

I try to insert a formula to multiply two rows, like A1*C1, A2*C2....., how
can I do that by using a FOR loop in the marco?

Thanks.

Gian

 

[Tom Ogilvy]

You don't need to loop
Assume you want the formula in column D

set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
rng.offset(0,3).Formula = "=A1*C1"

 

[Guest]

Thanks Tom.

I just curious, it's possible to put the variable in the formula, like
"=chr(65)&1 * chr(67)&1"

 

[Tom Ogilvy]

"=" & chr(65) & "1" & "*" & chr(67) & "1"

testing from the immediate window:

? "=" & chr(65) & "1" & "*" & chr(67) & "1"
=A1*C1

But I am not sure that is what you are trying to achieve.

 

[Guest]

Hi Tom,

This is exactly what I want. Appreciate your help.

BTW, what the "End(xlup)" for?

Thanks

 

[Tom Ogilvy]

go well below you data and hit the End Key, then the Up Arrow key. It takes
you to the last cell containing data in that column. It is the same in
code. So I am finding the extent of your data in column A.