CODEWITHSUNDEEP
vbaexcel
PootyToot
PootyToot

Reputation: 329

Excel VBA - Issue with sumproduct VBA script

I am currently building a tool in order to take extracts from SAP and put them into a financial management tool (or Financial Tool). The tool successfully pulls actual data from a SAP CJ74 report, and normalizes the data.

I have a requirement to put a sumproduct function into a spreadsheet to extract data from a data source into the spreadsheet using names and dates as the product match. One sheet is for CAPEX spend, the other sheet is for OPEX spend. The two sheets are replicas of each other.

I attempted to write some code in order to execute my requirement below, but I am getting application defined or object defined error when executing the code. Could I get some assistance with either a better way of doing this, or correcting the errors below?

NOTE: the sheet where this code is running from is the 'Exporter tool'

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

Upvotes: 0

Views: 205

Answers (2)

L42
L42

Reputation: 19727

You assign formula in VBA in a form of string. So this code for example:

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

will error out since it is like a multiple Boolean expression (e.g. x = y = z) which is also not accepted in VBA.

To do what you want, try this:

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

Upvotes: 1

Dan Donoghue
Dan Donoghue

Reputation: 6196

This bit concerns me:

Range("5:5" & LastColumn)

If you have say 4 for example as your last column, That range will be Rows 5 - 54 ALL COLUMNS because you are asking for "5:5" & 4 as a string that is "5:54", is this what you are expecting?

Also, where are LastRow and LastColumn being defined?

Upvotes: 1

Related Questions