I cannot skip Q1-2-3-4 from the monthly data and also it just takes totals from premium sheet, even though there are subcategories next to it

Question

I've made changes to the code, now it looks like this:

' Loop through each month in the GWP worksheet (starting from row 7)
For j = 7 To 18 ' Assuming data is for 12 months (rows 7 to 18)
    ' Check if the current header contains "Trim" followed by a space and a number
    If Not (InStr(1, wsCommissions.Cells(6, wsGWPHeaderRow).Value, "Trim " & j - 6 & " ") > 0) Then
        ' Get the numerator and denominator
        Dim numerator As Double
        Dim denominator As Variant ' Use Variant data type

        ' Attempt to convert the value to a Double; if it fails, set denominator to 0
        On Error Resume Next
        numerator = CDbl(wsCommissions.Cells(8, wsGWPHeaderRow).Offset(0, j - 7).Value)
        denominator = CDbl(wsGWP.Cells(j, PremiumsCol).Value)
        On Error GoTo 0

        ' Check if the denominator is a numeric value and not zero
        If IsNumeric(denominator) And denominator <> 0 Then
            ' Calculate the ratio and place it in the summary worksheet
            wsSummary.Cells(SummaryRow, 1).Value = wsGWP.Cells(j, 2).Value ' Place the month in column A
            wsSummary.Cells(SummaryRow, 2).Value = wsGWP.Cells(j, PremiumsCol).Value ' Place premiums in column B
            wsSummary.Cells(SummaryRow, 3).Value = numerator ' Place commissions in column C
            wsSummary.Cells(SummaryRow, 4).Value = Abs(numerator / denominator) ' Place the positive ratio in column D
            SummaryRow = SummaryRow + 1 ' Move to the next row in the summary sheet
        End If
    End If
Next j

This is my worksheet: https://www.dropbox.com/scl/fi/95aunxle2imk516cojjij/Random.xlsx?rlkey=8qk8ecoolgjwjsawf7hpxifpg&dl=0

I would appreciate it dearly if someone can figure it out, it's my first time using VBA so I'm a bit lost as to what the issue could be

I was expecting to see in column A the months for 2021 and then 2022, in column B the Premiums received, in column C the commissions paid and in D the commission ratio. I've tried countless different things but the end result seemed always very similar, with either skipping january in the months, but also the Trims, Italian for Quarters, also I don't understand why for premiums it always only takes the numbers of the totals, in column C even though in the box i put different names.

First worksheet GWP:

	Total	Aggregator	MassMarket	Auto	Agents direct	Bank distr	Partners	EGL	Int Part
Jan-21	10000	2500	2300	3000	5000	200	2000	200	50
Feb-21	10000	2500	2300	3000	5000	200	2000	200	50
Mar-21	10000	2500	2300	3000	5000	200	2000	200	50
Apr-21	10000	2500	2300	3000	5000	200	2000	200	50
May-21	10000	2500	2300	3000	5000	200	2000	200	50
Jun-21	10000	2500	2300	3000	5000	200	2000	200	50
Jul-21	10000	2500	2300	3000	5000	200	2000	200	50
Aug-21	10000	2500	2300	3000	5000	200	2000	200	50
Sep-21	10000	2500	2300	3000	5000	200	2000	200	50
Oct-21	10000	2500	2300	3000	5000	200	2000	200	50
Nov-21	10000	2500	2300	3000	5000	200	2000	200	50
Dec-21	10000	2500	2300	3000	5000	200	2000	200	50

Second worksheet Commissions:

	Total																Appalt																Broker																Pers Direct																Aggregator																Mass Market																Total Part																Auto																Bank Distr																Other part
	Trim 1 2021	Jan-21	Feb-21	Mar-21	Trim 2 2021	Apr-21	May-21	Jun-21	Trim 3 2021	Jul-21	Aug-21	Sep-21	Trim 4 2021	Oct-21	Nov-21	Dec-21	Trim 1 2021	Jan-21	Feb-21	Mar-21	Trim 2 2021	Apr-21	May-21	Jun-21	Trim 3 2021	Jul-21	Aug-21	Sep-21	Trim 4 2021	Oct-21	Nov-21	Dec-21	Trim 1 2021	Jan-21	Feb-21	Mar-21	Trim 2 2021	Apr-21	May-21	Jun-21	Trim 3 2021	Jul-21	Aug-21	Sep-21	Trim 4 2021	Oct-21	Nov-21	Dec-21	Trim 1 2021	Jan-21	Feb-21	Mar-21	Trim 2 2021	Apr-21	May-21	Jun-21	Trim 3 2021	Jul-21	Aug-21	Sep-21	Trim 4 2021	Oct-21	Nov-21	Dec-21	Trim 1 2021	Jan-21	Feb-21	Mar-21	Trim 2 2021	Apr-21	May-21	Jun-21	Trim 3 2021	Jul-21	Aug-21	Sep-21	Trim 4 2021	Oct-21	Nov-21	Dec-21	Trim 1 2021	Jan-21	Feb-21	Mar-21	Trim 2 2021	Apr-21	May-21	Jun-21	Trim 3 2021	Jul-21	Aug-21	Sep-21	Trim 4 2021	Oct-21	Nov-21	Dec-21	Trim 1 2021	Jan-21	Feb-21	Mar-21	Trim 2 2021	Apr-21	May-21	Jun-21	Trim 3 2021	Jul-21	Aug-21	Sep-21	Trim 4 2021	Oct-21	Nov-21	Dec-21	Trim 1 2021	Jan-21	Feb-21	Mar-21	Trim 2 2021	Apr-21	May-21	Jun-21	Trim 3 2021	Jul-21	Aug-21	Sep-21	Trim 4 2021	Oct-21	Nov-21	Dec-21	Trim 1 2021	Jan-21	Feb-21	Mar-21	Trim 2 2021	Apr-21	May-21	Jun-21	Trim 3 2021	Jul-21	Aug-21	Sep-21	Trim 4 2021	Oct-21	Nov-21	Dec-21	Trim 1 2021	Jan-21	Feb-21	Mar-21	Trim 2 2021	Apr-21	May-21	Jun-21	Trim 3 2021	Jul-21	Aug-21	Sep-21	Trim 4 2021	Oct-21	Nov-21	Dec-21
