zookeepertx
Well-known Member
- Joined
- May 27, 2011
- Messages
- 589
- Office Version
- 365
- Platform
- Windows
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
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
VBA Code:
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
VBA Code:
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
Last edited: