SumProduct as function in VBA

  • Thread starter Thread starter olssonj
  • Start date Start date
O

olssonj

Hi all, I am new to macros and VBA. Have a question concerning SumProduct.

I have a worksheet with 20 something active columns. In a for-loop I want to
ad dates from column G that depends on values from two other columns B and F.
I am trying to use SumProduct in VBA. See description and my code below:

Column B ColumnF ColumnG
Customer1 ProcessActivitity1 Date
Customer2 ... ...
....

MyDateList(x) = Application.WorksheetFunction. _
SumProduct(--([B:B] = Customer), --([F:F] = MyProcessList(x)),
--([G:G]))

Error message: "Incompatible types" (Hope the spelling is right :))

Thank you in advance! I am sure its a simple misstake I'v made, beginner and
all...

Take care!

//Jonas
 
=sumproduct() expects arrays as its arguments.

If you want to use application.worksheetfunction.sumproduct, then you'll have to
make those arguments arrays--maybe looping through each cell and returning 1's
or 0's.

And this destroys the idea around using =sumproduct()--why not just loop and
accumulate?

But you could let excel help you by using .Evaluate(). This is like using a
helper cell in the worksheet and letting excel calculate the result.

But when you do this, you have to follow excel's rules. Unless you're using
xl2007, you can't use the complete column.

Here's a small example with the ranges hardcoded:

Option Explicit
Sub testme()
Dim customer As String
Dim myFormula As String
customer = "asdf"

myFormula = "sumproduct(--(b1:b9=""" & customer & """),g1:g9)"
MsgBox ActiveSheet.Evaluate(myFormula)

End Sub

This is the formula that's being evaluated:
=sumproduct(--(b1:b9="asdf"),g1:g9)

If I used application.evaluate(), then the ranges on the activesheet would be
used.

I could have specified the addresses (including sheet names) or I could use:

workhsheets("sheet9999").evaluate(myformula)
if both ranges were on sheet9999


Hi all, I am new to macros and VBA. Have a question concerning SumProduct.

I have a worksheet with 20 something active columns. In a for-loop I want to
ad dates from column G that depends on values from two other columns B and F.
I am trying to use SumProduct in VBA. See description and my code below:

Column B ColumnF ColumnG
Customer1 ProcessActivitity1 Date
Customer2 ... ...
...

MyDateList(x) = Application.WorksheetFunction. _
SumProduct(--([B:B] = Customer), --([F:F] = MyProcessList(x)),
--([G:G]))

Error message: "Incompatible types" (Hope the spelling is right :))

Thank you in advance! I am sure its a simple misstake I'v made, beginner and
all...

Take care!

//Jonas
Click to expand...
 
Thanks Dave!!!

I feel that it must exist an easier way to match values in two (or more)
columns and returning the value of the same row in a third column. I am not
in the need of ackumulating values, only returning the actual date in each
case when two (or more) column values match indata statements.

I refuse to make long for- and/or if- statements ;) I want to learn to be
efficient :)

Appreciate your further assistance!!! Thanks for your patience!!!

//Jonas

Dave Peterson said:
=sumproduct() expects arrays as its arguments.

If you want to use application.worksheetfunction.sumproduct, then you'll have to
make those arguments arrays--maybe looping through each cell and returning 1's
or 0's.

And this destroys the idea around using =sumproduct()--why not just loop and
accumulate?

But you could let excel help you by using .Evaluate(). This is like using a
helper cell in the worksheet and letting excel calculate the result.

But when you do this, you have to follow excel's rules. Unless you're using
xl2007, you can't use the complete column.

Here's a small example with the ranges hardcoded:

Option Explicit
Sub testme()
Dim customer As String
Dim myFormula As String
customer = "asdf"

myFormula = "sumproduct(--(b1:b9=""" & customer & """),g1:g9)"
MsgBox ActiveSheet.Evaluate(myFormula)

End Sub

This is the formula that's being evaluated:
=sumproduct(--(b1:b9="asdf"),g1:g9)

If I used application.evaluate(), then the ranges on the activesheet would be
used.

I could have specified the addresses (including sheet names) or I could use:

workhsheets("sheet9999").evaluate(myformula)
if both ranges were on sheet9999


Hi all, I am new to macros and VBA. Have a question concerning SumProduct.

I have a worksheet with 20 something active columns. In a for-loop I want to
ad dates from column G that depends on values from two other columns B and F.
I am trying to use SumProduct in VBA. See description and my code below:

Column B ColumnF ColumnG
Customer1 ProcessActivitity1 Date
Customer2 ... ...
...

MyDateList(x) = Application.WorksheetFunction. _
SumProduct(--([B:B] = Customer), --([F:F] = MyProcessList(x)),
--([G:G]))

Error message: "Incompatible types" (Hope the spelling is right :))

Thank you in advance! I am sure its a simple misstake I'v made, beginner and
all...

Take care!

//Jonas
Click to expand...
Click to expand...
 
Maybe you can use Edit|find (in code, though) to search through the first
column. Then check the second (and third and ...) columns for a match.

If you match what you want, then retrieve the value from the other cell.

Or maybe you could apply Data|Filter|autofilter and extract the value from the
visible cell(s).
Thanks Dave!!!

I feel that it must exist an easier way to match values in two (or more)
columns and returning the value of the same row in a third column. I am not
in the need of ackumulating values, only returning the actual date in each
case when two (or more) column values match indata statements.

I refuse to make long for- and/or if- statements ;) I want to learn to be
efficient :)

Appreciate your further assistance!!! Thanks for your patience!!!

//Jonas

Dave Peterson said:
=sumproduct() expects arrays as its arguments.

If you want to use application.worksheetfunction.sumproduct, then you'll have to
make those arguments arrays--maybe looping through each cell and returning 1's
or 0's.

And this destroys the idea around using =sumproduct()--why not just loop and
accumulate?

But you could let excel help you by using .Evaluate(). This is like using a
helper cell in the worksheet and letting excel calculate the result.

But when you do this, you have to follow excel's rules. Unless you're using
xl2007, you can't use the complete column.

Here's a small example with the ranges hardcoded:

Option Explicit
Sub testme()
Dim customer As String
Dim myFormula As String
customer = "asdf"

myFormula = "sumproduct(--(b1:b9=""" & customer & """),g1:g9)"
MsgBox ActiveSheet.Evaluate(myFormula)

End Sub

This is the formula that's being evaluated:
=sumproduct(--(b1:b9="asdf"),g1:g9)

If I used application.evaluate(), then the ranges on the activesheet would be
used.

I could have specified the addresses (including sheet names) or I could use:

workhsheets("sheet9999").evaluate(myformula)
if both ranges were on sheet9999


Hi all, I am new to macros and VBA. Have a question concerning SumProduct.

I have a worksheet with 20 something active columns. In a for-loop I want to
ad dates from column G that depends on values from two other columns B and F.
I am trying to use SumProduct in VBA. See description and my code below:

Column B ColumnF ColumnG
Customer1 ProcessActivitity1 Date
Customer2 ... ...
...

MyDateList(x) = Application.WorksheetFunction. _
SumProduct(--([B:B] = Customer), --([F:F] = MyProcessList(x)),
--([G:G]))

Error message: "Incompatible types" (Hope the spelling is right :))

Thank you in advance! I am sure its a simple misstake I'v made, beginner and
all...

Take care!

//Jonas
Click to expand...
Click to expand...
Click to expand...
 
Back
Top