Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Loop
Hi. The following Loop works well until all of the items
have been replaced. In the final loop, I receive the following error: "Object variable or With Block Variable not set." Any ideas? Thanks, Mike. -------- 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Loop
Mike,
The problem is that if c is nothing, c.Address will fail with an error 91. Try something like the following: Dim C As Range Dim FirstAddress As String Dim Done As Boolean 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 Done = True Else If C.Address = FirstAddress Then Done = True End If End If Loop While Done = False End If End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mike" wrote in message ... Hi. The following Loop works well until all of the items have been replaced. In the final loop, I receive the following error: "Object variable or With Block Variable not set." Any ideas? Thanks, Mike. -------- 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Loop
Hi Mike,
Odd isn't it. This is straight from help and it doesn't work The reason that it does not work is because when it is not found, c is nothing, and thus it is impossible to get the address of c. AFAICS all you need is Loop While Not c Is Nothing -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mike" wrote in message ... Hi. The following Loop works well until all of the items have been replaced. In the final loop, I receive the following error: "Object variable or With Block Variable not set." Any ideas? Thanks, Mike. -------- 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Loop
Chip:
That works great! Could you tell me if there would be a way to also clear the contents of three cells to the right of where the value was found? Thanks again, Mike. -----Original Message----- Mike, The problem is that if c is nothing, c.Address will fail with an error 91. Try something like the following: Dim C As Range Dim FirstAddress As String Dim Done As Boolean 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 Done = True Else If C.Address = FirstAddress Then Done = True End If End If Loop While Done = False End If End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mike" wrote in message ... Hi. The following Loop works well until all of the items have been replaced. In the final loop, I receive the following error: "Object variable or With Block Variable not set." Any ideas? Thanks, Mike. -------- 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Loop
Bob:
Thanks! It is odd ... It's neat that you recognized this from HELP. Thanks again, Mike. -----Original Message----- Hi Mike, Odd isn't it. This is straight from help and it doesn't work The reason that it does not work is because when it is not found, c is nothing, and thus it is impossible to get the address of c. AFAICS all you need is Loop While Not c Is Nothing -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mike" wrote in message ... Hi. The following Loop works well until all of the items have been replaced. In the final loop, I receive the following error: "Object variable or With Block Variable not set." Any ideas? Thanks, Mike. -------- 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Loop
c.Offset(0,1).resize(1,3).clearcontents -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mike" wrote in message ... Chip: That works great! Could you tell me if there would be a way to also clear the contents of three cells to the right of where the value was found? Thanks again, Mike. -----Original Message----- Mike, The problem is that if c is nothing, c.Address will fail with an error 91. Try something like the following: Dim C As Range Dim FirstAddress As String Dim Done As Boolean 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 Done = True Else If C.Address = FirstAddress Then Done = True End If End If Loop While Done = False End If End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mike" wrote in message ... Hi. The following Loop works well until all of the items have been replaced. In the final loop, I receive the following error: "Object variable or With Block Variable not set." Any ideas? Thanks, Mike. -------- 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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Loop
I think I have looked up this help topic before and remembered it. Although
I never use it in total as you did. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mike" wrote in message ... Bob: Thanks! It is odd ... It's neat that you recognized this from HELP. Thanks again, Mike. -----Original Message----- Hi Mike, Odd isn't it. This is straight from help and it doesn't work The reason that it does not work is because when it is not found, c is nothing, and thus it is impossible to get the address of c. AFAICS all you need is Loop While Not c Is Nothing -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mike" wrote in message ... Hi. The following Loop works well until all of the items have been replaced. In the final loop, I receive the following error: "Object variable or With Block Variable not set." Any ideas? Thanks, Mike. -------- 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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Loop
Thanks again, Bob!
Mike. -----Original Message----- c.Offset(0,1).resize(1,3).clearcontents -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mike" wrote in message ... Chip: That works great! Could you tell me if there would be a way to also clear the contents of three cells to the right of where the value was found? Thanks again, Mike. -----Original Message----- Mike, The problem is that if c is nothing, c.Address will fail with an error 91. Try something like the following: Dim C As Range Dim FirstAddress As String Dim Done As Boolean 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 Done = True Else If C.Address = FirstAddress Then Done = True End If End If Loop While Done = False End If End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mike" wrote in message ... Hi. The following Loop works well until all of the items have been replaced. In the final loop, I receive the following error: "Object variable or With Block Variable not set." Any ideas? Thanks, Mike. -------- 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
do while loop problem | Excel Discussion (Misc queries) | |||
loop problem | Excel Discussion (Misc queries) | |||
Does loop function cause this problem? | Excel Worksheet Functions | |||
For..Next loop problem | Excel Programming | |||
loop problem | Excel Programming |