[Solved] Is it possible to count date stamped variables accross multiple sheets in Excel 2010 and list it by date? [closed]

by


Edit: The code below should now arrange the totals by date.

Private Sub Total()

Dim apple As Integer
Dim banana As Integer
Dim grape As Integer
Dim pear As Integer
Dim lemon As Integer
Dim orange As Integer
Dim sheet As Worksheet
Dim i As Integer
Dim lastRow As Integer
Dim j As Integer
Dim comp1 As String
Dim comp2 As String

apple = 0
banana = 0
grape = 0
pear = 0
lemon = 0
orange = 0


For Each sheet In Worksheets
sheet.Activate
lastRow = WorksheetFunction.CountA(Range("A:A"))
j = 2
'Create the header for the Totals cells
ActiveSheet.Cells(lastRow + 1, 1).Value = "Date"
ActiveSheet.Cells(lastRow + 1, 2).Value = "Apples"
ActiveSheet.Cells(lastRow + 1, 3).Value = "Bananas"
ActiveSheet.Cells(lastRow + 1, 4).Value = "Grapes"
ActiveSheet.Cells(lastRow + 1, 5).Value = "Pears"
ActiveSheet.Cells(lastRow + 1, 6).Value = "Lemons"
ActiveSheet.Cells(lastRow + 1, 7).Value = "Oranges"

For i = 2 To lastRow
    'Compare the date in row i to row i + 1 to see if we should add the totals or start a new daily total
    comp1 = ActiveSheet.Cells(i, 5).Value
    comp2 = ActiveSheet.Cells(i + 1, 5).Value

        'Determine which variable to increment
       If ActiveSheet.Cells(i, 6).Value = "apple" Then
            apple = apple + 1
        ElseIf ActiveSheet.Cells(i, 6).Value = "banana" Then
            banana = banana + 1
        ElseIf ActiveSheet.Cells(i, 6).Value = "grape" Then
            grape = grape + 1
        ElseIf ActiveSheet.Cells(i, 6).Value = "pear" Then
            pear = pear + 1
        ElseIf ActiveSheet.Cells(i, 6).Value = "lemon" Then
           lemon = lemon + 1
        ElseIf ActiveSheet.Cells(i, 6).Value = "orange" Then
            orange = orange + 1
        Else
        End If
    If comp1 <> comp2 Then
        'If this is the last entry for this date, past the date and totals
        ActiveSheet.Cells(lastRow + j, 1).Value = comp1
        ActiveSheet.Cells(lastRow + j, 2).Value = apple
        ActiveSheet.Cells(lastRow + j, 3).Value = banana
        ActiveSheet.Cells(lastRow + j, 4).Value = grape
        ActiveSheet.Cells(lastRow + j, 5).Value = pear
        ActiveSheet.Cells(lastRow + j, 6).Value = lemon
        ActiveSheet.Cells(lastRow + j, 7).Value = orange
        'Clear variables for next day
        apple = 0
        banana = 0
        grape = 0
        pear = 0
        lemon = 0
        orange = 0
        'Move to next line for totals row
        j = j + 1

    Else
    End If
Next i

Next sheet

End Sub

This should properly total each page in the manner you are looking for, but it will not create a Grand Total for all pages. You could implement a large array in this code to store all the values between pages, or possibly create a new function using VLookup and some For loops, but it would be rather complex. If possible, I would try to make a formula on your Grand Total sheet to pull the data in.

4

solved Is it possible to count date stamped variables accross multiple sheets in Excel 2010 and list it by date? [closed]