Having trouble "deciphering" some code so I can see how it works

[zookeepertx]

Hello again!

I've got a macro that was written by someone else and am trying to type out a description of how it works in case I - or someone else - might be able to use some of it to create another macro one day. But the Ifs and EndIfs aren't distributed in an orderly (to me) way. There don't seem to be enough EndIfs to match up with the Ifs. And some of the lines have colons in the middle that seem to take the place of an "enter" key causing what I'm pretty sure should be on its own line to appear with the previous line. (I know that was confusing but I'm trying to explain as best I can but I suck at that ) I tried to parse it out to make it all match up; been working on it for 4 afternoons now and am sick enough of it to ask for help!!
I thought I had it and created a new module (yes, I did keep the old one. Learned my lesson on that subject!) But the new way doesn't work when I try to run it; I end up with a "Next without For" error.
I'm going to post below the original code and then what I created. I hope someone can clue me in on how the original code should be formatted, as in splitting lines instead of having colons or figuring out why the original code works without enough Ifs for the EndIfs.
I've tried to color or bold the portions of the code below that are really messing me up but it won't let me. It's the "For R = 4" just before "QLoop" down to "Next R" just before "ExitSub"
I'll be amazed and forever impressed by anyone that can figure this mess out!

Jenny

Sub ABC():
'JennyD05152015

Dim AD As Date, c As String, p As Integer, Q, Z, UR, R As Long, s As Long
Dim F As String, H As Range

'Add headers for new sections to the right of original data
'Also goes to Sub Outline to put border around headers
Headers:
Set H = Range("H1:AE3"): H.HorizontalAlignment = xlCenter: H.VerticalAlignment = xlCenter
    Set H = Range("H1:M1"): H.MergeCells = True: H.Value = "Flat Units"
        With Range("H1:M1").Borders
        .LineStyle = xlContinuous
        .Weight = xlMedium
        End With
    Set H = Range("N1:S1"): H.MergeCells = True: H.Value = "Cosmetic Units"
        With Range("N1:S1").Borders
        .LineStyle = xlContinuous
        .Weight = xlMedium
        End With
    Set H = Range("T1:Y1"): H.MergeCells = True: H.Value = "Apparel Y Units"
        With Range("T1:Y1").Borders
        .LineStyle = xlContinuous
        .Weight = xlMedium
        End With
    Set H = Range("Z1:AE1"): H.MergeCells = True: H.Value = "Apparel N Units"
        With Range("Z1:AE1").Borders
        .LineStyle = xlContinuous
        .Weight = xlMedium
        End With
                       
'Adds A, B or C to new section row 2 and LY or TY to new section row 3
                            For R = 8 To 30 Step 6
            Cells(2, R) = "A": Cells(2, R + 2) = "B": Cells(2, R + 4) = "C": Next R
            For R = 8 To 30 Step 2: Cells(3, R) = "LY": Cells(3, R + 1) = "TY": Next R
              
                Q = Cells(2, 7).Resize(1, 25): Cells(2, 7).Resize(1, 25).Value = 0
                Z = Cells(2, 7).Resize(1, 25): Cells(2, 7).Resize(1, 25) = Q: Q = Z
                      
    ActiveSheet.UsedRange.Offset(3, 6).Clear: Range("G:G").NumberFormat = "mmm d, yyyy"
  
'Adds up total units for each designation and enters them in the corresponding cell
                    AD = Cells(4, 1): Q(1, 1) = AD: s = 3
For R = 4 To Range("A" & Rows.count).End(xlUp).Row
QLoop:
            If Cells(R, 1) = AD Then
            c = Cells(R, 5): UR = Cells(R, 6)

            If InStr(1, c, "FLAT") Or InStr(1, c, "MARK") Then
                    p = 2
            ElseIf InStr(1, c, "COSMETICS") Then
                            p = 8
            Else:                   p = 14
                    If Cells(R, 3) = "N" Then p = 20
            End If
                    p = p + 2 * (Asc(UCase(Cells(R, 2))) - 65)
                    If Year(AD) = Year(Date) Then p = p + 1
                          
                        Q(1, p) = Q(1, p) + UR
            Else
            s = s + 1: Cells(s, 7).Resize(1, 25) = Q: Q = Z
                  
                    If Weekday(Cells(R + 1, 1)) < Weekday(Cells(s, 7)) Or Cells(R, 1) = "Summary" Then
          
                        If Weekday(Cells(s, 7)) = 6 Then
            s = s + 1: Cells(s, 7) = Cells(s - 1, 7) + 1
                        Cells(s, 8).Resize(1, 24).Value = 0: End If
                      
            s = s + 1:  Cells(s, 8).Resize(1, 24).Formula = "=SUM(R[-6]C:R[-1]C)"
            s = s + 1:  End If
          
                If Not IsDate(Cells(R, 1)) Then GoTo ExitSub
            AD = Cells(R, 1): Q(1, 1) = AD: GoTo QLoop: End If
            Next R
ExitSub:
                F = "=Sum(R[" & -s + 3 & "]C:R[-2]C)/2"
            s = s + 1: Cells(s, 8).Resize(1, 24).Formula = F
          
Tailers:    Set H = Range("N1:AE" & s): H.Cut Range("H" & s + 2)
            Set H = Range("N" & s + 2 & ":Y" & 2 * s + 1): H.Cut Range("H" & 2 * s + 3)
            Set H = Range("N" & 2 * s + 3 & ":S" & 3 * s + 2): H.Cut Range("H" & 3 * s + 4)
            Set H = Range("G4:G" & s): H.Copy Range("G" & s + 5)
            H.Copy Range("G" & 2 * s + 6): H.Copy Range("G" & 3 * s + 7)
          
Columns("G:M").EntireColumn.AutoFit

End Sub

Sub ABC():
'JennyD05152015

Dim AD As Date, c As String, p As Integer, Q, Z, UR, R As Long, s As Long
Dim F As String, H As Range

'Add headers for new sections to the right of original data
Headers:
Set H = Range("H1:AE3")
    H.HorizontalAlignment = xlCenter
    H.VerticalAlignment = xlCenter

Set H = Range("H1:M1")
    H.MergeCells = True
    H.Value = "Flat Units"
    With Range("H1:M1").Borders
    .LineStyle = xlContinuous
    .Weight = xlMedium
    End With
Set H = Range("N1:S1")
    H.MergeCells = True
    H.Value = "Cosmetic Units"
    With Range("N1:S1").Borders
    .LineStyle = xlContinuous
    .Weight = xlMedium
    End With
Set H = Range("T1:Y1")
H.MergeCells = True
H.Value = "Apparel Y Units"
    With Range("T1:Y1").Borders
    .LineStyle = xlContinuous
    .Weight = xlMedium
    End With
Set H = Range("Z1:AE1")
    H.MergeCells = True
    H.Value = "Apparel N Units"
    With Range("Z1:AE1").Borders
    .LineStyle = xlContinuous
    .Weight = xlMedium
    End With
                       
'Adds A, B or C to new section row 2 and LY or TY to new section row 3
For R = 8 To 30 Step 6
    Cells(2, R) = "A": Cells(2, R + 2) = "B"
    Cells(2, R + 4) = "C"
Next R
For R = 8 To 30 Step 2
    Cells(3, R) = "LY"
    Cells(3, R + 1) = "TY"
Next R
              
Q = Cells(2, 7).Resize(1, 25): Cells(2, 7).Resize(1, 25).Value = 0
Z = Cells(2, 7).Resize(1, 25): Cells(2, 7).Resize(1, 25) = Q: Q = Z
                      
ActiveSheet.UsedRange.Offset(3, 6).Clear
Range("G:G").NumberFormat = "mmm d, yyyy"
  
'Adds up total units for each designation and enters them in the corresponding cell
AD = Cells(4, 1)
Q(1, 1) = AD
s = 3

For R = 4 To Range("A" & Rows.count).End(xlUp).Row
QLoop:
    If Cells(R, 1) = AD Then
        c = Cells(R, 5)
        UR = Cells(R, 6)

        If InStr(1, c, "FLAT") Or InStr(1, c, "MARK") Then
            p = 2
        ElseIf InStr(1, c, "COSMETICS") Then
            p = 8
        Else
            p = 14
            If Cells(R, 3) = "N" Then
                p = 20
        End If
        p = p + 2 * (Asc(UCase(Cells(R, 2))) - 65)
        If Year(AD) = Year(Date) Then
            p = p + 1
        Q(1, p) = Q(1, p) + UR
    Else
    s = s + 1
    Cells(s, 7).Resize(1, 25) = Q
    Q = Z
          
        If Weekday(Cells(R + 1, 1)) < Weekday(Cells(s, 7)) Or Cells(R, 1) = "Summary" Then
  
            If Weekday(Cells(s, 7)) = 6 Then
                s = s + 1
                Cells(s, 7) = Cells(s - 1, 7) + 1
                Cells(s, 8).Resize(1, 24).Value = 0
            End If
              
        s = s + 1
        Cells(s, 8).Resize(1, 24).Formula = "=SUM(R[-6]C:R[-1]C)"
        s = s + 1
    End If
  
    If Not IsDate(Cells(R, 1)) Then
        GoTo ExitSub

    AD = Cells(R, 1)
    Q(1, 1) = AD
    GoTo QLoop
    End If
Next R
ExitSub:
    F = "=Sum(R[" & -s + 3 & "]C:R[-2]C)/2"
    s = s + 1
    Cells(s, 8).Resize(1, 24).Formula = F
          
Tailers:
    Set H = Range("N1:AE" & s)
        H.Cut Range("H" & s + 2)
    Set H = Range("N" & s + 2 & ":Y" & 2 * s + 1)
        H.Cut Range("H" & 2 * s + 3)
    Set H = Range("N" & 2 * s + 3 & ":S" & 3 * s + 2)
        H.Cut Range("H" & 3 * s + 4)
    Set H = Range("G4:G" & s)
        H.Copy Range("G" & s + 5)
        H.Copy Range("G" & 2 * s + 6)
        H.Copy Range("G" & 3 * s + 7)
          
Columns("G:M").EntireColumn.AutoFit

End Sub

 

[Jeffrey Mahoney]

So I cleaned up the first VBA section you posted. I got rid of all the Colons that continue FOR loops on single lines. Way too confusing. What I noticed right away is that you have 3 FOR loops all using the same R Variable within each other. That is going to cause nightmares in my opinion. Because you're changing a running variable from the parent FOR loop. I just don't see how that could work.

There is only one NEXT R statement. You need two more to finish the other two FOR loops. You really need to change those variables on those FOR loops

When you line up the IF with the END IF using the correct indentation it seems to make sense.

Another thing: having GOTO statements that start at the beginning of a FOR loop is NOT going to iterate the the FOR variable, if you jump below the FOR R = 4 to bla bla, the macro will not increase R during that go around. You should be jumping to the bottom before the NEXT R so that the variable can iterate.

Sub ABC():
'JennyD05152015

  Dim AD As Date, c As String, p As Integer, Q, Z, UR, R As Long, s As Long
  Dim F As String, H As Range
 
  'Add headers for new sections to the right of original data
  'Also goes to Sub Outline to put border around headers
Headers:
  Set H = Range("H1:AE3"): H.HorizontalAlignment = xlCenter: H.VerticalAlignment = xlCenter
  Set H = Range("H1:M1"): H.MergeCells = True: H.Value = "Flat Units"
  With Range("H1:M1").Borders
    .LineStyle = xlContinuous
    .Weight = xlMedium
  End With
  Set H = Range("N1:S1"): H.MergeCells = True: H.Value = "Cosmetic Units"
  With Range("N1:S1").Borders
    .LineStyle = xlContinuous
    .Weight = xlMedium
  End With
  Set H = Range("T1:Y1"): H.MergeCells = True: H.Value = "Apparel Y Units"
  With Range("T1:Y1").Borders
    .LineStyle = xlContinuous
    .Weight = xlMedium
  End With
  Set H = Range("Z1:AE1"): H.MergeCells = True: H.Value = "Apparel N Units"
  With Range("Z1:AE1").Borders
    .LineStyle = xlContinuous
    .Weight = xlMedium
  End With
                      
'Adds A, B or C to new section row 2 and LY or TY to new section row 3
  For R = 8 To 30 Step 6
    Cells(2, R) = "A": Cells(2, R + 2) = "B": Cells(2, R + 4) = "C": Next R
    For R = 8 To 30 Step 2: Cells(3, R) = "LY": Cells(3, R + 1) = "TY": Next R
             
    Q = Cells(2, 7).Resize(1, 25): Cells(2, 7).Resize(1, 25).Value = 0
    Z = Cells(2, 7).Resize(1, 25): Cells(2, 7).Resize(1, 25) = Q: Q = Z
         
    ActiveSheet.UsedRange.Offset(3, 6).Clear: Range("G:G").NumberFormat = "mmm d, yyyy"
 
    'Adds up total units for each designation and enters them in the corresponding cell
    AD = Cells(4, 1): Q(1, 1) = AD: s = 3
    For R = 4 To Range("A" & Rows.Count).End(xlUp).Row
QLoop:
      If Cells(R, 1) = AD Then
        c = Cells(R, 5): UR = Cells(R, 6)
       
        If InStr(1, c, "FLAT") Or InStr(1, c, "MARK") Then
          p = 2
        ElseIf InStr(1, c, "COSMETICS") Then
          p = 8
        Else
          p = 14
          If Cells(R, 3) = "N" Then p = 20
        End If
        p = p + 2 * (Asc(UCase(Cells(R, 2))) - 65)
        If Year(AD) = Year(Date) Then p = p + 1
         
        Q(1, p) = Q(1, p) + UR
      Else
        s = s + 1
        Cells(s, 7).Resize(1, 25) = Q
        Q = Z
           
        If Weekday(Cells(R + 1, 1)) < Weekday(Cells(s, 7)) Or Cells(R, 1) = "Summary" Then
          If Weekday(Cells(s, 7)) = 6 Then
            s = s + 1
            Cells(s, 7) = Cells(s - 1, 7) + 1
            Cells(s, 8).Resize(1, 24).Value = 0
          End If
          s = s + 1
          Cells(s, 8).Resize(1, 24).Formula = "=SUM(R[-6]C:R[-1]C)"
          s = s + 1
        End If
   
        If Not IsDate(Cells(R, 1)) Then GoTo ExitSub
        AD = Cells(R, 1)
        Q(1, 1) = AD
        GoTo QLoop
      End If
    Next R
ExitSub:
    F = "=Sum(R[" & -s + 3 & "]C:R[-2]C)/2"
    s = s + 1: Cells(s, 8).Resize(1, 24).Formula = F
         
Tailers:
    Set H = Range("N1:AE" & s): H.Cut Range("H" & s + 2)
    Set H = Range("N" & s + 2 & ":Y" & 2 * s + 1): H.Cut Range("H" & 2 * s + 3)
    Set H = Range("N" & 2 * s + 3 & ":S" & 3 * s + 2): H.Cut Range("H" & 3 * s + 4)
    Set H = Range("G4:G" & s): H.Copy Range("G" & s + 5)
    H.Copy Range("G" & 2 * s + 6): H.Copy Range("G" & 3 * s + 7)
         
    Columns("G:M").EntireColumn.AutoFit

End Sub

 

[zookeepertx]

So I cleaned up the first VBA section you posted. I got rid of all the Colons that continue FOR loops on single lines. Way too confusing. What I noticed right away is that you have 3 FOR loops all using the same R Variable within each other. That is going to cause nightmares in my opinion. Because you're changing a running variable from the parent FOR loop. I just don't see how that could work.

There is only one NEXT R statement. You need two more to finish the other two FOR loops. You really need to change those variables on those FOR loops

When you line up the IF with the END IF using the correct indentation it seems to make sense.

Another thing: having GOTO statements that start at the beginning of a FOR loop is NOT going to iterate the FOR variable, if you jump below the FOR R = 4 to bla bla, the macro will not increase R during that go around. You should be jumping to the bottom before the NEXT R so that the variable can iterate.

Actually, those first 2 FOR loops do have NEXT R's; they're just hiding at the end of that line. At least, I THINK those should work together.

I thought the same thing about the GOTO statements, since I'm not the one that created this code, but when I sat and stepped through the code this week (approximately 117 times ) it does work correctly because of the weird formatting of the sheet it works on.

The second set of code in my first post is one where I had tried to get the IFs and ENDIFs to match up and I was pretty sure I was close; it was hard to tell 100%. I resorted to hitting F8, see what happened and write it down, then hit F8 again, see what happened and write it down, ad infinitum! But I still wasn't completely sure I had it all right.
Anyway, if I run YOUR code, it works; if I run MY version of the original code it doesn't work. Stupid thing is that my version seems to be virtually identical to yours! On the 3rd FOR-NEXT loop, it gives me a "NEXT without FOR" error. I just don't get it! I made a screenshot of yours and mine side by side, parsing yours out downward so the lines match up together. I made the line where the error occurs "screaming green". (My name for the color ) Here's that comparison:

Maybe you can spot something I'm not seeing, since I'm half blind from looking at this for so dang long.

Jenny

 

[Jeffrey Mahoney]

These are orphaned IF / End IF. Either put the whole thin on one line or finish it with and END IF

I am not a fan of using colons to separate lines of code. Just to hard to read.

 

[Jeffrey Mahoney]

 

[Jeffrey Mahoney]

I also don't like using Row/Column (RC) type cell reference. I don't know what this code is supposed to be doing, but I think that GoTo QLoop is suspicious.


Here is a cleaner version

Sub ABC()
'JennyD05152015

  Dim AD As Date, c As String, p As Integer, Q, Z, UR, R As Long, s As Long
  Dim F As String, H As Range
 
  'Add headers for new sections to the right of original data
  'Also goes to Sub Outline to put border around headers
Headers:
  Set H = Range("H1:AE3")
  H.HorizontalAlignment = xlCenter
  H.VerticalAlignment = xlCenter
  Set H = Range("H1:M1")
  H.MergeCells = True
  H.Value = "Flat Units"
  With Range("H1:M1").Borders
    .LineStyle = xlContinuous
    .Weight = xlMedium
  End With
  Set H = Range("N1:S1")
  H.MergeCells = True
  H.Value = "Cosmetic Units"
  With Range("N1:S1").Borders
    .LineStyle = xlContinuous
    .Weight = xlMedium
  End With
  Set H = Range("T1:Y1")
  H.MergeCells = True
  H.Value = "Apparel Y Units"
  With Range("T1:Y1").Borders
    .LineStyle = xlContinuous
    .Weight = xlMedium
  End With
  Set H = Range("Z1:AE1")
  H.MergeCells = True
  H.Value = "Apparel N Units"
  With Range("Z1:AE1").Borders
    .LineStyle = xlContinuous
    .Weight = xlMedium
  End With
                      
'Adds A, B or C to new section row 2 and LY or TY to new section row 3
  For R = 8 To 30 Step 6
    Cells(2, R) = "A"
    Cells(2, R + 2) = "B"
    Cells(2, R + 4) = "C"
  Next R
  For R = 8 To 30 Step 2
    Cells(3, R) = "LY"
    Cells(3, R + 1) = "TY"
  Next R
             
  Q = Cells(2, 7).Resize(1, 25)
  Cells(2, 7).Resize(1, 25).Value = 0
  Z = Cells(2, 7).Resize(1, 25)
  Cells(2, 7).Resize(1, 25) = Q
  Q = Z
       
  ActiveSheet.UsedRange.Offset(3, 6).Clear
  Range("G:G").NumberFormat = "mmm d, yyyy"

  'Adds up total units for each designation and enters them in the corresponding cell
  AD = Cells(4, 1)
  Q(1, 1) = AD
  s = 3
  For R = 4 To Range("A" & Rows.Count).End(xlUp).Row
QLoop:
    If Cells(R, 1) = AD Then
      c = Cells(R, 5)
      UR = Cells(R, 6)
      
      If InStr(1, c, "FLAT") Or InStr(1, c, "MARK") Then
        p = 2
      ElseIf InStr(1, c, "COSMETICS") Then
        p = 8
      Else
        p = 14
        If Cells(R, 3) = "N" Then p = 20
      End If
      p = p + 2 * (Asc(UCase(Cells(R, 2))) - 65)
      If Year(AD) = Year(Date) Then p = p + 1
       
      Q(1, p) = Q(1, p) + UR
    Else
      s = s + 1
      Cells(s, 7).Resize(1, 25) = Q
      Q = Z
         
      If Weekday(Cells(R + 1, 1)) < Weekday(Cells(s, 7)) Or Cells(R, 1) = "Summary" Then
        If Weekday(Cells(s, 7)) = 6 Then
          s = s + 1
          Cells(s, 7) = Cells(s - 1, 7) + 1
          Cells(s, 8).Resize(1, 24).Value = 0
        End If
        s = s + 1
        Cells(s, 8).Resize(1, 24).Formula = "=SUM(R[-6]C:R[-1]C)"
        s = s + 1
      End If

      If Not IsDate(Cells(R, 1)) Then GoTo ExitSub
      AD = Cells(R, 1)
      Q(1, 1) = AD
      GoTo QLoop
    End If
  Next R
ExitSub:
  F = "=Sum(R[" & -s + 3 & "]C:R[-2]C)/2"
  s = s + 1
  Cells(s, 8).Resize(1, 24).Formula = F
         
Tailers:
  Set H = Range("N1:AE" & s)
  H.Cut Range("H" & s + 2)
  Set H = Range("N" & s + 2 & ":Y" & 2 * s + 1)
  H.Cut Range("H" & 2 * s + 3)
  Set H = Range("N" & 2 * s + 3 & ":S" & 3 * s + 2)
  H.Cut Range("H" & 3 * s + 4)
  Set H = Range("G4:G" & s)
  H.Copy Range("G" & s + 5)

H.Copy Range("G" & 2 * s + 6)
H.Copy Range("G" & 3 * s + 7)

Columns("G:M").EntireColumn.AutoFit

End Sub

 

[zookeepertx]

These are orphaned IF / End IF. Either put the whole thin on one line or finish it with and END IF
View attachment 110525

I am not a fan of using colons to separate lines of code. Just to hard to read.

Aha, see I didn't even know you could have an If without an EndIf. That's the main problem I had when I was trying to match up Ifs with EndIfs. So can you do that anytime? Just put the part after "Then" on the same line and not have to put an EndIf? That's crazy; and seems like it'd be hard to keep track of - the proof being my current situation, LOL!
This macro is the first time I've ever seen colons used in code. When I first got it I tried to figure out what was going on, but, since it worked and I was so busy, I just never pursued it. I totally agree with you that the colons make it very hard to read/decipher!

 

[zookeepertx]

I also don't like using Row/Column (RC) type cell reference. I don't know what this code is supposed to be doing, but I think that GoTo QLoop is suspicious.

Here is a cleaner version

If Not IsDate(Cells(R, 1)) Then GoTo ExitSub

Okay - Putting those couple back into a single row didn't quite work but then I realized that I hadn't corrected the row I've noted above within your quote! It worked just right then!

I was trying to create a much smaller version that I could post so you could see what this is all working on, but when I cut out some of the dates to shorten the data, it caused a problem because some of the data didn't have 5 whole days of data. So when the sum formulas were put in at the end of each week, because of the (R,C) method of the formulas, those formulas were including too many lines.
I'm sure that made no sense at all. I have trouble getting the "pictures" in my head to come out in words, LOL! I have to clock out now, but I'll try again tomorrow. I just figured you might be interested what in the heck this is all doing. Even if you aren't, I'll have to figure out how to fix the formula problem. Hopefully that won't be TOO hard.

Jenny