Code bombing on Loop While line
 

The below code I copied from the Help screen under the Find Method of the Range
Object:
It is properly changing 2 values to 5 values, but before concluding it is
"bombing" on the 3rd from the last line (below);
Can someone spot what the problem is?
TIA,

With Worksheets(1).Range("A1:A10")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With

Code bombing on Loop While line
 

Jim,

Try this

With Worksheets(1).Range("A1:A10")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JMay" wrote in message
news:xoYrf.105610$WH.103657@dukeread01...
The below code I copied from the Help screen under the Find Method of the
Range
Object:
It is properly changing 2 values to 5 values, but before concluding it is
"bombing" on the 3rd from the last line (below);
Can someone spot what the problem is?
TIA,

With Worksheets(1).Range("A1:A10")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With

Code bombing on Loop While line
 

JMay,

I notice that the code you used is straight from XL Help (2003 at least) and
is also shown this way on MS 's web site. The reason it didn't work is
because the found values are changed, which means that c will eventually be
nothing when all the 2's are found. This generates the error ,i.e., the
address of nothing. Conversely if you are changing a property of the found
cells, e.g., the color, you need the "c.address < firstaddress". I think
you could eliminate the "not c is nothing" (at least in 2003, not sure about
earlier):

With Worksheets(1).Range("a1:a10")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Interior.ColorIndex = 5
Set c = .FindNext(c)
Loop While c.Address < firstaddress
End If
End With

Maybe more than you wanted to know, but it tool me a while to figure out...

Doug


"JMay" wrote in message
news:xoYrf.105610$WH.103657@dukeread01...
The below code I copied from the Help screen under the Find Method of the
Range Object:
It is properly changing 2 values to 5 values, but before concluding it is
"bombing" on the 3rd from the last line (below);
Can someone spot what the problem is?
TIA,

With Worksheets(1).Range("A1:A10")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With

Code bombing on Loop While line
 

Doug Thanks,
Bob, I'm sure recognized in a second (what the problem was)
and submitted revise code;
I'm sure you have seen it bye now,,,
Tks,
Jim


"Doug Glancy" wrote in message
...
JMay,

I notice that the code you used is straight from XL Help (2003 at least) and
is also shown this way on MS 's web site. The reason it didn't work is
because the found values are changed, which means that c will eventually be
nothing when all the 2's are found. This generates the error ,i.e., the
address of nothing. Conversely if you are changing a property of the found
cells, e.g., the color, you need the "c.address < firstaddress". I think you
could eliminate the "not c is nothing" (at least in 2003, not sure about
earlier):

With Worksheets(1).Range("a1:a10")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Interior.ColorIndex = 5
Set c = .FindNext(c)
Loop While c.Address < firstaddress
End If
End With

Maybe more than you wanted to know, but it tool me a while to figure out...

Doug


"JMay" wrote in message
news:xoYrf.105610$WH.103657@dukeread01...
The below code I copied from the Help screen under the Find Method of the
Range Object:
It is properly changing 2 values to 5 values, but before concluding it is
"bombing" on the 3rd from the last line (below);
Can someone spot what the problem is?
TIA,

With Worksheets(1).Range("A1:A10")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With