Error 2042

[Fred]

Hi
I'm trying to compare a variable to a value in a range of cells. Depending
on the variable, I need it to be entered into one of two worksheets. It
works fine if the variable is within the range - the variable is entered onto
Sheet1. If the variable is not within the range, then I get the error 2042
because it can't find the value - and the variable should be entered onto
Sheet2.

I do have a second range of cells so that if the variable is not in the
first range, it will be in the second range. How do I get this working
correctly?

Dim ShopName as String

Set Rng = Worksheets("IllinoisShopNames").Range("A2:A50")
Results = Application.VLookup(Range("E5").Value, Rng, 1, False)
Do While ShopName <> ""
If Results = ShopName Then
do stuff on Sheet1
Else
do other stuff on Sheet2
End If
Loop

TIA
Fred

 

[Dave Peterson]

You could use:

Dim ShopName as String
dim Results as variant

Set Rng = Worksheets("IllinoisShopNames").Range("A2:A50")

'it's nice to qualify the ranges!
Results = Application.VLookup(worksheets("somesheetname").Range("E5").Value, _
Rng, 1, False)

if iserror(results) then
'do stuff on sheet2
else
'do stuff on sheet1
end if

======
I don't quite get the loop portion, though.

And since you're only checking to see if there's a match, then =match() seems
like a more appropriate function to use:

Dim ShopName as String
dim Results as variant

Set Rng = Worksheets("IllinoisShopNames").Range("A2:A50")

Results = Application.match(worksheets("somesheetname").Range("E5").Value,rng,0)

if iserror(results) then
'do stuff on sheet2
else
'do stuff on sheet1
end if