VLOOKUP error 1004

[Omar]

Hi,

Can anyone help me to find the error in this code?

Private Sub btn4_Click()
Dim magneto As Range
Dim numero
Dim valor
Set magneto = Worksheets("info").Range("A2:AW65536")
numero = ufConsulta.txt33.Value
ufConsulta.cbo1.Value = Application.WorksheetFunction.VLookup(numero,
magneto, 13, false)

Thanks in advance...

 

[Dave Peterson]

Drop the .worksheetfunction portion:

ufConsulta.cbo1.Value = Application.VLookup(numero, magneto, 13, false)

 

[Omar]

I tried it and the same error message is still there.

 

[Dave Peterson]

I'd use:

Private Sub btn4_Click()
Dim magneto As Range
Dim numero
Dim valor
dim res as variant
Set magneto = Worksheets("info").Range("A2:AW65536")
numero = ufConsulta.txt33.Value
res = Application.VLookup(numero, magneto, 13, false)
if iserror(res) then
ufConsulta.cbo1.Value = "No match"
else
ufConsulta.cbo1.Value = res
end if
....

====
One more thing to watch out for...
If you're matching up numbers (not text), you may want:
res = Application.VLookup(cdbl(numero), magneto, 13, false)
or
res = Application.VLookup(clng(numero), magneto, 13, false)

 

[Omar]

If I'm expecting a String value, should I use:
res = Application.VLookUp(CStr(numero), magneto, 13, false)
?

Regards...

 

[Dave Peterson]

The value in that text box (ufconsulta.txt33 is a textbox, right?) is already a
string. So cstr() won't help at all.

But I would declare my variables more explicitly if I knew what they were:

dim numero as String
dim Valor as ????????

res is declared as a variant because it could be a number (long or double), a
string, but especially since it could be an error.

If I'm expecting a String value, should I use:
res = Application.VLookUp(CStr(numero), magneto, 13, false)
?

Regards...