Run an Independent Iteration of For Loop

[jessemejia79]

I can't figure this out for the life of me. If I was to run For j = 3 to 3 everything runs perfectly. When I run the loop For j = 0 to finalrowcust -1 it adds all kinds of stuff. I've tried to run a For k = 0 to finalrowcust -1 then For j = k to k, but that didn't work. I've also tried running case statements (It works for case 3 or case 2, but not a loop).

I've tried to run various loops and tried a variety of logical statements, but they don't seem to be working. But, again if I run it one time with an integer it works perfectly. I know I'm missing something here.

Cells(i,4) are customer names (alphabetized, from Sheet - Current Year Back 16 Weeks)
Cells(i,3) are dates (from Sheet - Current Year Back 16 Weeks)
Offset is dollar amounts (from Sheet - Current Year Back 16 Weeks)
Customer is on another sheet listed and alphebetized (this code only uses it for the finalrowcust)

What it's trying to do is check the date range then sum the amounts within that date range and place them on another sheet (Report) in a specific cell summing the values by date ranges.


Any help on this is greatly appreciated.


Sub addIt()


On Error Resume Next

Sheets("Customer").Select

Dim c As Range
Dim cell As Range
Dim cA()

finalrow = Worksheets("Current Year Back 16 Weeks").Cells(Rows.Count, 1).End(xlUp).Row
finalrowcust = Worksheets("Customer").Cells(Rows.Count, 1).End(xlUp).Row

ReDim cA(finalrowcust)

For h = 0 To finalrowcust
cA(h) = Range("A" & h + 1)
Next h

Sheets("Report").Select

k2 = 0
l2 = 0
m2 = 0
n2 = 0
o2 = 0
p2 = 0
q2 = 0
r2 = 0
s2 = 0
t2 = 0
u2 = 0
v2 = 0
w2 = 0
x2 = 0
y2 = 0
a2 = 0
b2 = 0

For i = 1 To finalrow


For Each c In Worksheets("Current Year Back 16 Weeks").Cells(i, 4)

For Each cell In Worksheets("Current Year Back 16 Weeks").Cells(i, 3)

For j = 0 To finalrowcust - 1


If c.Value = cA(j) And cell.Value >= Range("Q1") - 7 And cell.Value <= Range("Q1") Then

k2 = k2 + c.Offset(0, 2).Value
Cells(6 + (j * 21), 19).Value = k2

End If



'********

If c.Value = cA(j) And cell.Value >= Range("Q1") And cell.Value <= Range("Q1") + 7 Then

l2 = l2 + c.Offset(0, 2).Value
Cells(7 + (j * 21), 19).Value = l2

End If


'********

If c.Value = cA(j) And cell.Value >= Range("Q1") + 7 And cell.Value <= Range("Q1") + 14 Then

m2 = m2 + c.Offset(0, 2).Value
Cells(8 + (j * 21), 19).Value = m2

End If


'********

If c.Value = cA(j) And cell.Value >= Range("Q1") + 14 And cell.Value <= Range("Q1") + 21 Then

n2 = n2 + c.Offset(0, 2).Value
Cells(9 + (j * 21), 19).Value = n2

End If


'********

If c.Value = cA(j) And cell.Value >= Range("Q1") + 21 And cell.Value <= Range("Q1") + 28 Then

o2 = o2 + c.Offset(0, 2).Value
Cells(10 + (j * 21), 19).Value = o2

End If


'********

If c.Value = cA(j) And cell.Value >= Range("Q1") + 28 And cell.Value <= Range("Q1") + 35 Then

p2 = p2 + c.Offset(0, 2).Value
Cells(11 + (j * 21), 19).Value = p2

End If


'********

If c.Value = cA(j) And cell.Value >= Range("Q1") + 35 And cell.Value <= Range("Q1") + 42 Then

q2 = q2 + c.Offset(0, 2).Value
Cells(12 + (j * 21), 19).Value = q2

End If


'********

If c.Value = cA(j) And cell.Value >= Range("Q1") + 42 And cell.Value <= Range("Q1") + 49 Then

r2 = r2 + c.Offset(0, 2).Value
Cells(13 + (j * 21), 19).Value = r2

End If


'********

If c.Value = cA(j) And cell.Value >= Range("Q1") + 49 And cell.Value <= Range("Q1") + 56 Then

s2 = s2 + c.Offset(0, 2).Value
Cells(14 + (j * 21), 19).Value = s2

End If


'********

If c.Value = cA(j) And cell.Value >= Range("Q1") + 56 And cell.Value <= Range("Q1") + 63 Then

t2 = t2 + c.Offset(0, 2).Value
Cells(15 + (j * 21), 19).Value = t2

End If


'********

If c.Value = cA(j) And cell.Value >= Range("Q1") + 63 And cell.Value <= Range("Q1") + 70 Then

u2 = u2 + c.Offset(0, 2).Value
Cells(16 + (j * 21), 19).Value = u2

End If


'********

If c.Value = cA(j) And cell.Value >= Range("Q1") + 70 And cell.Value <= Range("Q1") + 77 Then

v2 = v2 + c.Offset(0, 2).Value
Cells(17 + (j * 21), 19).Value = v2

End If


'********

If c.Value = cA(j) And cell.Value >= Range("Q1") + 77 And cell.Value <= Range("Q1") + 84 Then

w2 = w2 + c.Offset(0, 2).Value
Cells(18 + (j * 21), 19).Value = w2

End If


'********

If c.Value = cA(j) And cell.Value >= Range("Q1") + 84 And cell.Value <= Range("Q1") + 91 Then

