SumProduct in VBA

[Brian]

Hello,

I am having trouble writing this into my VBA. I wrote this for 2007 however,
I need it to work for 2003. Here is what I had for the 2007 version:

c.Value = c & "-" & WorksheetFunction.CountIfs(Range("B2:B" & LastRow),
c.Offset(0, -1), Range("A2:A" & LastRow), c.Offset(0, -2))

I need to use WorksheetFuction with VBA since the formula is going to use
the current value of the cell. This line works great for 2007 as it is
COUNTIFS. However, I need to turn this into 2003 and use SUMPRODUCT. I know
the formula should work out to be
=SUMPRODUCT(($A$2:$A$22=A5)*($B$2:$B$22=B5)) with the A22 and B22 being a
variable cell based off my LastRow value.

I thought this might work, but it does not:

c.Value = c & "-" & WorksheetFunction.SumProduct((Range("B2:B" & LastRow) &
"=" & c.Offset(0, -1)) & "*" & (Range("A2:A" & LastRow) & "=" & c.Offset(0,
-2)))

Any ideas?

 

[Dave Peterson]

You have at least a couple of choices.

You can create a formula in C that will evaluate to what you want and then
convert it to a value:

So if the cell contained: Brian
the the formula would look like:
="Brian"&"-"&sumproduct(....)

Option Explicit
Sub testme2()
Dim wks As Worksheet
Dim myRng1 As Range
Dim myRng2 As Range
Dim myCell1 As Range
Dim myCell2 As Range
Dim myFormula As String
Dim LastRow As Long
Dim c As Range

Set wks = ActiveSheet

With wks
Set c = .Range("A1") 'whatever
'how was lastrow set?
'I used column A
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng1 = .Range("a2:a" & LastRow)
Set myRng2 = .Range("b2:b" & LastRow)
Set myCell1 = .Range("a5")
Set myCell2 = .Range("B5")

myFormula = "sumproduct(--(" & myRng1.Address _
& "=" & myCell1.Address & ")," _
& "--(" & myRng2.Address _
& "=" & myCell2.Address & "))"

With c
.Formula = "$=""" & .Value & """&""-""&" & myFormula
.Value = .Value
End With

End With

End Sub

Or you could use Evaluate to have VBA ask excel to help. This uses
worksheet.evaluate() (as opposed to application.evaluate()), so that the
unqualified ranges are against that sheet -- not necessarily the activesheet
(like application.evaluate()).

Option Explicit
Sub testme2()
Dim wks As Worksheet
Dim myRng1 As Range
Dim myRng2 As Range
Dim myCell1 As Range
Dim myCell2 As Range
Dim myFormula As String
Dim LastRow As Long
Dim c As Range

Set wks = ActiveSheet

With wks
Set c = .Range("A1") 'whatever
'how was lastrow set?
'I used column A
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng1 = .Range("a2:a" & LastRow)
Set myRng2 = .Range("b2:b" & LastRow)
Set myCell1 = .Range("a5")
Set myCell2 = .Range("B5")

myFormula = "sumproduct(--(" & myRng1.Address _
& "=" & myCell1.Address & ")," _
& "--(" & myRng2.Address _
& "=" & myCell2.Address & "))"

c.Value = c.Value & "-" & .Evaluate(myFormula)

End With

End Sub

ps. I like to use the -- syntax in sumproduct() functions when I can.

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Brian]

I really appreciate the help, this helped a lot. This is overall what I ended
up with:

Sub test()

Dim InputLR As Long
Dim OutputLR As Long
Dim OriginalRng As Range
Dim InputWks As Worksheet
Set InputWks = Worksheets("INPUT")
Dim OutputWks As Worksheet
Set OutputWks = Worksheets("OUTPUT")
Dim myRng1 As Range
Dim myRng2 As Range
Dim myCell1 As Range
Dim myCell2 As Range
Dim myFormula As String


InputLR = InputWks.Range("A1").End(xlDown).Row
OutputLR = Range("A1").End(xlDown).Row
Set OriginalRng = InputWks.Range("A1:C" & InputLR)

OutputWks.Cells.Clear
OriginalRng.Copy
OutputWks.Range("A1").PasteSpecial
OutputWks.Activate
OutputWks.Range("A2:C" & OutputLR).Sort Key1:=Range("A2"),
Key2:=Range("C2"), Order1:=xlAscending, Header:=xlNo

For Each c In Range("C2:C" & OutputLR)
With OutputWks
Set myCell1 = c.Offset(0, -2)
Set myCell2 = c.Offset(0, -1)
Set myRng1 = .Range("a2:a" & OutputLR)
Set myRng2 = .Range("b2:b" & OutputLR)

myFormula = "sumproduct(--(" & myRng1.Address & "=" &
myCell1.Address & ")," _
& "--(" & myRng2.Address & "=" & myCell2.Address & "))"
c.Value = c.Value & "-" & .Evaluate(myFormula)
End With
Next

End Sub

 

[Dana DeLouis]

Hi. Just an idea. I believe your MyRng1 & 2 are set to the same value
at each loop. Perhaps factor this out of the loop. Here is an
alternative to using strings.

For Each c In Range("C2:C"& OutputLR)
...
Set myRng1 = .Range("a2:a"& OutputLR)
Set myRng2 = .Range("b2:b"& OutputLR)

Set RngA = [A2].Resize(OutputLR - 1)
Set RngB = RngA.Offset(, 1) 'Next Column Over
Set RngC = RngB.Offset(, 1)

For Each C In RngC.Cells
Set myCell1 = C.Offset(0, -2)
Set myCell2 = C.Offset(0, -1)

MyFormula = ...

= = = = = = =
HTH :>)
Dana DeLouis