Insert variables on R1C1 Formula

[ccasio04]

Hey guys, I'm new here, I just looked over the whole internet and can't manage to understand what I'm going to explain and that probably is very simple.

So, I have 4 variables:
file1 = Sheets("BD MXN").Range("Y2")
file2 = Sheets("BD MXN").Range("Y3")
sheet1 = Sheets("BD MXN").Range("X4")
sheet2 = Sheets("BD MXN").Range("X5")

These variables are set on a specific cell because they change every month, so basically, before running the macro the user need to change some other cells to get to those variables (which are the file and sheet name of 2 different excel workbooks)
Those variables won't change in the whole code, therefore, I want to insert them into a vlookup or [Index, Match (Inverse vlookup)] formula, for example (name of path is only to make it shorter):
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(INDEX('C:\fullpath\[file1]sheet1'!R2C5:R30000C5,MATCH(RC[-9],'C:\fullpath\[file1]sheet1'!R2C6:R30000C6,0)),0)"

So, I tried different syntax to make Excel write whatever it is filled on the variable, however, it writes literally the name of the variable or returns an error instead.

Can someone help me figuring out this one? And an additional and great help would be if someone can explain me the logic on it (not just the code, but to understand what does VBA need to recognize a variable inserted in that way).

Thank you very much in advance.

[Richard Buttrey]

Untested in the basence of the workbook but perhaps...

Please Login or Register  to view this content.

I wasn't sure whether for instance 'file1' was a VBA variable that you have declared and set to take the value from Range Y2 in the BD MXN sheet.
If so then replace the Sheets("BD MXN").Range("Y2") with file1

[ccasio04]

Hello Richard,

Thanks for the quick response, I changed it and it worked perfectly, thank you very much.

and yes, 'file1' was a declared VBA variable that takes the value of Y2 in sheet BD MXN.

So, one last question, whenever I have the need of doing that, should I give VBA the location of the text I'm willing to replace instead of providing a variable?

Thank you very much

[Richard Buttrey]

Hi,

To some extent it's a matter of personal choice, particularly if in your code there's only one or two references to the cell.
Personally I always prefer to use a VBA variable for two reasons.

1. I think it makes code easier to read
2. There's a time overhead every time VBA needs to jump back to the Excel App and return. So it makes sense to create a VBA variable if it will be used many times. This is particularly relevant with looping procedures where within the loop a reference is made to a cell or object in the Excel App.

[ccasio04]

Thanks a lot for your time, greetings!