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
"Dave Peterson" wrote in message
...
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 wrote:

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.
"Tom Ogilvy" wrote in message
...
You would get that error if mystring were not found in the lookup
column.

--
Regards,
Tom Ogilvy


"Mike Fogleman" wrote in message
...
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





--

Dave Peterson