VLookup with VBA trouble :-(

[sp00nix]

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 tha
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,
Mik

 

[sp00nix]

Oh yeah... a few more things : : :

Code
-------------------

MsgBox WorksheetFunction.VLookup(0703, Sheets(2).Range("C:E"), 3)

-------------------
works.

but


Code
-------------------

MsgBox WorksheetFunction.VLookup("0703", Sheets(2).Range("C:E"), 3)

-------------------
does not.

Since i have the serial stored in a variable type string, how can
change a string to an expression?

Maybe that would be the answer to all my problems

 

[sp00nix]

*bump*

 

[Dave Peterson]

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.