vba - 在 VBA 中尝试在多个工作表中创建具有多个条件的动态 Sumifs 公式
问题描述
更新了最新的代码。唯一悬而未决的问题是工作簿没有改变,但其他一切都按我的意愿工作:)
在 VBA 中,我试图在不同的工作簿中创建一个具有多个条件的 sumifs 公式,但我在语法上遇到了很大的困难。任何帮助都会很棒:)
WorkbookRecut.Worksheets("Summary").Activate
Dim CountRows As Long
Dim CountRows2 As Long
CountRows = WorkbookRecut.Worksheets("Summary").Range("I" & WorkbookRecut.Worksheets("Summary").Rows.Count - 1).End(xlUp).Row
CountRows2 = CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Range("I" & CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Rows.Count - 1).End(xlUp).Row
CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Activate
Range("O6").Formula = _
"=Sumifs(" & [WorkbookRecut].Sheets("Summary").Range("I9").Address & ":" & [WorkbookRecut].Sheets("Summary").Range("I" & CountRows).Address _
& "," & [WorkbookRecut].Sheets("Summary").Range("A9").Address & ":" & [WorkbookRecut].Sheets("Summary").Range("A" & CountRows).Address _
& "," & [CashBreaksMetricsWorkbookFinal].Worksheets("CSCIG_Cash Breaks Metrics").Range("K6").Address(Rowabsolute:=False) _
& "," & [WorkbookRecut].Sheets("Summary").Range("D9").Address & ":" & [WorkbookRecut].Sheets("Summary").Range("D" & CountRows).Address _
& "," & [CashBreaksMetricsWorkbookFinal].Worksheets("CSCIG_Cash Breaks Metrics").Range("N6").Address(Rowabsolute:=False) & ")"
CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Range("O6:O" & CountRows2).FillDown
解决方案
我们缺少 .Address(External:=True)
感谢大家帮助我到达那里(终于!)
下面的最终代码
Public CashBreaksMetricsWorkbookFinal As Workbook
Public WorkbookRecut As Workbook
Dim CountRows As Long
Dim CountRows2 As Long
CountRows = WorkbookRecut.Worksheets("Summary").Range("I" & WorkbookRecut.Worksheets("Summary").Rows.Count - 1).End(xlUp).Row
CountRows2 = CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Range("I" & CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Rows.Count - 1).End(xlUp).Row
CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Activate
Range("O6").Formula = _
"=Sumifs(" & [WorkbookRecut].Sheets("Summary").Range("I9").Address(External:=True) & ":" & [WorkbookRecut].Sheets("Summary").Range("I" & CountRows).Address(External:=True) _
& "," & [WorkbookRecut].Sheets("Summary").Range("A9").Address(External:=True) & ":" & [WorkbookRecut].Sheets("Summary").Range("A" & CountRows).Address(External:=True) _
& "," & [CashBreaksMetricsWorkbookFinal].Worksheets("CSCIG_Cash Breaks Metrics").Range("K6").Address(Rowabsolute:=False) _
& "," & [WorkbookRecut].Sheets("Summary").Range("D9").Address(External:=True) & ":" & [WorkbookRecut].Sheets("Summary").Range("D" & CountRows).Address(External:=True) _
& "," & [CashBreaksMetricsWorkbookFinal].Worksheets("CSCIG_Cash Breaks Metrics").Range("N6").Address(Rowabsolute:=False) & ")"
CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Range("O6:O" & CountRows2).FillDown
推荐阅读
- dart - 在 Dart 中混合初始化器和构造器主体
- javascript - 点击时图片不显示在灯箱模块中
- javascript - JS puppeteer 使用 for 循环遍历链接
- vue.js - 我有一个 vue 组件不呈现数据的问题
- javascript - 在 forEach 循环 javascript 中创建对象时出现问题
- node.js - Zookeeper getChildren 事件只触发一次
- image - 为什么 `cv2.threshold` 只需要灰度图像?
- netbeans - 带有 MAMP 问题的 NetBeans Xdebug 路径
- swift - 在 Swift 中将 CoreData 导出为 CSV
- xml - 使用 XML 命名空间的不太复杂的 XPath 表达式