Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup probelm
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
|
|||
|
|||
Vlookup probelm
Well I'm using visual studio rather than VBA, i have this in a button click
event "Don Guillett" wrote: Life will be a lot easier if you try looking in the vba help index for FINDNEXT. Good example. -- Don Guillett Microsoft MVP Excel SalesAid Software "NewToVB" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Probelm with macro | Excel Worksheet Functions | |||
FileCopy Probelm | Excel Programming | |||
Hyperlink probelm | Excel Programming | |||
Hyperlink probelm | Excel Worksheet Functions | |||
Range probelm in VB6 | Excel Programming |