Excel VBA code sequence control

[mellymelle]

In a nutshell, my code loops through a master database and compiles a report for each unique autofilter criteria of a field. There are fees and other calculations for each criteria, and as there are hundreds of criteria I'm using the same control variable for each unique criteria's calcs. As such I have to reset these variables to zero at the beginning of each loop but it seems to reset to zero before the output steps. Weird thing the fees calc output is working but not the others (these print on report as zero), even though coding sequences are same for all. Would anyone be so kind as to take a look and see if you can spot what's going on? I do have a debug.print of one of the calcs that's not working and they print as non-zeros in the immediate window. Thanks in advance. If you notice any other wrongs/bad habits etc., any and all advice is welcome to a rookie like me.

    For currentCell = 2 To NumFilters
        Sheet9.UsedRange.Clear
        sumFees = 0
        sumHST = 0
        sumGST = 0
        sumQST = 0
        sumPST = 0
        reportLine = 9
        currentAcct = Sheet8.Range("A" & currentCell).Value
        currentClient = Sheet8.Range("B" & currentCell).Value
        Sheet2.UsedRange.AutoFilter Field:=2, Criteria1:=currentAcct
        Sheet2.UsedRange.SpecialCells(xlCellTypeVisible).Copy Sheet9.Range("A1")
        numTrans = Sheet9.Cells(Rows.Count, "A").End(xlUp).Row
        Sheet9.Sort.SortFields.Clear
        Sheet9.Sort.SortFields.Add Key:=Range("D2:D" & numTrans), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With Sheet9.Sort
            .SetRange Range("A1:H" & numTrans)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        Sheet1.Range("B5").Formula = currentAcct
        Sheet1.Range("B6").Formula = currentClient
        
        For rowNumber = 1 To numTrans
            If reportLine = 35 Then
                Sheet1.Range("A42").Clear
                Sheet1.Range("A5:H8").Copy Sheet1.Range("A48")
                Sheet1.Range("A36:B46").Copy Sheet1.Range("A83")
                Sheet1.Range("I37").Value = sumFees
                Sheet1.Range("I38").Value = sumHST
                Sheet1.Range("I39").Value = sumGST
                Sheet1.Range("I40").Value = sumQST
                Sheet1.Range("I41").Value = sumPST
                reportLine = 53
            End If
            Trans = Left(Sheet9.Range("F" & rowNumber).Value, 3)
            Select Case Trans
                Case "MGF"
                    Sheet9.Range("D" & rowNumber).Copy Sheet1.Range("A" & reportLine)
                    Sheet9.Range("G" & rowNumber).Copy Sheet1.Range("C" & reportLine)
                    Sheet9.Range("E" & rowNumber).Copy Sheet1.Range("I" & reportLine)
                    sumFees = sumFees + Sheet9.Range("E" & rowNumber).Value
                    reportLine = reportLine + 1
                Case "HSM"
                    sumHSM = sumHSM + Sheet9.Range("E" & rowNumber).Value
                    Debug.Print sumHSM
                Case "GSM"
                    sumGSM = sumGSM + Sheet9.Range("E" & rowNumber).Value
                Case "QSM"
                    sumQSM = sumQSM + Sheet9.Range("E" & rowNumber).Value
                Case "PSM"
                    sumPSM = sumPSM + Sheet9.Range("E" & rowNumber).Value
            End Select
        Next rowNumber


        If Not reportLine > 35 Then
            Sheet1.Range("I37").Value = sumFees
            Sheet1.Range("I38").Value = sumHST
            Sheet1.Range("I39").Value = sumGST
            Sheet1.Range("I40").Value = sumQST
            Sheet1.Range("I41").Value = sumPST
            Sheet1.Range("I42").Formula = "=SUM(R[-5]C:R[-1]C)"
            Sheet1.Range("I42").Font.Bold = True
        Else
            Sheet1.Range("I84").Value = sumFees
            Sheet1.Range("I85").Value = sumHST
            Sheet1.Range("I86").Value = sumGST
            Sheet1.Range("I87").Value = sumQST
            Sheet1.Range("I88").Value = sumPST
            Sheet1.Range("I89").Formula = "=SUM(R[-5]C:R[-1]C)"
            Sheet1.Range("I89").Font.Bold = True
        End If


        Sheet1.Columns(9).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* " - "??_);_(@_)"
        Sheet1.UsedRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            CreateObject("WScript.shell").specialfolders("Desktop") & "\HRCreports\" & currentClient & " " & currentAcct & ".pdf", Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Sheet1.Range("A9:I35").ClearContents
        Sheet1.Range("I37:I42").ClearContents
        Sheet1.Range("A47:I93").ClearContents
    
    Next currentCell

 

