sumproduct in code

[Guest]

What I am trying to accomplish is this:
I am trying to use a for loop to create a table that generates a column
(aa). Based on the cells in AA, I want sumproduct (in column ab) to perform
add up the sums in another table (cause.column(3)) where the value in
causecats (which is the first column in the cause table) is equalt to the
value in AAr+1.

The values in AAr+1 are populated from a review of the values in causecats.

The code I am trying is this.
Cells(r + 1, "ab").Formula = "=sumproduct((" & causecats.Address & "=" & _
Cells(r + 1, "aa").Address & ")*(" & cause.Columns(3).Address & "))"

From my experimentation, I can not get the sumproduct to work unless I put
the value I am looking for within the first array in quotes. In other words,
I can not say
"=sumproduct((array1=a3)*(array2)"
This appears to only work if I put an actual value in place of a3 and place
it in quotes.
I think that is my problem above. Is there a way around it?

TIA

 

[Dave Peterson]

I don't have the solution for you, but maybe this will help.

When I have trouble plopping a formula into a cell, I'll change it to text.

Cells(r + 1, "ab").Formula = "sumproduct((" & causecats.Address & "=" & _
Cells(r + 1, "aa").Address & ")*(" & cause.Columns(3).Address & "))"

(I dropped the leading equal sign.)

Then I go back to the worksheet and see what happens when I add the equal sign
back.

Usually, it gets a lot easier to solve the problem.

 

[Tom Ogilvy]

Just for reassurance, that shouldn't be a problem. If it is, it would
indicate you are not constructing your formula properly and Dave has given
you some hints on how to test that (I normally use the immediate window -
but we all have our own methods).

 

[Dave Peterson]

If I used the immediate window, I'd still have to paste it into a cell to see
where I messed up my commas/parentheses <bg>.