sumproduct formula in vba

[RivetHed]

Hi all, having a little problem with this sumproduct formula in VBA, it compiles fine but throws out a Type mismatch error at runtime.

rngdata is my raw data
rngsum1 is a summary table I am populating
Introw is an integer value
Intcolumn is an integer value

RngSum1.Cells(IntRow, IntColumn) = Application.WorksheetFunction. _
SumProduct((Range(RngData.Cells(2, 1), _
RngData.Cells(RngData.Rows.Count, 1)) = "General Practitioners") * _
(Left(Range(RngData.Cells(2, 2), RngData.Cells(RngData.Rows.Count, 2)), _
Len(RngSum1.Cells(IntRow, 1)) - 3) = Mid(RngSum1.Cells(IntRow, 1), 4, 100)) * _
(Range(RngData.Cells(2, IntColumn), RngData.Cells(RngData.Rows.Count, _
IntColumn))))

Any help greatly appreciated

 

[Zathras]

This is almost impossible to sort out.

What do your data look like?

What does the formula look like when you put it directly into a cell (without VBA)?

What are you really trying to do?

 

[TonyJollans]

Hi RivetHed,

I'm with Zathras - it's nigh impossible to see what you're trying to do.

But you do seem to have a circular reference ..

RngSum1.Cells( .... = ..... Mid(RngSum1.Cells( .....

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[kpetursson]

whats with all the _ at the end of the lines?
I think thoes are so you can continue you code on the next line, but the you have multiple = on the same line
It seems confusing.

Kevin Petursson

 

[RivetHed]

Hi guys, sorry possibly could have made this clearer.

The formula I'm trying to replicate is this:

=SUMPRODUCT(($A$4:$A$1060="General Practitioners")*(LEFT($B$4:$B$1060,LEN($A1070)-3)=MID($A1070,4,100))*(E$4:E$1060))

The col A and col E sections are nice and easy: where col A = General Practitioners sum col E, it's the col B section that's a little stranger I'm trying to select where the start of col E is the same as the end of a cell in the table I'm trying to populate.

This formula works fine just doesn't seem to like it when done through application.worksheetfunction.

The data is an absolute monstrosity A main Category in col A a sub-category in col B and a requester in col C then anywhere between 50 and 250 columns of data.

The plethora of summary tables I'm trying to automatically create have to be based on very specific criteria i.e. within the same summary table totals for categorys will be placed together with totals for some of the sub categories and summed across all columns of data.

Excel is the only option I'm given for producing this.

 

[RivetHed]

Just in case anyones interested now or in the future I have found a work around for this after a lot of web trawling.

Apparently sumproduct cannot be used for conditional summing from within vba only in its "traditional" fashion.

If you wish to perform a multiple criteria conditional sum within vba you can either:

Enter in the formula through .formula or .formulaArray and then copy and PasteSpecial as values.

Or alternatively use the Evaluate method, using this my formula listed above became:

RngSum1.Cells(IntRow, IntColumn) = Evaluate("SumProduct((" & _
RngData.Cells(2, 1).Address & ":" & _
RngData.Cells(RngData.Rows.Count, 1).Address & "=""General Practitioners"")" & _
"*(Left(" & RngData.Cells(2, 2).Address & ":" & _
RngData.Cells(RngData.Rows.Count, 2).Address & _
",Len(" & RngSum1.Cells(IntRow, 1).Address & ") - 3) = Mid(" & _
RngSum1.Cells(IntRow, 1).Address & ", 4, 100))*(" & _
RngData.Cells(2, IntColumn).Address & ":" & _
RngData.Cells(RngData.Rows.Count, IntColumn).Address & "))")

Not too impressed with either of these methods but guess it'll have to do....