Vlookup help, please

[Fred]

Hi
I'm having problems with this function. I need to compare "ShopName" to an
alphabetically sorted list on another worksheet. I'm getting a "Complie
error: sub or function not defined" and the "VLookup" is highlighted.

The variables have been properly declared.

If ShopName = Range(VLookup("E" & ERRW &
",IllinoisShopNames!A2:A50,1")).Value Then
blah blah blah
End If

What am I missing?
Thanks in advance
Fred

 

[Mike H]

Fred,

I don't understand your formula. If I've understood you lookup the value in
E(n) in A2 - A50 so the only thing you can return is what was in E(n) in the
first place. However, here's the correct syntax

Set rng = Range("IllinoisShopNames!A2:A50")
Results = Application.VLookup(Range("E" & errw).Value, rng, 1, False)

Mike

 

[Fred]

Hi Mike
What I'm trying to do with my formula is; if the value of "ShopName" matches
one of the values in the list on the worksheet IllinoisShopNames, cells
A2:A50, then proceed. I'm having trouble with the comparison.

I tried your suggestion, and then compared:
If rng = ShopName Then...
but I do not get a value for rng. I'm getting a run time error #13, type
mismatch.

Any more thoughts?
Fred

 

[Mike H]

Fred,

I don't know from you snippet of code where you are geting the variable
'shopname' from but this may get you going. Note that I have manually set
errw=1 but i suppose you do this in a loop.

Sub sonic()
errw = 1
Dim rng As Range
Set rng = Range("IllinoisShopNames!A2:A50")
If Application.VLookup(Range("E" & errw).Value, rng, 1, False) = shopname Then
'do something
else
'do something else
End If
End Sub

Mike

 

[Fred]

Thanks Mike
I forgot to declare Dim rng as Range.

Everything else is "working", just need to troubleshoot it.

Have a great day.
Fred

 

[Fred]

Hi
Problems again.
I declared:
Dim ShopName as String

If I use this line as you suggested:
"If Application.VLookup(Range("E" & errw).Value, rng, 1, False) = shopname
Then"
then I get "error 13 type mismatch"

If I use this other line and If statement:
"Results = Application.VLookup(Range("E" & errw).Value, rng, 1, False)"
"If ShopName = Results then"
Then the results don't advance past the first item in the list, which is
"Aurora"

What am I missing now?

Thanks in advance
Fred