title: Excel VBA Named Range in VLOOKUP #VALUE error
body: <p>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.</p>
  
  <pre><code>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
  </code></pre>