CODEWITHSUNDEEP
excelvba
Ahror Kuldashev
Ahror Kuldashev

Reputation: 1

VBA use variable value as cell reference in formula

I am trying to enter a "=$M15-$N15" formula to cell A15. However, need to use variable (r) instead of 15 as I don't know that value until later in the code. Trying the below but it is not working. What am I doing wrong?

Range("A" & r).formula = "="$M" & r - "$N" & r"

after the code is run, I need to have a formula ($M15-$N15) in Cell A 15.

Upvotes: 0

Views: 1233

Answers (1)

Zack Barresse
Zack Barresse

Reputation: 249

I suspect you're looking for the INDIRECT function. An example of this, based on your explanation, would look like:

=INDIRECT("M"&A1)-INDIRECT("N"&A1)

If you're actually looking for VBA, you could use the following:

Range("A" & r).formula = "=$M" & r & "-$N" & r

However, this makes me suspect this is part of some over-arching routine, and, without knowing more, I can only speculate there might be a better or more efficient method(s) for going about it.

Upvotes: 1

Related Questions