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

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

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

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

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

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.

--
Regards,
Tom Ogilvy

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

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
"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

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

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


--
Don Guillett
SalesAid Software

"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

Sorry Don, I didn't mean to reply to sender. This didn't help either.
"Don Guillett" wrote in message
...
Didn't look too hard but try just
c instead of c.value


--
Don Guillett
SalesAid Software

"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

Not a biggie. I see you got a good answer

--
Don Guillett
SalesAid Software

"Mike Fogleman" wrote in message
...
Sorry Don, I didn't mean to reply to sender. This didn't help either.
"Don Guillett" wrote in message
...
Didn't look too hard but try just
c instead of c.value


--
Don Guillett
SalesAid Software

"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

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
"Don Guillett" wrote in message
...
Not a biggie. I see you got a good answer

--
Don Guillett
SalesAid Software

"Mike Fogleman" wrote in message
...
Sorry Don, I didn't mean to reply to sender. This didn't help either.
"Don Guillett" wrote in message
...
Didn't look too hard but try just
c instead of c.value


--
Don Guillett
SalesAid Software

"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