#NA error using vlookup in vba

[Guest]

Hi
I have the following formual in VBA and I keep getting the #NA in the ouput
cell and I dont know why.

Dim stNumber As String
Dim stans As Variant

stNumber = cobStore.Value
stans = Application.VLookup(stNumber,
Worksheets("Numbers").Range("A2:B188"), 2)

Range("I15").Value = stans

can someone please help.

Thanks
Noemi

 

[Don Guillett]

Perhaps we need to tell excel what cobstore is?
Sub lookitup()
cobstore = 1
Range("I15") = Application.VLookup(cobstore, _
Worksheets("sheet4").Range("d2:e188"), 2)
End Sub

 

[Tom Ogilvy]

stNumber

would indicate that you are looking up a number, but you have typed stNumber
as String.

the string "123" does not match the number 123. So if your search range
contains numbers, you need to search with a number.

Perhaps do

Dim stNumber As Double

 

[Guest]

Hi Tom
Thank you very much, that was the problem. It should have been double.
Thanks again
Noemi