VLookup VBA

[Wardylewis]

Good evening,

I attempting to use a Vlookup as to provide me with a yes-no answer and then use that to lock out a box if the answer is "No".

I need to define the sheet as it will be hidden to the user as they input data on a Userform.

However, every time I run this code I get a run-time error '1004': Unable to get the Vlookup property of the WorksheetFunction Class.

I've googled everything but cannot seem to fix it, can anybody assist please?

Audio = Application.WorksheetFunction.VLookup(TextBox1.Value, Worksheets("Employee List").Range("A:P"), 12, 0)

If Audio = "No" Then
ComboBox1.Enabled = False
TextBox5.Enabled = False
End If

 

[Rino702]

The range specified in your Vlookup is too large.
Limit the Vlookup by finding first the last row.

Use the following code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

LastRow = Sheets("Employee List").Range("A1").SpecialCells(xlCellTypeLastCell).Row
Audio = Application.WorksheetFunction.VLookup(ActiveSheet.Shapes("TextBox 1").OLEFormat.Object.Text, Sheets("Employee List").Range("A1:P" & LastRow), 12, 0)
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]If Audio = "No" Then
ComboBox1.Enabled = False
TextBox5.Enabled = False
End If[/FONT]

Regards
[/FONT]

 

[Trebor76]

Try this to set the Audio variable:

Audio = Evaluate("VLOOKUP(""" & Me.TextBox1.Value & """,'Employee List'!A:P,12,FALSE)")

Regards,

Robert

 

[Wardylewis]

Good Morning @Rino702 and @Trebor76.

I have tried both of these but none are working I either get a mismatch error or Unable to get the Vlookup property of the WorksheetFunction Class

Have you got any idea why?

 

[Norie]

What type of values do you have in column A on the 'Employee List' sheet?

 

[Wardylewis]

Hi @Norie,

Thanks for the quick reply, column A contains employee ID's in a number format.

 

[Trebor76]

My bad - I assumed the data was text. Se if this resolves the issue:

Audio = Evaluate("VLOOKUP(" & Me.TextBox1.Value & ",'Employee List'!A:P,12,FALSE)")

Robert

 

[Fluff]

How about

Dim Audio As Variant
Audio = Application.vlookup(Val(TextBox1.Value), Worksheets("Employee List").Range("A:P"), 12, 0)
If IsError(Audio) Then
   MsgBox "ID not found"
ElseIf Audio = "no" Then
   ComboBox1.Enabled = False
   TextBox5.Enabled = False
End If

 

[Wardylewis]

Hi @Fluff,

That works perfectly thank you.

 

[Fluff]

You're welcome & thanks for the feedback