Problems with specific names when using vlookup function in VBA
I have a combobox which enables me to choose a name from an array.
Depending on the name i choose, the macro shall repaint the userform labels with the corresponding numbers from the array. All this works fine. The problem is that the macro gives me an error 1004 " Unable to get the Vlookup property of the WorksheetFunction class" if a use a specific name in specific cell in the array. E.g. if put "Anders" or "Barry" in the third cell from the top in the left column i get the error message. If i use "Nathan" in the same cell it works fine.
I fo have a workaround for the problem, but im very curious to find out why excel target "special" names
Bob 5 11
Charlie 2 3
Anders 0 6
Terrance 1 0
Tor 3 7
Private Sub combobox1_change()
Dim name As String
Dim myrange As Range
name = ComboBox1.Value
Set myrange = Worksheets("Sheet1").Range("D4:F9")
UserForm1.Label22 = WorksheetFunction.VLookup(name, myrange, 2)
UserForm1.Label23 = WorksheetFunction.VLookup(name, myrange, 3)