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)

--

Dave Peterson

[xrull]

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

"xrull" <joni...@gmail.com> wrote in message
news:3f15fb1b-1dcd-47d4...@g38g2000yqd.googlegroups.com...

[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)

xrull wrote:
>
<<snipped>>

>
> 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

[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?

--

Dave Peterson

[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

[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.

--
__________________________________
HTH

Bob

"SmartbizAustralia" <t...@smartbiz.com.au> wrote in message
news:bae91cca-34ef-41aa...@a29g2000pra.googlegroups.com...

[Dave Peterson]

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

--

Dave Peterson

[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