Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Loop Until non-incrementing cell condition met.
Hi,
I am a VBA dummy. I am stuck on the following: I have a cell GJ1 that will either have a 1 or a 0 value. My routine needs to loop until GJ1 changes from 0 to 1. The solutions that I have seen on the Internet use counters to count loops until a condition is met. In my case, counters or incrementing is not applicable since the condition that determines if GJ1 is 1 is not based on counters or incrementing but on user input. The example below is what I am trying. I can get the loop to work, but then it just keeps looping infinitely. Sub PasteValuesTill() ' The code below must loop until cell GJ1=1. GJ1 can only have values of 0 or 1. ' Sheets("ThisSheet").Select Range("E5:G5").Select Selection.Copy Range("F2:G2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Do Until GJ1 = 1 Loop End Sub -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Loop Until non-incrementing cell condition met.
It is not very clear why the value of GJ1 changes when you are recoopying the
same thing over and over again. However, assuming that the condition is met eventually there are two things that could be awry is your posted code is a true replication of the VBA. 1. Do Until GJ1 = 1 - VBA may be seeing GJ1 and a variable that is never assigned a value and therefore the condition is never met. (This assumes that you are not using Option Explicit). Try being a bit more specific with Range("GJ1") 2. Are you sure that the copying is returning the NUMBER 1 and not the STRING "1"? It may be that you are looking for one and returning the other. If this is helpful, please click Yes. "GBExcel via OfficeKB.com" wrote: Hi, I am a VBA dummy. I am stuck on the following: I have a cell GJ1 that will either have a 1 or a 0 value. My routine needs to loop until GJ1 changes from 0 to 1. The solutions that I have seen on the Internet use counters to count loops until a condition is met. In my case, counters or incrementing is not applicable since the condition that determines if GJ1 is 1 is not based on counters or incrementing but on user input. The example below is what I am trying. I can get the loop to work, but then it just keeps looping infinitely. Sub PasteValuesTill() ' The code below must loop until cell GJ1=1. GJ1 can only have values of 0 or 1. ' Sheets("ThisSheet").Select Range("E5:G5").Select Selection.Copy Range("F2:G2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Do Until GJ1 = 1 Loop End Sub -- Message posted via http://www.officekb.com . |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Loop Until non-incrementing cell condition met.
Hi Geoff,
I appreciate your reply and yes, it is valuable to help me learn. However, I don't know what the actual code that I should enter to correct my VBA script looks like, and being a VBA copy-and-paste newbie, I am not sure how to apply what you are saying to my script. The weakness is mine. To clarify. Geoff_L wrote: It is not very clear why the value of GJ1 changes when you are recopying the same thing over and over again. GJ1 is set up to test if the sum of 1 row [ Range("E5:G5") ] is equal to the sum of another row [ Range("F2:G2") ]. I have a set of highly complicated tables that run routines that sort columns into various lengths. The results of these columns [ Range("E5:G5") ] needs to be checked against a validating row [ Range("F2:G2") ] to see if the routines must run again. Hence, the script below, that I need to loop. This must continue until the sum of the 2 rows is equal to each other. If GJ1 =0, it means that the sum of the 2 rows is not equal to each other and the loop must run again. If it is equal to 1 the script must stop. 1. Do Until GJ1 = 1 - VBA may be seeing GJ1 and a variable that is never assigned a value and therefore the condition is never met. (This assumes that you are not using Option Explicit). Try being a bit more specific with Range("GJ1") I hear what you are saying here, but I don't know how to apply it. 2. Are you sure that the copying is returning the NUMBER 1 and not the STRING "1"? It may be that you are looking for one and returning the other. As above, I hear what you are saying here, but I don't know how to apply it. I was born somewhat dyslexic, which is why copy-and-paste works best for me. If I can see the correct code, it helps me to understand the explanation. I appreciate your help. GBExcel -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200911/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Loop Until non-incrementing cell condition met.
I like to put solutions back online to show appreciation. Maybe it will help
others too. So here is what worked for me. Do While Range("GJ1") = 0 [My Macro] Loop I set the cell format to 'Number.' Thanks for helping. GBExcel -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200911/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Incrementing a formula within an excel cell | Excel Worksheet Functions | |||
Incrementing cell addresses | Excel Worksheet Functions | |||
Incrementing cell reference | Excel Discussion (Misc queries) | |||
incrementing mm/yy based on value in other cell | Excel Worksheet Functions | |||
Incrementing Cell Numbers | Excel Discussion (Misc queries) |