x2 = x2 + c.Offset(0, 2).Value
Cells(19 + (j * 21), 19).Value = x2

End If


'********

If c.Value = cA(j) And cell.Value >= Range("Q1") + 91 And cell.Value <= Range("Q1") + 98 Then

y2 = y2 + c.Offset(0, 2).Value
Cells(20 + (j * 21), 19).Value = y2

End If


'********

If c.Value = cA(j) And cell.Value >= Range("Q1") + 98 And cell.Value <= Range("Q1") + 105 Then

a2 = a2 + c.Offset(0, 2).Value
Cells(21 + (j * 21), 19).Value = a2

End If


'********

If c.Value = cA(j) And cell.Value >= Range("Q1") + 105 And cell.Value <= Range("Q1") + 112 Then

b2 = b2 + c.Offset(0, 2).Value
Cells(22 + (j * 21), 19).Value = b2

End If

Next j
Next cell
Next c
Next i


End Sub



Sample Data (Current Year Back 16 weeks); the data range I'm working with is 767 lines of various different companies and values:
A B C D E F
[TABLE="width: 824"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]Scheduled [/TD]
[TD]red48[/TD]
[TD="align: right"]05/13/2017 [/TD]
[TD] Sonic[/TD]
[TD]Project Manager [/TD]
[TD="align: right"]3500[/TD]
[/TR]
[TR]
[TD]Scheduled [/TD]
[TD]red48[/TD]
[TD="align: right"]08/23/2017[/TD]
[TD] Sonic[/TD]
[TD]Project Manager [/TD]
[TD="align: right"]75000[/TD]
[/TR]
[TR]
[TD]Scheduled [/TD]
[TD]blue71[/TD]
[TD="align: right"]08/22/2017[/TD]
[TD] Chase Inc.[/TD]
[TD]Project Manager [/TD]
[TD="align: right"]3500[/TD]
[/TR]
[TR]
[TD]Scheduled[/TD]
[TD]blue71[/TD]
[TD="align: right"]08/27/2017[/TD]
[TD] Chase Inc.[/TD]
[TD]Project Manager [/TD]
[TD="align: right"]18000[/TD]
[/TR]
</tbody>[/TABLE]


Sample Data (Report, the summed values for Chase would go to right of the dates):

[TABLE="width: 288"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]02/10/2017[/TD]
[TD]to[/TD]
[TD]06/02/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chase Inc.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]05/15/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]05/22/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]05/29/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]06/05/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]06/12/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]06/19/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]06/26/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07/03/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07/10/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07/17/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07/24/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07/31/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]08/07/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]08/14/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]08/21/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]08/28/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]09/04/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

That's going to give you some strange results because the variables you're using to accumulate the totals aren't reset each time. I've tried to figure out what your code is trying to do and here's my stab at it:

Sub addIt()

Dim customerSheet As Worksheet
Dim currentSheet As Worksheet
Dim reportSheet As Worksheet
Dim lastCustomer As Long
Dim lastRow As Long
Dim thisCustomer As Long
Dim thisRow As Long
Dim thisWeek As Long
Dim totals() As Long

Set customerSheet = Worksheets("Customer")
Set currentSheet = Worksheets("Current Year Back 16 Weeks")
Set reportSheet = Worksheets("Report")

lastCustomer = customerSheet.Cells(customerSheet.Rows.Count, 1).End(xlUp).Row
lastRow = currentSheet.Cells(currentSheet.Rows.Count, 1).End(xlUp).Row

ReDim totals(lasCustomer, 17)

For thisRow = 1 To lastRow
    For thisCustomer = 1 To lastCustomer
        If currentSheet.Cells(thisRow, 4).Value = customerSheet.Cells(thisCustomer, 1).Value Then
            For thisWeek = 1 To 17
                If currentSheet.Cells(thisRow, 3).Value >= reportSheet.Range("Q1").Value + (thisWeek * 7 - 14) _
                And currentSheet.Cells(thisRow, 3).Value <= reportSheet.Range("Q1").Value + (thisWeek * 7 - 7) Then
                    totals(thisCustomer, thisWeek) = totals(thisCustomer, thisWeek) + currentSheet.Cells(thisRow, 6).Value
                End If
            Next thisWeek
        End If
    Next thisCustomer
Next thisRow

For thisCustomer = 1 To lastCustomer
    For thisWeek = 1 To 17
        reportSheet.Cells(5 + thisWeek + (thisCustomer - 1) * 21, 19).Value = totals(thisCustomer, thisWeek)
    Next k
Next j

reportSheet.Select

End Sub

This takes your original idea and tidies it up a bit. The one thing I will say is that you'll get totals being added to more than 1 week - try removing one of the "=" in red above to prevent this happening.

WBD

 

[micsza]

Hi there,

This is ugly specified problem ... First of all, what is `finalrow` and `finalrowcust`? Secondly, if your goal is what I'm thinking, then you don't need to nest a loop three times(i.e. do 4x for loop).

So if your `finalrowcust` refers to a separate list of the customers you want your report for, then the loop seems to be wrongly constructed: first iterate over this list (so "for each customer"), then iterate over the ranges you want the reports ("for each date range") - this way you have a tuple [customer, date range] <customer, date="" range=""> <customer, date="" range=""> (the order might be reversed if you wish but this seems more logical) and you can get proper sum for it simply iterating over Worksheets("Current Year Back 16 Weeks") rows with simple check if customer and date fit to the tuple. So these are 3 for loops - not 4 as in your code.

Please bear in mind I'm only guessing what your problem is so the tip is not guaranteed to do what you really want.
</customer,></customer,>