Sumif Returning Same Value

Question

I got below table that I need to fill with data based on current month (Worksheet "PR"):

An example of the raw data looks like (Worksheet "CSV Data PR"):

I have two issues:

• SumIF only works for the first region, all the others take the same data. As example, correct data shows below Feb.
• For some reason it pulls the formula down all the way..., whilst it should stop at Western Europe. I am not sure why that is the case.

Based on the following piece of code:

Sub TableDataTest()

    Dim rngHdrFound, rngHdrFound2, findrng, USDRng, RegionRNG, rngHeaders, RngHeadersOutPut As Range
    Dim x, y As Worksheet
    Dim ThisMonth As Date
    Dim index As Variant

    Application.ScreenUpdating = False

    'Set Worksheets
    Set x = ThisWorkbook.Sheets("CSV Data PR")
    Set y = ThisWorkbook.Sheets("PR")
    index = y.Range("D8")
    ThisMonth = Format(Date, "MM/YYYY")

    'Set HeaderRow
    Const ROW_HEADERS As Integer = 1

    Set rngHeaders = Intersect(Worksheets("CSV Data PR").UsedRange, Worksheets("CSV Data PR").Rows(ROW_HEADERS))
    Set RngHeadersOutPut = y.Range("6:6")
    Set rngHdrFound = rngHeaders.Find("In USD")
    Set rngHdrFound2 = rngHeaders.Find("Region")
    Set findrng = RngHeadersOutPut.Find(What:=ThisMonth, LookIn:=xlFormulas, lookat:=xlWhole)
    Set USDRng = Range(rngHdrFound.Offset(1), rngHdrFound.End(xlDown))
    Set RegionRNG = Range(rngHdrFound2.Offset(1), rngHdrFound2.End(xlDown))

    'Find CurrentMonth + Range
    With y
        If findrng Is Nothing Then
            MsgBox "Error, unable to match " & ThisMonth & " in the specified range", vbCritical
        Exit Sub
        Else
            findrng.Offset(2, 0).Resize(Selection.Rows.Count + 8).Value = Application.WorksheetFunction.SumIf(RegionRNG, "=" & index, USDRng)
        End If
    End With

    Application.ScreenUpdating = True

End Sub

Answer 1

You could try this:

Option Explicit
Sub TableDataTest()

    Dim ws As Worksheet, wsData As Worksheet, MonthCol As Integer, ThisMonth As Date, C As Range, _
    x As Integer, y As Integer

    x = 2 'Number of the column with the region
    y = 3 'Number of the column with the data to sum

    With ThisWorkbook
        Set ws = .Sheets("PR")
        Set wsData = .Sheets("CSV Data PR")
    End With
    ThisMonth = Format(wsData.Range("C2"), "MM/YYYY")
    With ws
        MonthCol = .Cells.Find(ThisMonth, LookIn:=xlFormulas, lookat:=xlWhole).Column
        For Each C In .Range(.Cells(3, Col), .Cells(11, Col))
            C = Application.SumIf(wsData.Columns(x), .Cells(C.Row, 1), wsData.Columns(y))
        Next C
    End With

End Sub

You only need to find the column where the month is on the table, and then hardcode the rows you wanna work in because as for I can see, they are always the same and unlikely to grow.

PS: I'm assuming the table starts on row 3 and column A, otherwise change the starting row 3 on the For Each C range and the criteria inside the sumif taking column 1.