With Worksheets("Sheet2").Range("A:A")
Set c = .Find("JW F", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = Mid((c), 4, 8)
Set c = .FindNext(c)
if c is nothing then
exit do
end if
Loop
End If
End With

After the last cell with "JW F" is found and changed, then the next .Findnext
will return nothing (same as not found).

The loop statent tries to use c.address and that won't work when c is nothing.

Ps. I'd change that .find line to something like:

Set c = .Find(What:="JW F*", After:=.cells(1), LookIn:=xlvalues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

If you don't specify exactly what you want, you'll inherit the settings from the
previous find--either from the user (via edit|Find) or by code.

And by looking for "JW F*" and xlwhole, you'll be looking for strings that begin
with "JW F".

Wendy wrote:

Hi

I've copied this out of the help in Excel 2003 and amended it for my use, it
works and corrects the data but I get a debug error on the Loop line.

The data is

JW F1871810 and I want it to be F1871810

With Worksheets("Sheet2").Range("A:A")
Set c = .Find("JW F", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = Mid((c), 4, 8)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

Thanks

Wendy

--

Dave Peterson