VBA Error With Using A Named Range In A vlookup Formula

[Ark68]

Please consider this following simple code:

Private Sub uf1cbx1_operatorini_Change()
    'populate uf1lbl1_name
    uf1lbl1_name = Application.WorksheetFunction.VLookup(uf1cbx1_operatorini.Value, ufrng1_staff, 2, False)
End Sub

When the user changes the value in the combobox (uf1cbx1_operatorini) on my form, it triggers an event to populate the label (uf1lbl1_name) within the same userform.

I am getting an error with the line in red.

uf1rng_staff is a named range referring to a range in the workbook, sheet2!O32:P42. The values available in my combobox are all listed in cells O32:O42.

Am I using this parameter wrong by referring to a named range?

 

[Fluff]

What sort of values do you have in the combo? text or numbers?

 

[Ark68]

Hi Fluff ... they are text.

 

[Fluff]

Ok, try

uf1lbl1_name = Application.WorksheetFunction.vlookup(uf1cbx1_operatorini.Value, Range("ufrng1_staff"), 2, False)

 

[Ark68]

If I replace the named range with "sheet2.range("O32:P42"), the vlookup works without error.
I should mention the error I get with using the named range is "Unable to get the VLookup property of the WorksheetFunction class."

 

[Ark68]

I substituted the erroneous line with :

uf1lbl1_name = Application.WorksheetFunction.VLookup(uf1cbx1_operatorini.Value, Range("ufrng1_staff"), 2, False)

as suggested. It now creates an "Method 'Range' of object '_Global' failed."

 

[Norie]

You need to use Range(namedrange) (or [namedrange]) to refer to a named range in code.

Also you don't really need the lookup, you can use the ListIndex property of the combobox to get the row.

Private Sub uf1cbx1_operatorini_Change()
Dim idx As Long

    'populate uf1lbl1_name
    idx = uf1cbx1_operatorini.ListIndex

    If idx <> -1 then
        uf1lbl1_name.Value = Range("ufrng1_staff").Cells(idx+1, 2).Value
    End If

End Sub

 

[Ark68]

Thanks all.
There was a typo in the named range. All working wonderfully now.
Thanks Norie for sharing the ListIndex method with me!!

 

[Fluff]

Glad you sorted it & thanks for the feedback