Excel VBA Named Range in VLOOKUP #VALUE error

Question

This code throws a #VALUE error in cell A8 or A17 (depending on which toggle is active) instead of the string value from the 'dataWeaponField' named range. This Excel formula in (for example) cell A17 works correctly: =VLOOKUP(B17,dataWeaponField,2,FALSE). I am trying to simply replicate this formula in VBA.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.Column = 1 And Target.Row = 7 Then 'CLICK TO SWAP WEAPONS
        If Range("A8").Interior.ColorIndex = 6 Then 'Primary Weapon
            Range("A8:C15").Interior.ColorIndex = 12 'Primary Weapon
            Range("A17:C24").Interior.ColorIndex = 6 'Secondary Weapon
            Range("A8").Value = "" 'Primary Weapon
            Range("A17").Value = Application.VLookup(Range("B17"), ThisWorkbook.Names("dataWeaponField"), 2, False)
        Else
            Range("A8:C15").Interior.ColorIndex = 6 'Primary Weapon
            Range("A17:C24").Interior.ColorIndex = 12 'Secondary Weapon
            Range("A8").Value = Application.VLookup(Range("B8"), ThisWorkbook.Names("dataWeaponField"), 2, False)
            Range("A17").Value = "" 'Secondary Weapon
        End If
        Range("A6").Select 'Cell above CLICK TO SWAP WEAPONS
    End If
End Sub

Answer 1

Because you are passing a Name object instead of Range. You can set the Formula:

Range("A17").Formula = "=VLOOKUP(B17, dataWeaponField, 2, FALSE)"

or Evaluate it:

Range("A17").Value = [VLOOKUP(B17, dataWeaponField, 2, FALSE)]

Also, because SelectionChange doesn't trigger when clicking a selected cell, you can use a Control or Hyperlink and the Worksheet.FollowHyperlink Event

Answer 2

ThisWorkbook.Names("dataWeaponField") is a Name object, not a Range object. VLookup expects the latter, so you can simply replace this expression by Application.Range("dataWeaponField").

The Application. prefix is necessary because your code is in a worksheet code module, and the named range belongs to another worksheet, so we need the application object to fetch it from global scope.

Alternatively you can evaluate the whole formula that works in Excel as is, using Application.Evaluate(yourWorkingFormula).