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" wrote:

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" wrote:

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" wrote:

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