Do-Until Loop Not Recognizing Range Values

Question

I am trying to get the do-until loop that I have in my code to recognize a date (Date is in format M/DD/YY HH:MM) that adds a single minute to it each time around the loop and then terminate when that value is equal to a second, unchanging value. Here is what I have:

Do
    Range("A9").Select
    Selection.Copy
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R1C1+R1C72"
    Range("B1").Select
    Selection.Copy
    Range("A9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A9").Select
Loop Until ActiveCell.value = Range("RC74").value

This is a photo of what my sheet looks like with the data removed in the middle.

The two cells being compared were BW9 and A9. Sorry I wasnt very specific. The specialty pasting was to prevent a glitch with a third party software that is also being used.

as far as I can tell the value in A9 will always be the same. The formula in B1 becomes `=C2+BV2` which neither cell changes in the loop therefore B1 will always be the same and so will A9. Therefore if it does not equal RC74 (can't tell if mean the actual cell RC74, or if this an attempt to use R1C1 format, which can't be done like this) the first loop the loop will go on forever. — Scott Craner, Mar 03 '16 at 20:06
I'm going to bet it's being hindered by the use of `.Select` and `ActiveCell`. Try instead to work directly with the data. Read over [How to Avoid `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for some tips. I highly recommend doing so. If you can post the code outside of this loop, we can help as well. — BruceWayne, Mar 03 '16 at 20:09
What is the formula in B1 supposed to be, if it were to be put in the cell manually? — Scott Craner, Mar 03 '16 at 20:10
I updated it. Sorry about the wording I'm very new to VBA. Also, the final cell value for A9 should be identical to the value in BW9 — jmcole, Mar 03 '16 at 20:27

Scott Holtzman · Accepted Answer · 2016-03-03T21:24:15.373

1

You have a slew of unnecessary .Select and ActiveSheet statements.

I am also concerned that you are using R1C1 notation in a way that you do not intend.

Try the code below and adjust the R1C1 notation as needed, since it's hard to tell exactly what cells you are referring to.

Range("B1").FormulaR1C1 = "=RC[-1]+RC[72]" 'this makes B1 = A1+BU72 .. (72 columns from B) change as needed

Do

    Range("A1").Value = Range("A9").Value
    Range("B1").Calculate
    Range("A9").Value = Range("B1").Value

Loop Until Range("A9").Value >= Range("BW9").Value

edited Mar 03 '16 at 21:24

answered Mar 03 '16 at 20:13

Scott Holtzman

27,099
5
37
72

This was the correct cell names (see picture link above) but I couldn't get this formula to work. – jmcole Mar 03 '16 at 20:57
@jmcole - **I couldn't get this formula to work** - can you be more specific ... also, I just saw your other comment on the original question, see my edit – Scott Holtzman Mar 03 '16 at 20:59
Beyond the initial step of setting A1 and A9 equal, the loop refuses to loop successfully and the system hangs rather than further increasing the value of A9 by 1 minute. I originally attempted this problem using the another code ( will attach to my original) but there was no response from the machine once the code got to the loops. – jmcole Mar 03 '16 at 21:12
@jmcole - did you step through the code line-by-line (debugging) to see what is cause the issue or which line produces an unexpected behavior. – Scott Holtzman Mar 03 '16 at 21:13
1

@jmcole the problem is that date/time is very inaccurate. There is a lot of variation in the decimal number behind the date that will still return the same second let alone the same minute. Scott, change the loop until criterion to `Range("A9").Value >= Range("BW9").Value ` – Scott Craner Mar 03 '16 at 21:19
1

@ScottCraner - Thanks for pointing that out. I was thinking of doing that but was waiting to hear back from the user. Worth changing anyway, since it seems accurate in this case. – Scott Holtzman Mar 03 '16 at 21:25
I havent actually debugged this. Im unsure about VBA as a whole as it is a completely new language for me. Can one of you direct me to a page to learn the basics? I think I'm coding something too challenging. – jmcole Mar 03 '16 at 22:41

Do-Until Loop Not Recognizing Range Values

1 Answers1