[JLGWhiz]

        sumFees = 0
        sumHST = 0
        sumGST = 0
        sumQST = 0
        sumPST = 0

With the exception of SumFees, I don't see where you give the variables a value greater than zero within the loop that is posted before you assign their values to a range. So they would be entered as zero. But maybe I missed something in the code Are you sure your Select Case values are correct?

 

[mellymelle]

Hi JLGWhiz, in the Select Case statements in the loop within the loop.

 

[JLGWhiz]

I only saw one, it was in a loop. It looks like that is where you meant to initialize your variables, but the values are being assigned to the case criteria, which seems odd. Thought you might want to take a look and make sure you are giving the right variables values in that statement.

 

[mellymelle]

It may help to give a bit more background on this. The outer loop is looping through each unique autofilter criteria. On each of these criteria, the inner loop goes through the filtered data row by row, which are monetary transactions, and calculates each of the variables (sumFees, sumHST, sumGST, etc.) by using the data in column F to determine to which variable the value in column E should be added. Once the last row is reached (the next steps are part of the outer loop), the value of each of these variables is posted to Sheet1 (the report), printed to pdf, and then the process starts all over again on the next autofilter criteria of the outer loop.

The sumFees calcs and posting is working but not for the others, even though the same logic is used in the compilation of all of them...at least as far as I can tell.

            Select Case Trans
                Case "MGF"
                    Sheet9.Range("D" & rowNumber).Copy Sheet1.Range("A" & reportLine)
                    Sheet9.Range("G" & rowNumber).Copy Sheet1.Range("C" & reportLine)
                    Sheet9.Range("E" & rowNumber).Copy Sheet1.Range("I" & reportLine)
                    sumFees = sumFees + Sheet9.Range("E" & rowNumber).Value
                    reportLine = reportLine + 1
                Case "HSM"
                    sumHSM = sumHSM + Sheet9.Range("E" & rowNumber).Value
                    Debug.Print sumHSM
                Case "GSM"
                    sumGSM = sumGSM + Sheet9.Range("E" & rowNumber).Value
                Case "QSM"
                    sumQSM = sumQSM + Sheet9.Range("E" & rowNumber).Value
                Case "PSM"
                    sumPSM = sumPSM + Sheet9.Range("E" & rowNumber).Value
            End Select

 

[JLGWhiz]

Take a close look at the red font"

Select Case Trans
                Case "MGF"
                    Sheet9.Range("D" & rowNumber).Copy Sheet1.Range("A" & reportLine)
                    Sheet9.Range("G" & rowNumber).Copy Sheet1.Range("C" & reportLine)
                    Sheet9.Range("E" & rowNumber).Copy Sheet1.Range("I" & reportLine)
                    sumFees = sumFees + Sheet9.Range("E" & rowNumber).Value
                    reportLine = reportLine + 1
                Case "HSM"
                    [COLOR=#ff0000]sumHSM[/COLOR] = sumHSM + Sheet9.Range("E" & rowNumber).Value
                    Debug.Print sumHSM
                Case "GSM"
                   [COLOR=#ff0000] sumGSM = [/COLOR]sumGSM + Sheet9.Range("E" & rowNumber).Value
                Case "QSM"
                   [COLOR=#ff0000] sumQSM =[/COLOR] sumQSM + Sheet9.Range("E" & rowNumber).Value
                Case "PSM"
                   [COLOR=#ff0000] sumPSM [/COLOR]= sumPSM + Sheet9.Range("E" & rowNumber).Value
            End Select

Should those not be HST, QST and PST? There is nowhere else that I see that would give those variables a value.

 

[mellymelle]

$*!%&$* wow I'm a grade A moron. Thank you thank you JLGWhiz, and sorry for wasting your time. Will go hang my head in shame now.

 

[JLGWhiz]

$*!%&$* wow I'm a grade A moron. Thank you thank you JLGWhiz, and sorry for wasting your time. Will go hang my head in shame now.

Happens to the best of us,
regards, JLG