I want to make one loop

[David]

I use the following to get totals of those who ate breakfast and those
who ate lunch on a given day and post those totals in another workbook
(and it works as written):

Workbooks.Open "foodcost"
ThisWorkbook.Activate
BreakfastTotal = 0 'is this necessary?
LunchTotal = 0 'or this?
For i = 1 To 2
Sheets(i).Activate
t1 = Sheets(i).Rows(2).Find(Date).Offset(Range("Attendance").Rows.Count +
1, 0).Value
If t1 = "" Then t1 = 0 'or this if no non-member ate breakfast?
BreakfastTotal = BreakfastTotal + t1
Next
For i = 3 To 4
Sheets(i).Activate
t2 = Sheets(i).Rows(2).Find(Date).Offset(Range("Attendance").Rows.Count +
1, 0).Value
If t2 = "" Then t2 = 0 'or this if no non-member ate lunch?
LunchTotal = LunchTotal + t2
Next
Sheets(5).Activate
Workbooks("foodcost.xls").Sheets(6).Columns(1).Find(Format(Date,
"d")).Offset(0, 1).Value = Total 'from earlier in the code
Workbooks("foodcost.xls").Sheets(6).Columns(1).Find(Format(Date,
"d")).Offset(0, 2).Value = BreakfastTotal
Workbooks("foodcost.xls").Sheets(6).Columns(1).Find(Format(Date,
"d")).Offset(0, 3).Value = LunchTotal

I can't help but think there's a way to combine the 2 For/Next loops into
a single loop.

What it does:
Sheets(1) contains members who ate breakfast
Sheets(2) contains non-members who ate breakfast
Sheets(3) contains members who ate lunch
Sheets(4) contains non-members who ate lunch
Sheets(5) contains total attendance
Range("Attendance") is unique for each sheet and works OK.

Loop 1 adds up total breakfasts served
Loop 2 adds up total lunches served

All '.Finds' locate current date in both workbooks
"foodcost" has numbers in ColA representing dates
Total attendance goes 1 cell to right of "d" into ColB
Total breakfasts goes 2 cells to right of "d" into ColC
Total lunches goes 3 cells to right of "d" into ColD

 

[Mike Fogleman]

It is OK to set your variables to "0".
Try 1 For..Next loop with an If statement

Workbooks.Open "foodcost"
ThisWorkbook.Activate
BreakfastTotal = 0 'is this necessary?
LunchTotal = 0 'or this?
For i = 1 To 4
If i < 3 Then
Sheets(i).Activate
t1 = Sheets(i).Rows(2).Find(Date).Offset(Range("Attendance").Rows.Count
+ _
1, 0).Value
If t1 = "" Then t1 = 0 'or this if no non-member ate breakfast?
BreakfastTotal = BreakfastTotal + t1
Else
Sheets(i).Activate
t2 = Sheets(i).Rows(2).Find(Date).Offset(Range("Attendance").Rows.Count
+ _
1, 0).Value
If t2 = "" Then t2 = 0 'or this if no non-member ate lunch?
LunchTotal = LunchTotal + t2
End If
Next

Mike F

 

[David]

Mike Fogleman wrote

It is OK to set your variables to "0".

More curious if it was necessary or not, specifically if they were not,
subsequent runs would result in cumulative total. Now I seem to recall
variables are reset to 0 each run.

Try 1 For..Next loop with an If statement

That's the key I was seeking, a strategically placed If statement

Workbooks.Open "foodcost"
ThisWorkbook.Activate
BreakfastTotal = 0 'is this necessary?
LunchTotal = 0 'or this?
For i = 1 To 4

For some reason, I pictured it placed *after* sheets 1&2 were processed,
i.e. If i = 3 Then...

If i < 3 Then
Sheets(i).Activate
t1 =
Sheets(i).Rows(2).Find(Date).Offset(Range("Attendance").Rows.Count
+ _
1, 0).Value

Testing revealed following line is indeed necessary

If t1 = "" Then t1 = 0 'or this if no non-member ate breakfast?
BreakfastTotal = BreakfastTotal + t1
Else
Sheets(i).Activate
t2 =
Sheets(i).Rows(2).Find(Date).Offset(Range("Attendance").Rows.Count
+ _
1, 0).Value Ditto this
If t2 = "" Then t2 = 0 'or this if no non-member ate lunch?
LunchTotal = LunchTotal + t2
End If
Next

Many thanks.