0

My VBA enters a formula in a cell. This formula references another sheet.

I'd like to replace the sheet name '13) Friends Mailer' with a string variable, so the formula references a sheet of my choosing.

ActiveCell.FormulaR1C1 = _
  "=VLOOKUP(RC[-2],'13) Friends Mailer'!R6C1:R90C15,14,0)+VLOOKUP(RC[-2],'13) Friends Mailer'!R6C1:R90C15,15,0)"

I tried:

ActiveCell.FormulaR1C1 = _
  "=VLOOKUP(RC[-2],'" &worksheetName& "'!R[4]C[-9]:R[88]C[5],14,0)+VLOOKUP(RC[-2],'13) Friends Mailer'!R[4]C[-9]:R[88]C[5],15,0)"
Community
  • 1
  • 1
James Hague
  • 1
  • Possible duplicate of [Use string value from a cell to access worksheet of same name](https://stackoverflow.com/questions/16899175/use-string-value-from-a-cell-to-access-worksheet-of-same-name) – kabanus Apr 27 '18 at 10:10
  • Also a better possible answer: https://stackoverflow.com/questions/40456960/excel-vba-sheet-name-as-variable – kabanus Apr 27 '18 at 10:11
  • I might sound a little redicolous, but is `worksheetName` a `String` or a `Worksheet`? – AntiDrondert Apr 27 '18 at 12:45

2 Answers2

0

Replace:

"=VLOOKUP(RC[-2],'" &worksheetName&

with:

"=VLOOKUP(RC[-2],'" & worksheetName &

(there may be other problems)

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
0
txt = "13) Friends Mailer"
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-2],'" & txt & "'!R6C1:R90C15,14,0)+VLOOKUP(RC[-2],'" & txt & "'!R6C1:R90C15,15,0)"
CFO
  • 288
  • 1
  • 11