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

Slai · Accepted Answer · 2017-06-19 00:54:34Z

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

answered Jun 19, 2017 at 0:54

Slai

23k5 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Slai Over a year ago

@chazcon it does the same as what you described in the question "This Excel formula in (for example) cell A17 works correctly: =VLOOKUP(B17,dataWeaponField,2,FALSE)"

A.S.H · Accepted Answer · 2017-06-19 01:21:17Z

0

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).

edited Jun 19, 2017 at 1:21

answered Jun 19, 2017 at 1:02

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

3 Comments

chazcon Over a year ago

The first solution results in an error: Run-time error '1004': Method 'Range' of object '_Worksheet' failed.

chazcon Over a year ago

The second solution results in an error as well: Compile error: Sub or Function not defined.

A.S.H Over a year ago

@chazcon ah ok, because the code is in sheet code module. It should be Application.Range("dataWeaponField"), see edit.

Collectives™ on Stack Overflow

Excel VBA Named Range in VLOOKUP #VALUE error

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related