Problem with excel VBA 'Loop While'. Cannot seem to find boolean value to end the loop

Question

Good morning everyone. I am building a project finance model which requires me to break some of the circularities by using copy/paste circular tables.

Now, I'm using the code where one of the cells in the "Fin Statements" tab is required to go to True before the loop should cuut off. But it's not working for some reason. I need your help fixing the code.

Sub Cir_Reinvestment()
'
' Cir_Reinvestment Macro
    
' For Scenario 1
    
    Dim I As Long
    Dim Rngcashchk As Boolean

    'Cell where sheet checks for True/False statement
    Rngcashchk = Sheets("Fin Statements").Cells(105, E)
    


    'Copy Paste Actions start here
    
    Sheets("Inputs").Select
    Range("Macro.Cashflow.Closing.Copy").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Range("Macro.Cashflow.Closing.Paste").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Inputs").Select
    Range("MacroRS.Invested.Fund.Copy").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("MacroRS.Invested.Fund.Paste").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("MacroRS.REIncome.Copy").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("MacroRS.REIncome.Paste").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("Macro.Cashflow.Closing.Copy").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Range("Macro.Cashflow.Closing.Paste").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
'Loop should end once Cell is True

 Loop Until Rngcashchk = True

End Sub

**1.** Where is the `Do` part of the loop? **2.** You may also want to see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Siddharth Rout, Mar 08 '21 at 06:05
You don't appear to be changing Rngcashchk anywhere in the posted code. Will the value in the cell you are getting it's initial value from change as the code executes? — norie, Mar 08 '21 at 06:19
When should it end, the Rngcashchk never changes. Is it posible that Cell(105, E) is part of one of the ranges? — snenson, Mar 08 '21 at 08:19
*But it's not working* doesn't help. Is there an error? If so what are its number and description? If `E` isn't a defined name, then `Cells(105, E)` will not work. Use `Cells(105, "E")` instead. Could you replace the named ranges with the actual addresses (there are only 6 of them)? — VBasic2008, Mar 08 '21 at 08:28
Ok, it's working with the "E" but the loop doesn't end. What to do about that? — Majajun, Mar 08 '21 at 20:52

score 0 · Answer 1 · answered Mar 08 '21 at 08:49

0

I don't see keyword Do in front of Loop Until
By the way, selecting a range before copy/paste it is unnessary. You can just do like this: Range("A1").Copy Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False
You can call Application.CutCopyMode = False once at the end of all copy-paste progress

answered Mar 08 '21 at 08:49

judaikun88

59
5

I'm beginning to learn so basic errors probably. 1. Isn't Do supposed to be where the loop starts? 2. I'll add that to the code. 3. Will add that too. Thanks. – Majajun Mar 08 '21 at 18:20

score 0 · Answer 2 · answered Mar 08 '21 at 09:30

0

Since I dont' know exactly what isn't working, it's hard to help you.
But here is a suggestion on how you could write the code, if it always follows the same pattern with the named ranges, as per the example.
Since we are doing the same operating several times, I've broken that out into a separate sub that we can call from the main loop.
But I would NOT recommend running this code since there is nothing stopping ot from looping forever. Therefore I have commented out the loop for now.

Sub Cir_Reinvestment()
  Dim Rngcashchk As Boolean
  Rngcashchk = Sheets("Fin Statements").Cells(105, "E")

  'Do
      CopyPaste ("Macro.Cashflow.Closing")
      CopyPaste ("MacroRS.Invested.Fund")
      CopyPaste ("MacroRS.REIncome")
  'Loop Until Rngcashchk

End Sub


Sub CopyPaste(rng As String)

  Sheets("Inputs").Range(rng & ".Copy", Range(rng & ".Copy").End(xlToRight)).copy
  Sheets("Inputs").Range(rng & ".Paste").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

End Sub

answered Mar 08 '21 at 09:30

Christofer Weber

1,464
1
9
18

