CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-r1c1-notation
Matteo Ghiringhelli
Matteo Ghiringhelli

Reputation: 11

Excel VBA how to keep a variable cell in a R1C1 formula

My problem is that I can't figure out a way for the formula not to immediately evaluate the cell number, while I would rather want it to keep it variable.

I'm currently working on a code that checks the value of an arbitrary cell in the workbook which can be changed manually. This value determines the value of a whole column of calculations, but this has been working fine so far for me.

So what I mean is that I would like the formula to be kept in this form ("=R" & x + y & "C), where x and y are arbitrary variables, instead of the module evaluating it to a specific cell so that I can change the value of one of the variables to change the reference cell.

Upvotes: 1

Views: 143

Answers (1)

Erik E. Flores Delfín
Erik E. Flores Delfín

Reputation: 9

You could use the range and using the formula method to call as next:

LRandomNumber1 = Int ((300 - 200 + 1) * Rnd + 200)
LRandomNumber2 = Int ((300 - 200 + 1) * Rnd + 200)
Worksheets("Sheet1").Range("A1").Formula = "=A" + LRandomNumber1 + ":C" + LRandomNumber2;

Upvotes: 1

Related Questions