VLookup VBA error trapping

[xrull]

Can anyone tell me how to modify this formula to trap errors? I tried
wrapping it in IsError (code) Then...but it doesn't work.

Public Sub RunMeNow()
Dim i As Long
Dim iLastRow As Long

With Sheets("Sheet2")

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 3 To iLastRow 'iLastRow to 1 Step -1
.Cells(i, "B").Value = Application.VLookup( _
.Cells(i, "A").Value, Range("RegionGrouping"), 2,
False)
Next i
End With

End Sub

Or any other way I can handle an error with this type of procedure.
Thanks,
Xrull

 

[Dave Peterson]

Public Sub RunMeNow()
Dim i As Long
Dim iLastRow As Long
dim res as variant 'could be an error

With Sheets("Sheet2")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 3 To iLastRow 'iLastRow to 1 Step -1
res = Application.VLookup( _
.Cells(i, "A").Value, Range("RegionGrouping"), 2, False)
if iserror(res) then
.Cells(i, "B").Value = "it's an error"
else
.Cells(i, "B").Value = res
end if
Next i
End With

End Sub

I'd include the worksheet in that =vlookup() formula, too:

If RegionGrouping is on Sheet2:

res = Application.VLookup( _
.Cells(i, "A").Value, .Range("RegionGrouping"), 2, False)

Or on another sheet:

res = Application.VLookup(.Cells(i, "A").Value, _
worksheets("Somenamehere").Range("RegionGrouping"), 2, False)

 

[xrull]

Public Sub RunMeNow()
Dim i As Long
Dim iLastRow As Long
dim res as variant 'could be an error

   With Sheets("Sheet2")
        iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 3 To iLastRow 'iLastRow to 1 Step -1
            res = Application.VLookup( _
                .Cells(i, "A").Value, Range("RegionGrouping"), 2, False)
            if iserror(res) then
                .Cells(i, "B").Value = "it's an error"
            else
                .Cells(i, "B").Value = res
            end if
        Next i
 End With

End Sub

I'd include the worksheet in that =vlookup() formula, too:

If RegionGrouping is on Sheet2:

     res = Application.VLookup( _
             .Cells(i, "A").Value, .Range("RegionGrouping"), 2, False)

Or on another sheet:

     res = Application.VLookup(.Cells(i, "A").Value, _
             worksheets("Somenamehere").Range("RegionGrouping"), 2, False)

One more questions:
Suppose I wanted to name the column to look how would I do that? I
tried qualifying the range by:
Cells(i, "B").Value = Application.VLookup( .Cells(i, "A").Value, Range
("RegionGrouping"), Worksheets("Sheet1").Range("Period"),False
False)
I discovered that the formula needs to be more complex. It is coming
from the following formula:

=H3&IF(C3="","",VLOOKUP(C3,Data2!B:O,F3+2,0))&IF(D3="","",VLOOKUP
(D3,Data2!B:O,F3+2,0)). The column search changes every month based on
the information in column F.


Xrull

 

[Bob Phillips]

Try

Cells(i, "B").Value = Application.VLookup( .Cells(i, "A").Value,
Range("RegionGrouping"), Worksheets("Sheet1").Range("Period").Value, False)


--
__________________________________
HTH

Bob

Public Sub RunMeNow()
Dim i As Long
Dim iLastRow As Long
dim res as variant 'could be an error

With Sheets("Sheet2")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 3 To iLastRow 'iLastRow to 1 Step -1
res = Application.VLookup( _
.Cells(i, "A").Value, Range("RegionGrouping"), 2, False)
if iserror(res) then
.Cells(i, "B").Value = "it's an error"
else
.Cells(i, "B").Value = res
end if
Next i
End With

End Sub

I'd include the worksheet in that =vlookup() formula, too:

If RegionGrouping is on Sheet2:

res = Application.VLookup( _
.Cells(i, "A").Value, .Range("RegionGrouping"), 2, False)

Or on another sheet:

res = Application.VLookup(.Cells(i, "A").Value, _
worksheets("Somenamehere").Range("RegionGrouping"), 2, False)

One more questions:
Suppose I wanted to name the column to look how would I do that? I
tried qualifying the range by:
Cells(i, "B").Value = Application.VLookup( .Cells(i, "A").Value, Range
("RegionGrouping"), Worksheets("Sheet1").Range("Period"),False
False)
I discovered that the formula needs to be more complex. It is coming
from the following formula:

=H3&IF(C3="","",VLOOKUP(C3,Data2!B:O,F3+2,0))&IF(D3="","",VLOOKUP
(D3,Data2!B:O,F3+2,0)). The column search changes every month based on
the information in column F.


Xrull

 

[Dave Peterson]

Another take:

Cells(i, "B").Value = Application.VLookup( .Cells(i, "A").Value, _
worksheets("data2").Range("RegionGrouping"), _
.cells(i,"F").value, False)

Or maybe:

Cells(i, "B").Value = Application.VLookup(.Cells(i, "A").Value, _
Worksheets("data2").Range("RegionGrouping"), _
.Cells(i, .Range("period").Column).Value, False)

 

[SmartbizAustralia]

Don't use vlookup....it's too flakey

Use index/match or offset/match...more accurate and you may not need
the other stuff!!!

Regards,
Tom Bizannes
Excel Development
Sydney,Australia
http://www.macroview.com.au

 

[Dave Peterson]

How is it flakey?
How is it less accurate?

Don't use vlookup....it's too flakey

Use index/match or offset/match...more accurate and you may not need
the other stuff!!!

Regards,
Tom Bizannes
Excel Development
Sydney,Australia
http://www.macroview.com.au

 

[SmartbizAustralia]

Try it with a lookup on a string with a - in it! It goes to a row it
thinks is okay!...But not the one you wanted!

It is so bad ... just google for issues with vlookup..

Obviously there are some workarounds such as wrapping text around the
strings but....enough said


Regards,
Tom Bizannes
Excel Development
Sydney,Australia
http://www.macroview.com.au

 

[Bob Phillips]

I cannot reproduce that behaviour, and I am not aware of any such problems
with Vlookup. You may be correct, but I think you should back your
statements up with some tangible facts.

 

[Dave Peterson]

Maybe you meant asterisks and question marks. But =match() supports this useful
(not flakey) behavior.

 

[NickViv]

the best way to look for things on a sheet is to use .Find:

Here's an example that finds all instances of "Nick" on a sheet:

Sub FindStuff()

With ActiveSheet.Cells ' you can change this to any range
thingToLookFor = "Nick" ' for example
Set FoundIt = .Find(thingToLookFor, LookIn:=xlValues,
LookAt:=xlWhole)
If Not FoundIt Is Nothing Then
firstaddress = FoundIt.Address
Do
FoundIt.Select ' select it for example
myValue = FoundIt.Offset(0, 3) ' pick up the value 3 cols to the
right
Set FoundIt = .FindNext(FoundIt)
Loop While Not FoundIt Is Nothing And FoundIt.Address <>
firstaddress
Else
MsgBox "didn't find it !" ' but note, no error trapping needed
End If
End With

End Sub


This is the cleanest way to find things, don't use VLOOKUP, INDEX,
MATCH via VBA

Nick

http://www.excelexperts.com/
Free Excel based systems for the world