Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FindNext Errors
I simply cannot get the Help example on FindNext to work. Even explicitly
setting the find paramaeters still results in err 91. The example cell value is changed but then errors out at Loop While. The problem seems to be with c.address. What am I missing? With Worksheets(1).Range("a1:a500") 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 Geoff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FindNext Errors
On Apr 26, 8:52 am, Geoff wrote:
I simply cannot get the Help example on FindNext to work. Even explicitly setting the find paramaeters still results in err 91. The example cell value is changed but then errors out at Loop While. The problem seems to be with c.address. What am I missing? With Worksheets(1).Range("a1:a500") 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 Geoff Hello Geoff, You first Find is looking for the number 2. The FindNext is looking for a 5. If you are searching for all 5's, change the 2 in the first Find call to a 5, Sincerely, Leith Ross |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
FindNext Errors
It's a problem with the sample code in VBA's help. In earlier versions, the
code didn't change the .value, it just changed the formatting (font or fill or something). In this version, the 2s are changed to 5s. After the last 2 is changed, then this line: set c = .findnext(c) will result in C being nothing -- it wasn't found. Excel's vba checks both conditions ("Not c is nothing" and "c.address < firstaddress") in that "while" statement. Since c is nothing, then c.address fails. Personally, I find it easier to just check to see if the code should drop out of the loop myself: Option Explicit Sub testme() Dim c As Range Dim FirstAddress As String With Worksheets(1).Range("a1:a500") Set c = .Find(2, LookIn:=xlValues) If Not c Is Nothing Then FirstAddress = c.Address Do c.Value = 5 Set c = .FindNext(c) If c Is Nothing Then Exit Do End If If c.Address = FirstAddress Then Exit Do End If Loop End If End With End Sub ps. I would also include all the parms on the .find statement. If you don't, then you'll get the same choices as the user made in the last Edit|Find or the choices made in last .find in code. Geoff wrote: I simply cannot get the Help example on FindNext to work. Even explicitly setting the find paramaeters still results in err 91. The example cell value is changed but then errors out at Loop While. The problem seems to be with c.address. What am I missing? With Worksheets(1).Range("a1:a500") 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 Geoff -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
FindNext Errors
Hi Leith
I don't think that can be. If you enter 2 in several rows of col A they are all changed to 5 correctly. The error occurs when no more 2's are found in the range and c becomes nothing. How can Nothing have an address is what I cannot fathom. Geoff "Leith Ross" wrote: On Apr 26, 8:52 am, Geoff wrote: I simply cannot get the Help example on FindNext to work. Even explicitly setting the find paramaeters still results in err 91. The example cell value is changed but then errors out at Loop While. The problem seems to be with c.address. What am I missing? With Worksheets(1).Range("a1:a500") 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 Geoff Hello Geoff, You first Find is looking for the number 2. The FindNext is looking for a 5. If you are searching for all 5's, change the 2 in the first Find call to a 5, Sincerely, Leith Ross |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
FindNext Errors
Hi Dave
So my reasoning in my response to Leith was correct - if c is nothing how can it have an address? Thanks, I was going spare. I found if i removed c.address < firstaddress then it was ok but i think your solution is more explicit. Thanks again. Geoff "Dave Peterson" wrote: It's a problem with the sample code in VBA's help. In earlier versions, the code didn't change the .value, it just changed the formatting (font or fill or something). In this version, the 2s are changed to 5s. After the last 2 is changed, then this line: set c = .findnext(c) will result in C being nothing -- it wasn't found. Excel's vba checks both conditions ("Not c is nothing" and "c.address < firstaddress") in that "while" statement. Since c is nothing, then c.address fails. Personally, I find it easier to just check to see if the code should drop out of the loop myself: Option Explicit Sub testme() Dim c As Range Dim FirstAddress As String With Worksheets(1).Range("a1:a500") Set c = .Find(2, LookIn:=xlValues) If Not c Is Nothing Then FirstAddress = c.Address Do c.Value = 5 Set c = .FindNext(c) If c Is Nothing Then Exit Do End If If c.Address = FirstAddress Then Exit Do End If Loop End If End With End Sub ps. I would also include all the parms on the .find statement. If you don't, then you'll get the same choices as the user made in the last Edit|Find or the choices made in last .find in code. Geoff wrote: I simply cannot get the Help example on FindNext to work. Even explicitly setting the find paramaeters still results in err 91. The example cell value is changed but then errors out at Loop While. The problem seems to be with c.address. What am I missing? With Worksheets(1).Range("a1:a500") 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 Geoff -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
FindNext Errors
And depending on what you're doing with those found cells, you may want to use
the firstaddress check to get out of the loop. If you're changing formats or just retrieving values, then c would never be nothing and you'd be stuck forever in that loop. And forever is a long, long time <vbg. Geoff wrote: Hi Dave So my reasoning in my response to Leith was correct - if c is nothing how can it have an address? Thanks, I was going spare. I found if i removed c.address < firstaddress then it was ok but i think your solution is more explicit. Thanks again. Geoff "Dave Peterson" wrote: It's a problem with the sample code in VBA's help. In earlier versions, the code didn't change the .value, it just changed the formatting (font or fill or something). In this version, the 2s are changed to 5s. After the last 2 is changed, then this line: set c = .findnext(c) will result in C being nothing -- it wasn't found. Excel's vba checks both conditions ("Not c is nothing" and "c.address < firstaddress") in that "while" statement. Since c is nothing, then c.address fails. Personally, I find it easier to just check to see if the code should drop out of the loop myself: Option Explicit Sub testme() Dim c As Range Dim FirstAddress As String With Worksheets(1).Range("a1:a500") Set c = .Find(2, LookIn:=xlValues) If Not c Is Nothing Then FirstAddress = c.Address Do c.Value = 5 Set c = .FindNext(c) If c Is Nothing Then Exit Do End If If c.Address = FirstAddress Then Exit Do End If Loop End If End With End Sub ps. I would also include all the parms on the .find statement. If you don't, then you'll get the same choices as the user made in the last Edit|Find or the choices made in last .find in code. Geoff wrote: I simply cannot get the Help example on FindNext to work. Even explicitly setting the find paramaeters still results in err 91. The example cell value is changed but then errors out at Loop While. The problem seems to be with c.address. What am I missing? With Worksheets(1).Range("a1:a500") 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 Geoff -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
FindNext Errors
Sounds like a cue for a song. <g
But thanks again, I had spent far too long today trying to get their example to make sense. Geoff "Dave Peterson" wrote: And depending on what you're doing with those found cells, you may want to use the firstaddress check to get out of the loop. If you're changing formats or just retrieving values, then c would never be nothing and you'd be stuck forever in that loop. And forever is a long, long time <vbg. Geoff wrote: Hi Dave So my reasoning in my response to Leith was correct - if c is nothing how can it have an address? Thanks, I was going spare. I found if i removed c.address < firstaddress then it was ok but i think your solution is more explicit. Thanks again. Geoff "Dave Peterson" wrote: It's a problem with the sample code in VBA's help. In earlier versions, the code didn't change the .value, it just changed the formatting (font or fill or something). In this version, the 2s are changed to 5s. After the last 2 is changed, then this line: set c = .findnext(c) will result in C being nothing -- it wasn't found. Excel's vba checks both conditions ("Not c is nothing" and "c.address < firstaddress") in that "while" statement. Since c is nothing, then c.address fails. Personally, I find it easier to just check to see if the code should drop out of the loop myself: Option Explicit Sub testme() Dim c As Range Dim FirstAddress As String With Worksheets(1).Range("a1:a500") Set c = .Find(2, LookIn:=xlValues) If Not c Is Nothing Then FirstAddress = c.Address Do c.Value = 5 Set c = .FindNext(c) If c Is Nothing Then Exit Do End If If c.Address = FirstAddress Then Exit Do End If Loop End If End With End Sub ps. I would also include all the parms on the .find statement. If you don't, then you'll get the same choices as the user made in the last Edit|Find or the choices made in last .find in code. Geoff wrote: I simply cannot get the Help example on FindNext to work. Even explicitly setting the find paramaeters still results in err 91. The example cell value is changed but then errors out at Loop While. The problem seems to be with c.address. What am I missing? With Worksheets(1).Range("a1:a500") 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 Geoff -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Throwing Circular Errors When No Errors Exist | Excel Worksheet Functions | |||
Findnext | Excel Discussion (Misc queries) | |||
Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions | |||
FindNext | Excel Programming | |||
FindNext | Excel Programming |