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