如何仅为一张纸设置vba代码? [英] How to set up vba code for only one sheet?

问题描述

我目前正试图建立一个将运行sumifs宏的按钮.我正在尝试在Sheet1中构建按钮，并使sumifs在Sheet2上执行.我目前拥有的是:

I am currently stuck trying to build a button that will run a sumifs macro. I am trying to build the button in Sheet1 and have the sumifs execute on Sheet2. What I currently have is:

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上运行它，则什么也没有发生.

I have tried running it with alt + F8 and it works great as long as I am in Sheet2, if I try running it on Sheet1 nothing happens.

还可以将sumifs条件链接到单独的工作表吗?具体来说，我试图将Range ("B" & 1)链接到Sheet3上的单元格J5:J10.目前，每次尝试

Also, is there a way to link the sumifs criteria to a separate worksheet? Specifically, I am trying to have Range ("B" & 1) be linked to a cells J5:J10 on Sheet3. Currently, I get a type error whenever I try

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

非常感谢您可以提供的任何建议. 谢谢

Really appreciate any advice you can provide. Thanks

推荐答案

将代码放入模块:

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.

The idea is that it is really a good practice to mention the "Parent-Worksheet" of the range. Otherwise it is taking either the ActiveSheet (if in a module) or the worksheet in which the code resides.

无论何时使用这种构造:

Whenever you are using construction like this:

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

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

You need to tell VBA to refer somehow to the Sht1. This is done, through dots here:

否则，它将获取父级"工作表，该工作表可以是ActiveSheet或代码所在的工作表(如果不在模块中).

Otherwise it takes the Parent worksheet, which is either ActiveSheet or the one where the code is (if not in a module).

这篇关于如何仅为一张纸设置vba代码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！