loop and vlookup

[NewToVB]

I'm doing a vlookup to find the matching identifier in another excel sheet,
and when it doesn't find the matching identifier, then look for another
identifier in the sheet, if neither are there return a zero. But for some
reason, when it doesn't find the first identifier it doesn't look for the
other, it just returns zero, any idea why?
This is what I have:

For i = 3 To lastRow Step 1
tmp = VariantType.Empty

On Error Resume Next
tmp = LCV.WorksheetFunction.VLookup(LCV.Range("A" & i).Value,
oApp.Range("A1:J" & lastRow2).Value, 10, False)
On Error GoTo 0

If tmp = VariantType.Empty Then
On Error Resume Next
tmp = LCV.WorksheetFunction.VLookup(LCV.Range("B" &
i).Value, oApp.Range("B1:J" & lastRow2).Value, 10, False)
On Error GoTo 0
If tmp = VariantType.Empty Then
LCV.Range("L" & i).Value = 0
Else : LCV.Range("L" & i).Value = tmp
End If
Else : LCV.Range("L" & i).Value = tmp
End If
Next i

Thanks in advance!

 

[OssieMac]

Hi,

You have some errors in your code. I don't know what VariantType.Empty is.
Can't find anything on it in help so I used an alternative method.

The function does not refer to a specific worksheet; it is simply a
worksheet function and the name of the worksheet is not included.

The table to lookin is a range and should not have '.Value' after it.

Note the comment before your second VlookUp. You were trying to look in a
column which was outside the table range.

Set LCV = Sheets("Sheet1")
Set oAPP = Sheets("Sheet2")

For i = 3 To lastRow Step 1
tmp = Empty
On Error Resume Next
tmp = WorksheetFunction.VLookup(LCV.Range("A" & i) _
.Value, oAPP.Range("A1:J" & lastRow2), 10, False)
On Error GoTo 0

If tmp = Empty Then

On Error Resume Next

'In the next line of code columns B to J is only
'9 columns wide. Can't say look in column 10
tmp = WorksheetFunction.VLookup(LCV.Range("B" & i) _
.Value, oAPP.Range("B1:J" & lastRow2), 9, False)

On Error GoTo 0

If tmp = Empty Then
LCV.Range("L" & i).Value = 0
Else
LCV.Range("L" & i).Value = tmp
End If
Else
LCV.Range("L" & i).Value = tmp
End If

Next i

Regards,

OssieMac

 

[Dave Peterson]

NewToVB is writing in VSTO.

Probably a good thing to include in all of his posts when he's looking for an
answer that will work in VSTO, huh?

(is that too passive aggressive?)

 

[NewToVB]

Thank you for your help! The reason I'm using variant type is because I'm
using visual studio rather than VBA. tmp is declared as VariantType, and I
tried at first to use tmp = Empty but it was an error, so I had to use tmp =
VariantType.Empty. Also for the vlookup, LCV is the excel application that I
have to refer to rather than the worksheet. I can't run it with just
("worksheetfunction.VLookUp..."). Good call on the table range.. but for
some reason, I'm still getting a zero for the second Vlookup and the
identifier is there. Could it be because the cell I'm looking for is all
caps? I have both columns (the identifier its looking for, and the range its
looking in) in the second Vlookup formatted as text so I don't think that
would matter...but I don't know what else it could be. Thanks in advance!!

 

[Dave Peterson]

Case doesn't matter to =vlookup().

You may want to look at your data once more. Maybe there's a difference (extra
space???).

In fact, open the workbooks and build the formula manually and see what
happens. Maybe that'll give you a hint.

 

[NewToVB]

I got it! On OssiMac's post earlier, about the table to looking in range,
rather than changing 10 to 9, i changed "B1:J" to "A1:J" and then I realized
that the column its looking in has to be the left most column and the value
I'm looking for is in Column B. Thanks for the help!