Commissions	500	200	200	100	500	200	200	100	500	200	200	100	500	200	200	100	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25	100	50	25	25

My expected result show be, if I want the Total commission ratio:

Month	Premiums	Commissions	Commission Ratio
Jan-21	10000	200	0.05
Feb-21	10000	200	0.02
Mar-21	10000	100	0.02
Apr-21	10000	200	0.01
May-21	10000	200	0.05
Jun-21	10000	100	0.02
Jul-21	10000	200	0.02
Aug-21	10000	200	0.01
Sep-21	10000	100	0.05
Oct-21	10000	200	0.02
Nov-21	10000	200	0.02
Dec-21	10000	100	0.01
Jan-22	10000	200	0.05
Feb-22	10000	200	0.02
Mar-22	10000	100	0.02
Apr-22	10000	200	0.01
May-22	10000	200	0.05
Jun-22	10000	100	0.02
Jul-22	10000	200	0.02
Aug-22	10000	200	0.01
Sep-22	10000	100	0.05
Oct-22	10000	200	0.02
Nov-22	10000	200	0.02
Dec-22	10000	100	0.01

Answer 1

It appears the code has undergone multiple revisions, leading to mismatching variable names. For example, wsSummaryHeaderRow is a column index rather than row. I aimed to get it working with minimal changes.

Sub AutomateDataGatheringWithSectorFilter()
    Dim wsSummary As Worksheet
    Dim wsCommissions As Worksheet
    Dim wsGWP As Worksheet
    Dim LastCol As Long
    Dim i As Long, j As Long
    Dim Year As Integer
    Dim Sector As String
    Dim SummaryRow As Long
    ' Define your worksheets
    Set wsSummary = ThisWorkbook.Sheets("Summary")
    wsSummary.Range("A:D").ClearContents
    wsSummary.Range("A6:D6").Value = Array("Mese", "GWP", "Commissions", "Commission Ratio")
    ' InputBox to select the sector
'    Sector = InputBox("Enter the sector (e.g., Totals, MassMarket, Automotive):", "Select Sector")
    Sector = "Total" ' "Broker"  ' "Total"
    If Sector = "" Then Exit Sub ' Exit if no sector is entered
    ' Initialize the summary row
    SummaryRow = 7
    ' Loop through each year
    For Year = 2021 To 2022 ' Process data for both 2021 and 2022
        ' Set references to the GWP and Commissions worksheets for the current year
        On Error Resume Next ' Continue to the next iteration if the worksheet is not found
        Set wsCommissions = ThisWorkbook.Sheets("Commissions " & Year)
        Set wsGWP = ThisWorkbook.Sheets("GWP " & Year)
        On Error GoTo 0 ' Reset error handling
        ' Check if the worksheets were found
        If Not wsCommissions Is Nothing And Not wsGWP Is Nothing Then
            ' Find the last column of premiums data in the Commissions worksheet (row 8)
            LastCol = wsCommissions.Cells(8, wsCommissions.Columns.Count).End(xlToLeft).Column
            ' Determine the header row in the GWP sheet based on the selected sector
            wsSummaryHeaderRow = 6 ' Default to Totals row
            For i = 2 To LastCol
                If wsCommissions.Cells(6, i).Value = Sector Then
                    wsSummaryHeaderRow = i
                    Exit For
                End If
            Next i
            Dim iGWPRow As Integer, sMth As String
            iGWPRow = 6
            ' Loop through each month in the GWP worksheet (starting from row 7)
            For j = 0 To 16 ' Assuming data is for 12 months (rows 7 to 18)
                ' Check if the header contains "Trim" and skip those columns
                sMth = wsCommissions.Cells(7, wsSummaryHeaderRow + j).Text
                'wsCommissions.Cells(7, wsSummaryHeaderRow + j).Select
                If InStr(1, sMth, "Trim") = 0 Then
                    ' Check if the header contains "Q" and skip those columns
                    If Left(sMth, 1) <> "Q" Then
                        ' Get the numerator and denominator
                        Dim numerator As Double
                        Dim denominator As Variant ' Use Variant data type
                        ' Attempt to convert the value to a Double; if it fails, set denominator to 0
                        On Error Resume Next
                        numerator = CDbl(wsCommissions.Cells(8, wsSummaryHeaderRow + j).Value) ' Offset for columns C to K
                        iGWPRow = iGWPRow + 1
                        denominator = CDbl(wsGWP.Cells(iGWPRow, 3).Value) ' Pick the date from GWP sheet (assuming it's in column B)
                        On Error GoTo 0
                        ' Check if the denominator is a numeric value and not zero
                        If IsNumeric(denominator) Then
                            ' Calculate the ratio and place it in the summary worksheet
                            wsSummary.Cells(SummaryRow, 1).Value = wsGWP.Cells(iGWPRow, 2).Value ' Place the month in column A
                            wsSummary.Cells(SummaryRow, 2).Value = denominator ' Place premiums in column B
                            wsSummary.Cells(SummaryRow, 3).Value = numerator ' Place commissions in column C
                            wsSummary.Cells(SummaryRow, 4).Value = IIf(numerator = 0, 0, Abs(numerator / denominator))  ' Place the positive ratio in column D
                            SummaryRow = SummaryRow + 1 ' Increment the summary row
                        End If
                    End If
                End If
            Next j
        Else
            ' Handle the case where the worksheets for the current year were not found
            MsgBox "Worksheets for " & Year & " not found or do not match expected names/structure."
        End If
    Next Year
