Loop Until non-incrementing cell condition met.

[GBExcel via OfficeKB.com]

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

 

[Geoff_L]

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]

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.

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

 

[GBExcel via OfficeKB.com]

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