Excel VBA - sumproduct VBA脚本问题

Question

我目前正在构建一个工具，以便从SAP提取并将其放入财务管理工具(或财务工具)。该工具成功地从SAP CJ74报告中提取了实际数据，并对数据进行了标准化。

我需要将sumproduct函数放到电子表格中，以便使用名称和日期作为产品匹配将数据从数据源提取到电子表格中。一张是资本支出表，另一张是运营支出表。这两张纸是彼此的复制品。

我试图写一些代码来执行我下面的要求，但我在执行代码时遇到应用程序定义或对象定义的错误。我是否可以通过更好的方式或纠正下面的错误获得一些帮助？

注意:运行此代码的工作表是“导出器工具”

Sub SumproductAlternative()

Dim Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
Dim Copyrange As Long, MaxRow As Long
Dim CapexPersonName As Range, CapexActualsDate As Range, CapexActualsCell As Range, 
Dim OpexPersonName As Range, OpexActualsDate As Range, OpexActualsCell As Range
Dim OpexActualsDate As Range, CapexActualsDate as Range
Dim CapexPersonLookup As Range, OpexPersonLookup As Range

Set wb1 = Workbooks.Open("Financial Tool.xlsm")
Set wb2 = Workbooks.Open("Finance Extract.xlsm")
Set wb3 = Workbooks.Open("Exporter Tool.xlsm")

wb3.Worksheets("Config Sheet").Activate
Set CapexPersonName = ThisWorkbook.Sheets("Config Sheet").Range("A5:A" &     LastRow)
Set CapexActualsDate = ThisWorkbook.Sheets("Config Sheet").Range("5:5" & LastColumn)
Set CapexSumRange = ThisWorkbook.Sheets("Capex Pivot").Range("A4").CurrentRegion
Set CapexPersonLookup = wb2.Sheets("Capex").Range("E2:E1300")
Set CapexActualsCell = wb2.Sheets("Capex").Range("5:5" & LastColumn)


Set OpexPersonName = ThisWorkbook.Sheets("Config Sheet").Range("A5:A" & LastRow)
Set OpexActualsDate = ThisWorkbook.Sheets("Config Sheet").Range("5:5" & LastColumn)
Set OpexSumRange = ThisWorkbook.Sheets("Capex Pivot").Range("A4").CurrentRegion
Set OpexPersonLookup = wb2.Sheets("Opex").Range("E2:E1300")
Set OpexActualsCell = wb2.Sheets("Opex").Range("c3:c" & LastRow)


wb1.Sheets("CAPEX").Select
With wb1.Sheets("Capex")
 MaxRow = .Range("H528").CurrentRegion.rows.Count
 For Copyrange = MaxRow To 528 Step -1
     If .Cells(Copyrange).Interior.ColorIndex = 40 Then
        .Cells(Copyrange).Formula = " = SUMPRODUCT((CapexPersonName" = "CapexPersonLookup)*(CapexActualsDate" = "CapexActualsCell),(CapexSumRange)"
     End If
 Next Copyrange
End With

MaxRow = 0
Copyrange = 0

wb1.Sheets("OPEX").Select
With wb1.Sheets("Opex")
 MaxRow = .Range("H528").CurrentRegion.rows.Count
 For Copyrange = MaxRow To 528 Step -1
     If .Cells(Copyrange).Interior.ColorIndex = 40 Then
        .Cells(Copyrange).Formula = " = SUMPRODUCT((OpexPersonName" = "OpexPersonLookup)*(OpexActualsDate" = "OpexActualsCell),(OpexSumRange)"
     End If
 Next Copyrange
End With

Answer 1

这一点让我很担心：

Range("5:5" & LastColumn)

例如，如果您将4作为最后一列，则范围将是第5- 54行所有列，因为您请求"5:5“&4作为字符串"5:54"，这是您所期望的吗？

另外，LastRow和LastColumn是在哪里定义的？

Answer 2

在VBA中以字符串的形式指定公式。下面的代码是这样的：

.Cells(Copyrange).Formula = "=SUMPRODUCT((CapexPersonName" = "CapexPersonLookup)*(CapexActualsDate" = "CapexActualsCell),(CapexSumRange)"

将会出错，因为它类似于多个布尔表达式(例如x=y= z)，这在VBA中也是不被接受的。

要执行您想要的操作，请尝试以下操作：

Dim myformula As String
myformula = "=SUMPRODUCT((" & CapexPersonName.Address(, , , True) & "=" & _
    CapexPersonLookup.Address(, , , True) & ")*(" & _
    CapexActualsDate.Address(, , , True) & "=" & _
    CapexActualsCell.Address(, , , True) & _
    ")," & CapexSumRange.Address(, , , True) & ")"
.Cells(Copyrange).Formula = myformula