Search code examples
vbareferencerangerowcell

How to make Range.Value have cell reference specific to local row vba

I want to add a range, C2:E6, where the value for each row refers to that row's A and B cells. For example, C2, D2 and E2 should be equal to

"=A2*B3"

C3, D3 and E3 should be equal to

"=A3*B3"

etc. I can do a For loop, but it would be much easier if I could figure out how to do it using Range.Value. I'm fairly new to VBA, is there some way to do this that I'm just not familiar with? thanks a lot for your help.

Solution

  • Use:

    Range("C2:E10").Formula = "=$A2*$B2"
Range("C2:E10").Value = Range("C2:E10").Value

    Change the 10 to your final row. Excel will change the row reference automatically. Then replace the formula with the resultant value.