Solved: VBA VLookup not working

[SBrooky]

Been struggling with this one for quite a while now and tried a few methods from googling with no help so I decided to turn to reddit!

I have a populated sheet and I want to find where 3 different combo boxes appear on the same line. Being the "clever dick" I am after trying other methods was on concatenate the 3 rows on the end of each row then simply look for the 3 comboboxes put together:

result = Application.WorksheetFunction.VLookup(ComboBox3.Text & ComboBox5.Text & ComboBox4.Text, sheet.Range("A1:N72"), 4, False)
So im trying to set 'result' as row 4 on the line which contains the result of ComboBox3&5&4 put together in that order in the area of A1:N72.

Looks legit to me but it comes back with a "Unable to get the VLookup property of the WorksheetFunction class".

I have remembered to do this:

Dim result As String
Dim sheet As Worksheet
Set sheet = ActiveWorkbook.Sheets("PriceMatrix")

Also if it makes any difference the concatenated results are in row N?

Any more info needed?

Please help!

[Tinbendr]

Try changing Sheet to aSheet, as Sheet is a reserve word.

Take a look at this (http://www.youtube.com/watch?v=Q_7M-DRNPCI), too.

[SBrooky]

Didnt know that! Shouldve guessed though. Changed it all over to aSheet and I get the same error. =(

Also due to work restrictions I cant go on YouTube so ill check this out later.

[Tinbendr]

Dunno? Need a sample.

[mancubus]

perhaps:

Sub Example_of_Vlookup()
'http://www.mrexcel.com/archive/VBA/8653.html

Dim lookFor As Variant
Dim rng As Range
Dim col As Integer
Dim result As Variant

Set lookFor = ComboBox3.Text & ComboBox5.Text & ComboBox4.Text
Set rng = ActiveWorkbook.Sheets("PriceMatrix").Range("A1:N72")
col = 4

On Error Resume Next
result = Application.VLookup(lookFor, rng, col, 0)
If IsError(result) Then
MsgBox lookFor & " not found"
Else
MsgBox "The look-up value of " & lookFor & " is " & result & " in column " & col
End If
On Error GoTo 0

End Sub

[SBrooky]

perfect mancubus thanks alot for your help! =D

[mancubus]

you're wellcome...