VLookup Worksheet Function: Run TIme 1004 issue

[chipsworld]

I am receiving a "Run Time Error 1004 - Unable to get the VLookup property if the WorksheetFunction" for the below, but not until the 3rd Vlookup runs. Values are in the lookup table, etc...Help! I don't understand why the first two run but not the third and fourth.

The only difference between the values is the first two are all text values and the second two are/can be alpha-numeric...

What am I missing?

Dim qcntry As String
Dim ccntry As String
Dim qusc As String
Dim cusc As String
Dim wslkup As Worksheet

Set wslkup = Sheets("formulas")

qcntry = Application.WorksheetFunction.VLookup(Me.cmbqcntry.Value, wslkup.Range("AB3:AC25"), 2, False)
ccntry = Application.WorksheetFunction.VLookup(Me.cmbccntry.Value, wslkup.Range("AB3:AC25"), 2, False)
qusc = Application.WorksheetFunction.VLookup(Me.cmbqusc.Value, wslkup.Range("AB3:AC25"), 2, False)
cusc = Application.WorksheetFunction.VLookup(Me.cmbcusc.Value, wslkup.Range("AB3:AC25"), 2, False)[/B]

 

[jasonb75]

Probably no valid match in the range, if the vlookup would return a #N/A error when used as a formula in a worksheet then it will return rte 1004 in vba.

 

[chipsworld]

Jason,
I thought the same thing but...nope! The result is there. This is a lookup for status. I set the return value from a pre-determined list and a selection from a combo box.
This just makes no sense to me...was hoping there was some code thing I missed or limitation or ???

 

[Fluff]

I would agree with Jason, it cannot find the cmbqusc value in AB3:AB25.
Check that they are exactly the same, with no leading/trailing spaces.

 

[chipsworld]

OK...the value that is causing the issue is a numeric one. If I place an additional alpha character at the end of it in the drop down and the lookup range, it works fine, but as soon as I remove the alpha character, it fails.

Is there some crazy rule for numeric values in vlookups that I am unaware of? I have never seen anything like this before.

Another idea...could I use the worksheet range of values I am looking up in, as the source of the combo box? That would eliminate any possible chance of a miss-match since the source would be the same for both.

 

[Fluff]

Combobox values are text not numerical, which is why you get the problem.
You could load the combos like

Me.cmbqcntry.List = wslkup.Range("AB3:A25").Value

but you would then have the same values in each combo.

 

[chipsworld]

Fluff,
Found a solution...if I format the entire lookup range on the worksheet as "Text" the VLOOKUP works perfectly.
Thank you for the heads-up on that...was really pulling my hair out.

 

[Fluff]

Glad we could help & thanks for the feedback.