Solved VBA looping errors occurring with FOR and NEXT statements

November 27, 2016 at 10:35:28
Specs: Windows 64
Sub PrintGrpAnalys()

    
    If MsgBox("YOU ARE ABOUT TO PRINT THE GRAPHS FOR THE AGM. ARE YOU SURE?", vbYesNo) = vbYes Then
    
' Declare variables.
    Dim MyRange As Range
    Dim MyCell As Range
    Dim cell As Range
' Define the target Range.
    Set MyRange = Sheets("GRPLIST").Range("K2:K49")
' Start looping through the range.
    For Each MyCell In MyRange
' Do something with each cell.
    If MyCell.Value = "" Then
        MsgBox ("ALL GRAPH CHARTS HAVE BEEN PRINTED")
             
     Exit Sub
     
     Else
     
      Sheets("ANALYS2").Cells.Select
        Selection.EntireRow.Hidden = False
        
' apply name to
    Sheets("ANALYS2").Range("H4") = MyCell.Value
 
' Define the target Range.
    For Each cell In Sheets("ANALYS2").Range("A9:A130")
    If (cell.Value) = "0" Then
    cell.EntireRow.Hidden = True
    
    
    Sheets("ANALYS2").Range("C2").Select

    ' prints individual analysis sheets
'
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    End If
    
    
        Next cell
    Next MyCell
     
    
End Sub

Hi Guys
Having trouble with the above with the FOR and Next statements, Where am I going wrong
Any ideas anyone
Regards
Tony


See More: VBA looping errors occurring with FOR and NEXT statements

Report •

#1
November 27, 2016 at 11:25:34
"Having trouble" is a bit vague. What's going wrong? Any error messages?

Report •

#2
November 27, 2016 at 11:44:47
Ooops that would help
Compile Error
Invalid Next control variable reference

This is occurring with the last line of the VBA Next MyCell


Report •

#3
November 27, 2016 at 17:48:20
✔ Best Answer
You have 3 Block If's and only one End If. That is confusing VBA.

Add the End If's where they belong and your For-Next error should resolve itself.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
November 28, 2016 at 02:21:17
Hi DD03
Thanks for the heads up, but I must still have something wrong
I've broken the order down to the following

a = If
b = For Each
c = If ...... Then
d = Else
e = For Each
f = If ...... Then
g = End If (g to f)
h = Exit For ( h to e)
i = End If ( I to c)
j = Next ( j to b)
k = End If (k to a)

Still have error 'End If without Block If' on ' i ' above, and not sure why. Your guidance would be much appreciated

message edited by tonygibb


Report •

#5
November 28, 2016 at 04:38:34
Hi DD03
Thanks fro the heads up, Finally found where I was going wrong
Got everything working as required

Tony


Report •

Ask Question