VBA VLookup

[Mike Fogleman]

I can't see an obvious problem with the VLookup at the end of this code, but
I get an error 1004, 'Unable to get the VLookup property of the
WorksheetFunction class'. Could someone point me in the right direction?

Sub NodeAddress()
Dim LRow As Long
Dim LRow2 As Long
Dim c As Range
Dim mystring As String
Dim myrng As Range

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets.Add.Name = "Data"
Worksheets("All TCs Detail").Columns("A:C").Copy
Worksheets("Data").Range("A1")
With Columns("A:C")
.AutoFilter Field:=1, Criteria1:="<>"
.Copy Range("D1")
.Delete Shift:=xlToLeft
End With
LRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:C" & LRow).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"D1"), Unique:=True
With Columns("A:C")
.Delete Shift:=xlToLeft
End With
LRow = Cells(Rows.Count, "A").End(xlUp).Row
For Each c In Range("C2:C" & LRow)
mystring = c.Value
c.Value = WorksheetFunction.Trim(mystring)
Next c
Range("A1:A" & LRow).Cut
Range("C1").Insert Shift:=xlToRight
Columns("A:C").EntireColumn.AutoFit
LRow2 = Worksheets("Repeat TCs Detail").Cells(Rows.Count, "A").End(xlUp).Row
With Worksheets("Repeat TCs Detail")
.Range("A1").Value = Worksheets("Data").Range("B1").Value
.Range("C1").Value = Worksheets("Data").Range("C1").Value
End With
Set myrng = Worksheets("Data").Range("$A$2:$C$" & LRow)
For Each c In Worksheets("Repeat TCs Detail").Range("A2:A" & LRow2)
If c.Value <> "" Then
mystring = c.Offset(0, 1).Value
c.Value = WorksheetFunction.VLookup(mystring, myrng, 2, False)
End If
Next c


Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Mike F

 

[Tom Ogilvy]

You would get that error if mystring were not found in the lookup column.

 

[Don Guillett]

Didn't look too hard but try just
c instead of c.value

 

[Mike Fogleman]

The number is there. A VLookup formula in the cell works. Both the lookup
value and the values in the table have the same custom format '0000000-00'.
Mystring = "23603", but in the cell looks like 0000236-03. The same holds
true for the lookup table. Could this be that it is looking for 23603 in a
table that displays 0000236-03? Even though the cell contents are the same.

 

[Mike Fogleman]

Sorry Don, I didn't mean to reply to sender. This didn't help either.

 

[Dave Peterson]

If those values in the first column in the lookup table are really numbers, then
what happens if you change:

Dim myString as String
to
dim myString as Long

Although, I might change myString to myValue. It won't bother excel/vba, but it
would make it easier to read later.

 

[Mike Fogleman]

That was it Dave, thanks. In the meantime I found an alternative with .Find
that worked also.
'mystring = Format(c.Offset(0, 1), "0000000-00")
'c = myrng.Find(mystring, LookIn:=xlValues).Offset(0, 1)
That is what got me thinking about the format in the VLookup.
I wonder which is quicker.

Mike F

 

[Don Guillett]

Not a biggie. I see you got a good answer

 

[Mike Fogleman]

The VLookup is still hanging on some numbers. Not sure why, now. A cell
formula still finds it. So far the Find method has not hung up at all.
Guess which one I am going with?

Mike F
PS. I hate using data imported from AS400

 

[Tom Ogilvy]

Good you found a solution, but I think you need a little more due diligence.

If Vlookup as a formula works, then generally, vlookup used in VBA works as
well. So I suspect your statement that you get an inconsistency is not
against the same values.

123 <> "123" in either case - regardless of formatting.

If the cell value holds a number, it does not match a string and vice versa.
If both are stored as numbers and identical, then match. if both are stored
as strings and identical, then match. Formatting has no effect on a value
stored as a string.

Find is less discriminating in many instances, but not all.

 

[Mike Fogleman]

The data all came from an AS400 export and the data in question are numbers,
as you say, regardless of formatting. Changing the variable from String to
Long did the trick. Something else caused the inconsistency, because Excel
eventually crashed during this session. After restarting, the VLookup
formula worked fine, and much faster than the Find method.
Mike F