vba - 如何为一张纸设置vba代码？

Question

我目前正试图构建一个运行 sumifs 宏的按钮。我正在尝试在 Sheet1 中构建按钮并让 sumif 在 Sheet2 上执行。我目前拥有的是:

Option Explicit

Sub Sumifs()
    Dim Sht2 As Worksheet
    Dim EndRow As Long
    Dim i As Integer
    Dim SumRange As Range
    Dim CrtA As Range
    Dim CrtB As Range

    With Sht1
        EndRow = Cells(Rows.Count, "A").End(xlUp).Row
    End With

    Set Sht2 = Worksheets("Sheet2")
    Set SumRange = Worksheets("Sheet3").Range("L5:L10")
    Set CrtA = Worksheets("Sheet3").Range("C5:C10")
    Set CrtB = Worksheets("Sheet3").Range("K5:K10")

    For i = 5 To EndRow
        sht2.Cells(i, 4) = WorksheetFunction.SumIfs(SumRange, crtA, Range("G" & i), crtB, Range("B" & i))
    Next i
End Sub

我尝试使用 alt + F8 运行它只要我在 Sheet2 中，它就很好用，如果我尝试在 Sheet1 上运行它什么都没发生。

此外，有没有办法将 sumifs 标准链接到单独的工作表？具体来说，我试图拥有 Range ("B" & 1)链接到单元格 J5:J10上Sheet3 。目前，每当我尝试时都会出现类型错误

worksheets("sheet3").range ("B" & 1)

非常感谢您提供的任何建议。谢谢

Answer 1

将代码放入模块中:

Option Explicit

Sub SumIfS()
    Dim Sht1 As Worksheet
    Dim Sht2 As Worksheet
    Dim EndRow As Long
    Dim i As Integer
    Dim SumRange As Range
    Dim CrtA As Range
    Dim CrtB As Range

    Set Sht2 = Worksheets(2)
    Set Sht1 = Worksheets(1)

    With Sht1
        EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    Set SumRange = Worksheets("Sheet3").Range("L5:L10")
    Set CrtA = Worksheets("Sheet3").Range("C5:C10")
    Set CrtB = Worksheets("Sheet3").Range("K5:K10")

    For i = 5 To EndRow
        With Sht2
            .Cells(i, 4) = WorksheetFunction.SumIfS(SumRange, CrtA, .Range("G" & i), CrtB, .Range("B" & i))
        End With
    Next i

End Sub

这个想法是，提及该范围的“父工作表”确实是一个很好的做法。否则，它将采用 ActiveSheet(如果在模块中)或代码所在的 worksheet。

每当您使用这样的构造时:

With Sht1
    EndRow = Cells(Rows.Count, "A").End(xlUp).Row
End With

您需要告诉VBA以某种方式引用Sht1。这是通过此处的点完成的:

否则，它将采用父工作表，该工作表是 ActiveSheet 或代码所在的工作表(如果不在模块中)。