I'm looking to add a loop limiter, how do I do that? – Majajun Mar 08 '21 at 19:35
The issue with my code was that the loop doesn't stop. How do I make VBA stop the loop? How do I make it read the True statement in Cell (105, E) in 'Fin Statements' worksheet so that when the Cell turns true, the copy-paste cycle stops. – Majajun Mar 08 '21 at 20:54
It would be easier to end the loop within the code. How are you controlling and changing the statement in E105? If it's always true, there won't be a loop, if it's not true, it will be an infinite loop. How many times do you want it to loop? And, if it's a variable amount, how do you decide? – Christofer Weber Mar 08 '21 at 22:04
So it's a true/false statement dependent on a condition being met. The condition is met when after x number of copy-pastes, it will eventually turn True. So I need it to stop when the statement changes from False to True in E105. – Majajun Mar 09 '21 at 02:39
Since we aren't afffecting that cell directly, I'm guessing there's some kind of formula in it that checks the progress? Make sure that calculates properly on each loop (but it should do). If it's just a number, it's easier to pu in the code. – Christofer Weber Mar 09 '21 at 07:07
Thanks, I'll take a look. Appreciate the feedback! – Majajun Mar 09 '21 at 07:26
Since we don't know what is in E105 and how the rest of the work book looks, it's hard to guess. But it does feel strange to loop a copy/paste action to the same range. – Christofer Weber Mar 09 '21 at 11:39

VBasic2008 · Answer 3 · 2021-03-08T09:52:08.960

Circular

Not tested (hopefully CashChk will become True).
Rethink if you really need to copy the first range twice (at the beginning and after the loop).

The Code

Option Explicit

Sub Cir_Reinvestment()
'
' Cir_Reinvestment Macro
    
' For Scenario 1
    
    Dim CopyRanges As Variant
    CopyRanges = Array("Macro.Cashflow.Closing.Copy", _
        "MacroRS.Invested.Fund.Copy", "Macro.Cashflow.Closing.Copy")
    Dim PasteRanges As Variant
    PasteRanges = Array("Macro.Cashflow.Closing.Paste", _
        "MacroRS.Invested.Fund.Paste", "Macro.Cashflow.Closing.Paste")
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim First As Long: First = LBound(CopyRanges)
    Dim Last As Long: Last = UBound(CopyRanges)
    Dim crg As Variant: ReDim crg(First, Last)
    Dim prg As Variant: ReDim prg(First, Last)
    
    Dim n As Long
    With wb.Worksheets("Inputs")
        For n = First To Last
            With .Range(CopyRanges(n))
                Set crg(n) = .Parent.Range(.Offset, .End(xlToRight))
            End With
            With .Range(PasteRanges(n))
                Set prg(n) = .Resize(crg(n).Rows.Count, crg(n).Columns.Count)
            End With
        Next n
    End With
    
    Dim cashRg As Range
    Set cashRg = wb.Worksheets("Fin Statements").Cells(105, "E")
    
    Dim CashChk As Boolean
    Do
        CashChk = cashRg.Value
        For n = First To Last
            prg(n).Value = crg(n).Value
        Next n
        prg(First).Value = crg(First).Value
    Loop Until CashChk = True

End Sub

I'll try this code out and see how it goes. Thanks. New to VBA so trying to learn the ropes. — Majajun, Mar 08 '21 at 19:34
Yes, the the same paste has to happen at the start and end to close the loop completely and balance stuff in the sheets. 4 — Majajun, Mar 08 '21 at 21:02
I tried the code it says "Script out of Range" at Ln27 Set crg(n) = .Parent.Range(.Offset, .End(xlToRight)) — Majajun, Mar 08 '21 at 21:04
Are you copy/pasting in one worksheet or are there multiple sheets? If one, adjust "Inputs" to the actual name. If multiple then please do clarity. — VBasic2008, Mar 08 '21 at 21:31
There are multiple sheets. The true/false statement is in 'Fin Statements' which is needed to stop the loop when it turns True from false. — Majajun, Mar 09 '21 at 02:40

Problem with excel VBA 'Loop While'. Cannot seem to find boolean value to end the loop

3 Answers3

Circular