| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop and vlookup
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and vlookup
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 "NewToVB" wrote: 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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and vlookup
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?) OssieMac wrote: 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 "NewToVB" wrote: 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! -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and vlookup
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!! "NewToVB" wrote: 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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and vlookup
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 wrote: 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!! "NewToVB" wrote: 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! -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and vlookup
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! "Dave Peterson" wrote: 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 wrote: 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!! "NewToVB" wrote: 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! -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Add loop to Vlookup | Excel Programming | |||
| VLOOKUP loop multiple times | Excel Worksheet Functions | |||
| Need a vb loop for vlookup | Excel Programming | |||
| Need a vb loop for vlookup | Excel Programming | |||
| Find & Replace / Loop & Vlookup | Excel Programming | |||
Linear Mode
