Error in Sub

[Wendy]

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]

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".

 

[Bob Phillips]

If you are looking for a cell with JW F in it, the Mid(c,4,8) is pointless,
it will always return just F. And because you modify the cell, it will never
loop back to the firstcell, so testing for it is pointless.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Wendy]

Thanks, Dave that worked. It does mean that microsoft's excel 2003's help
doesnt! - At least in that example.

Wendy

 

[Dave Peterson]

MS changed the sample in its help.

At one time, it changed the color of the font (or fill). This meant that the
..findnext() would still find it again.

But when they decided to change the value of that found cell, they broke their
sample code.