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