0

I have a VBA code that inserts vlookup formula. The line looks like that:

Range("I2").FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[Cash.xlsm]Account Mapping'!C3,1,0)"

However, I would like to make it more general in case someone changes the name of the main file where the source for vlookup is.

I defined variables path and mainwb:

path = Application.ActiveWorkbook.path & "\"
mainwb = ActiveWorkbook.Name

but got lost while updating the formula in the code with them.

Tried something like:

Range("I2").FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'path & [mainwb]Account Mapping'!C3,1,0)"

But unfortunately, I haven't succeed so far.

Can you please support?

FunThomas
  • 23,043
  • 3
  • 18
  • 34
qxphm
  • 19
  • 3
  • 1
    General rule of thumb: variables don't belong inside quotes, and need concatenated with `&`. So something like `"=VLOOKUP(RC[-1],'" & path & "[" & mainwb & "]Account Mapping'!C3,1,0)"`. – BigBen Sep 23 '22 at 13:21
  • Another helpful hint: before writing the formula to a cell, use `Debug.Print` to print it to the Immediate Window to make sure it's what you want. – BigBen Sep 23 '22 at 13:23

0 Answers0