End Sub

---

Refactor the code to make it is more flexible and efficient.

Variables startYr and endYr are used to change start and end year.

Option Explicit
Sub demo()
    Dim wsSummary As Worksheet
    Dim wsCommissions As Worksheet
    Dim wsGWP As Worksheet, iGWPCol
    Dim iYear As Integer, iMth As Integer
    Dim startYr As Integer, endYr As Integer
    Dim i As Long, c As Range, Sector
    Dim aRes(), aGWP, aComm, sKey As String
    Set wsSummary = ThisWorkbook.Sheets("Summary")
    wsSummary.Range("A:D").ClearContents
    wsSummary.Range("A6:D6").Value = Array("Mese", "GWP", "Commissions", "Commission Ratio")
    Sector = InputBox("Enter the sector (e.g., Total, Mass Market, Automotive):", "Select Sector")
    ' Sector = "Broker"  ' for testing
    If Sector = "" Then Exit Sub
    startYr = 2021: endYr = 2022
    For iYear = startYr To endYr
        ReDim aRes(1 To 12, 1 To 4)
        iMth = 1
        On Error Resume Next
        Set wsCommissions = ThisWorkbook.Sheets("Commissions " & iYear)
        Set wsGWP = ThisWorkbook.Sheets("GWP " & iYear)
        On Error GoTo 0
        If Not (wsCommissions Is Nothing Or wsGWP Is Nothing) Then
            Set c = wsCommissions.Rows(6).Find(Sector, LookIn:=xlValues, LookAt:=xlWhole)
            If c Is Nothing Then
                MsgBox "Can't find " & Sector & " on sheet " & wsSummary.Name
                Exit Sub
            Else
                aComm = c.MergeArea.Offset(1, 0).Resize(2, c.MergeArea.Cells.Count).Value
                aGWP = wsGWP.[B6].CurrentRegion.Value
                iGWPCol = 0
                For i = 1 To UBound(aGWP, 2)
                    If UCase(aGWP(1, i)) = UCase(Sector) Then
                        iGWPCol = i
                        Exit For
                    End If
                Next i
                If iGWPCol = 0 Then
                    MsgBox "Can't find " & Sector & " on sheet " & wsGWP.Name
                Else
                    For i = 1 To UBound(aComm, 2)
                        sKey = UCase(aComm(1, i))
                        If InStr(1, UCase(sKey), "TRIM") = 0 And Left(sKey, 1) <> "Q" And _
                            IsDate(sKey) Then
                            aRes(iMth, 1) = aGWP(iMth + 1, 1)
                            aRes(iMth, 2) = aGWP(iMth + 1, iGWPCol)
                            aRes(iMth, 3) = aComm(2, i)
                            aRes(iMth, 4) = IIf(aComm(2, i) = 0, 0, Abs(aComm(2, i) / aGWP(iMth + 1, iGWPCol)))
                            iMth = iMth + 1
                        End If
                    Next i
                End If
            End If
        Else
            MsgBox "Worksheets for " & iYear & " not found or do not match expected names/structure."
        End If
        wsSummary.Cells(wsSummary.Rows.Count, 1).End(xlUp).Offset(1, 0) _
            .Resize(UBound(aRes), UBound(aRes, 2)).Value = aRes
    Next iYear
    MsgBox "Done"
End Sub