Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup with VBA trouble :-(
if the vlookup() would return #n/a, then worksheetfunction.vlookup() raises an
error. You can turn off error checking and then do the vlookup() then check for the error, but I find it easier just using application.vlookup. You can check the results of the vlookup() with iserror(). This compiled, but I didn't test any data: Option Explicit Sub testme() Dim strRN As String Dim i As Long Dim j As Long Dim res As Variant For i = 2 To Sheets("ABC").Range("a1").End(xlDown).Row strRN = Sheets("ABC").Range("d" & i).Value If Left(strRN, 1) = 0 Then strRN = Right(strRN, 3) End If For j = 2 To Worksheets.Count res = Application.VLookup(strRN, Sheets(j).Range("C:E"), 3) If IsError(res) Then Sheets("abc").Range("f" & i).Value = "Not Found" Else If res = Sheets("ABC").Range("a" & i).Value Then Sheets("ABC").Range("f" & i).Value = Worksheets(j).Name 'MsgBox Worksheets(j).Name & " " & strRN Else Sheets("ABC").Range("f" & i).Value = "!" End If End If Next j Next i End Sub And just curious--did you really mean this as your vlookup() res = Application.VLookup(strRN, Sheets(j).Range("C:E"), 3, False) This will check for an exact match in sheets(j) column C. "sp00nix <" wrote: I keep getting errors with this code - - - - specifically the if statement when it tries to execute: Code: -------------------- WorksheetFunction.VLookup(strRN, Sheets(j).Range("C:E"), 3) -------------------- maybe someone could take a whack at it... Code: -------------------- Dim strRN As String Dim i As Integer, j As Integer ' First Loop, cycles through sheet 1 for values to use in VLOOKUP '---------------------------------------------------------------- For i = 2 To Sheets("ABC").Range("a1").End(xlDown).Row strRN = Sheets("ABC").Range("d" & i).Value If Left(strRN, 1) = 0 Then strRN = Right(strRN, 3) ' Loop 2, checks each sheet for the lookup result '------------------------------------------------ For j = 2 To Worksheets.Count ' If a result is found, then put the sheet ' name next to the value on the "ABC" sheet '------------------------------------------+ If WorksheetFunction.VLookup(strRN, Sheets(j).Range("C:E"), 3) = Sheets("ABC").Range("a" & i).Value Then Sheets("ABC").Range("f" & i).Value = Worksheets(j).Name 'MsgBox Worksheets(j).Name & " " & strRN Else Sheets("ABC").Range("f" & i).Value = "!" End If Next j ' End Loop 2 '<---------- Next i ' End Loop 1 '<---------- -------------------- I have all the cells in my spreadsheet formated as text. I thought i was doing things the easy way - but i've been stuck on that line for a too long - my self confidence has dropped 3 points :-( I checked out some of the other posts but none seem to really help. TIA, Mike --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with Vlookup | Excel Discussion (Misc queries) | |||
Trouble with vLookup | New Users to Excel | |||
Having trouble with vlookup and match | Excel Worksheet Functions | |||
VLOOKUP trouble .. HELP! | Excel Worksheet Functions | |||
VLookup with VBA trouble :-( | Excel Programming |