vba - 使用 VBA 在公式 SUMIFS 中的另一个工作簿中引用变量工作表

Question

我正在尝试向单元格添加一个 SUMIFS 公式，因此我需要有一个变量来引用另一个工作簿中的另一个工作表。但是，这在我的代码中不起作用。仅供引用，我已经用 msoFileDialogFilePicker 打开了工作簿.

这是我必须添加公式的部分，ws 是工作表的对象:

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
    .Title = "..."
    .AllowMultiSelect = False

    If .Show = True Then
        If .SelectedItems(1) <> vbNullString Then
            Filename = .SelectedItems(1)
        End If
    Else
        Exit Sub
    End If
    Set wb = GetObject(Filename)
    Set ws = wb.Sheets("ASEAN (EUR)")

    With ws
        YTDws.Range("$A$1:$D$" & usedRow).AutoFilter Field:=1, Criteria1:="Singapore"
        If .Range("Q20").Value = "SINGAPORE" Then
        usedRow2 = YTDws.Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = Range("C2:C" & usedRow2)
        For Each cl In rng.SpecialCells(xlCellTypeVisible)
            cl.Select
            cl.FormulaR1C1 = "=SUMIFS('&ws.name&'!R[32]C[3]:R[243]C[3],'&ws.name&'!C[2], C[-1])"
        Next cl
        End If
    End With

End With

我相信问题出在这行代码上:

cl.FormulaR1C1 = "=SUMIFS('&ws.name&'!R[32]C[3]:R[243]C[3],'&ws.name&'!C[2], C[-1])"

我在网上搜索了很多做的例子，这个是我从 this post 得到的。 ，我发现它与我的情况相似。但是，它似乎仍然无法正常工作，程序提示我从工作簿中选择工作表并说它找不到工作表 ws.name .选择工作表后，单元格中的公式如下所示: =SUMIFS('[&ws.name&]ASEAN (EUR)'!R[32]C[3]:R[243]C[3],'[&ws.name&]ASEAN (EUR)'!C[2], C[-1]) .

请帮我定位问题，在此先感谢!

Answer 1

几件事

您在这里指的是哪张表Set rng = Range("C2:C" & usedRow2)

SumIfs() 语法为 =SUMIFS(Sum_range,Criteria_range1,Criteria1 ....) .

将 SUMIFS() 与外部工作簿一起使用时，公式如下所示

"=SUMIFS('[" & wb.Name & "]" & ws.Name & "'!" & YOUR_SUM_RANGE & ",'[" & wb.Name & "]" & ws.Name & "'!" & YOUR_CRITERIA_RANGE & "," & YOUR_CRITERIA & ")"
您只使用工作表名称而不是工作簿名称。

我的建议 :手动打开工作簿并输入公式(再次手动)。检查公式是否有效，然后在代码中使用该公式