excel - Sumif 返回相同的值

Question

我得到了下面的表格，需要根据当月填充数据(工作表“PR”):

原始数据示例如下所示(工作表“CSV Data PR”):

我有两个问题:

• SumIF 仅适用于第一个区域，所有其他区域均采用相同的数据。例如，正确的数据显示如下 2 月。
• 由于某种原因，它把公式一路拉下来......，而它应该停在西欧。我不确定为什么会这样。

基于以下代码:

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

你可以试试这个:

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

您只需要找到表格中月份所在的列，然后对您想要处理的行进行硬编码，因为就我而言，它们始终相同并且不太可能增长。

PS:我假设表格从第 3 行和 A 列开始，否则更改 For Each C 范围上的起始行 3 以及采用第 1 列的 sumif 内的条件。