I have a data base that starts at cell AA2(Numbers), my macro works on columns to right of this and copies the result to a report, it then refreshes line AA2. I am using "Do ....Loop Until AA2<1" but it does not work. Any help appreciated, I've been days on this.BR Adrian Hudd

there will be a button to accept an answer as "Official Answer" or "Best Solution"just click on that

Excel Loop problems | Microsoft Community Hub

9 Replies

bhushan_z

Iron Contributor

May 18, 2020

can u pls share your file or VBA code?

WorkTests44

Copper Contributor

May 18, 2020

bhushan_z

As requested I have copied the macro, It works on first run but will not loop. I'm new to this, so it could be syntax. All help aprectiated.

Sub WhatIf()

Sub WhatIf()
'
' WhatIf Macro
'

'copy Wind data etc to Course

    Range("E2:E8").Select
    Selection.Copy
    Sheets("Course").Select
    Range("I2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
   'copy course to side cells
   
    Range("H15:O54").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("WhatIF").Select
    ActiveWindow.LargeScroll ToRight:=1
    Range("AA2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.LargeScroll ToRight:=-1
    
    'Copy First line to main panel (Loop)
    
    Do

    ActiveWindow.LargeScroll ToRight:=1
    Range("AA2:AH2").Select
    Selection.Copy
    ActiveWindow.LargeScroll ToRight:=-1
    Range("D13").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
'Copy Buoy to top line of course and then Find Dist,Bearing,Time to What If Front panel

    Range("D13:K13").Select
    Selection.Copy
    Sheets("Course").Select
    Range("H13").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Distance").Select
    Range("E9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("WhatIF").Select
    Range("I13").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Distance").Select
    Range("E5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("WhatIF").Select
    Range("J13").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Time").Select
    Range("F9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("WhatIF").Select
    Range("K13").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
'Copy Wind and Tide info to "calcs" and TWA to "360"

    Range("E2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Calcs").Select
    Range("D47").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Bearing").Select
    Range("D14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Calcs").Select
    Range("F47").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H47").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("M 360 deg").Select
    Range("S2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("WhatIF").Select
    Range("E13").Select
    
'Copy Distance and Time for additions + copy first line down.

    ActiveWindow.LargeScroll ToRight:=1
    Range("W3:Y3").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveWindow.LargeScroll ToRight:=-1
    Range("D14:K14").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("I13").Select
    Selection.Copy
    ActiveWindow.LargeScroll ToRight:=1
    Range("W3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.LargeScroll ToRight:=-1
    Range("K13").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.LargeScroll ToRight:=1
    Range("X3").Select
    Application.CutCopyMode = False
    ActiveWindow.LargeScroll ToRight:=-1
    Range("I13").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.LargeScroll ToRight:=1
    Range("K13").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.LargeScroll ToRight:=1
    Range("X3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.LargeScroll ToRight:=-1
    Range("D13:K13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=1
    Range("E13").Select
    
'Remove top line of Course

    ActiveWindow.LargeScroll ToRight:=1
    Range("AA2:AI2").Select
    Selection.Delete Shift:=xlUp
    ActiveWindow.LargeScroll ToRight:=-2
    Range("E13").Select
    
    Loop Until ("AA2<1")
      
    
End Sub

bhushan_z
Iron Contributor
May 18, 2020
WorkTests44 I see problem is in last line.
You need to clarify AA2 of which sheet?

Update it as below, in below line change "Course" with correct worksheet name where your cell AA2 is located.

Loop Until Sheets("Course").Range("AA2").Value < 1

Forum Discussion

Excel Loop problems

9 Replies

Resources