0

I'm very new to VBA so please bear with me...

I'm trying to perform a vlookup where the workbook and worksheet referred to in table_array are string variables.

Specifically, the line of code I'm having trouble with is this:

ActiveCell.FormulaR1C1 = _
    "=IF(ISNA(VLOOKUP(RC[5],'[Var1]Var2'!C5,1,FALSE,)),""NOT IN TRACKER"",""IN TRACKER"")"

Var1 is the name of the workbook I'm looking in and Var2 is the name of the worksheet. Both are stored as strings.

Every time my program gets to this line of code it triggers an application-defined or object-defined error.

Changed to

=IF(ISNA(VLOOKUP(RC[5],'[" & Var1 & "]" & Var2 & "'!C5,1,FALSE,)),""NOT IN TRACKER"",""IN TRACKER"")"
GSerg
  • 76,472
  • 17
  • 159
  • 346
KingNeilDiamond
  • 1
  • 1
  • 1
    Variables need to be outside the quotes, concatenated with `&`: `=IF(ISNA(VLOOKUP(RC[5],'[" & Var1 & "]" & Var2 & "'!C5,1,FALSE,)),""NOT IN TRACKER"",""IN TRACKER"")"` – BigBen Jul 27 '21 at 17:57
  • Hmmm, still no luck... – KingNeilDiamond Jul 27 '21 at 18:18
  • Then `Debug.Print` the entire string and inspect the output in the Immediate Window. Copy this output and paste it into a cell ... Excel should complain and hopefully give a hint as to what is wrong with the formula. What are the values of `Var1` and `Var2`? – BigBen Jul 27 '21 at 18:18
  • 2
    Extra `,` here after FALSE: `... & "'!C5,1,FALSE,)` You need to remove that. FYI you can use MATCH() if you're just checking one column. – Tim Williams Jul 27 '21 at 18:23
  • This is what it outputs: =IF(ISNA(VLOOKUP(RC[5],'[2021_Delivery_Tracker.xlsx]WB Deliveries (07-02)'!C5,1,FALSE,)),"NOT IN TRACKER","IN TRACKER") Which is exactly what it should look like, I'm pretty sure. But it still gives the same error... – KingNeilDiamond Jul 27 '21 at 18:24
  • 1
    Firstly, fix the error Tim points out - remove the extra `,`. Secondly, is the specified wb open? If it's not you'll need to include the full path inside the `[ ]` – chris neilsen Jul 27 '21 at 19:15
  • Oh my god it worked! It's always a damn comma or semicolon.... Thanks guys!!! – KingNeilDiamond Jul 27 '21 at 21:53

0 Answers0