Sumproduct in VBA with a Space

[Tinkerz]

The Excel spreadsheet I use the formula
'=SUMPRODUCT((Accounts!S2:S65536= 1) * (Accounts!R2:R65536 = """ """))

Then I include it in VBA to run across a select number of sheets, I use the ws.new in the worksheet name to locate the file, this works as I have taken out some more simple code in the below loop

The error is a type mismatch but the formula looks ok to me, same as the one in excel, any other eyes on it would be great

Thanks


Dim wsnew As Worksheet
offsetcounter = 0
For Each wsnew In Worksheets
If InStr(1, wsnew.Name, "Accounts") > 0 Then

Application.WorksheetFunction.SumProduct((Worksheets(wsnew.Name).Range("S:S") = 1) * (Worksheets(wsnew.Name).Range("R:R") = """ """))
offsetcounter = offsetcounter + 1
End If
Next wsnew

 

[cgcamal]

Hi Tinkerz,

If the SUMPRODUCT spreedsheet formula works correctly for you, and the VBA SumProduct fails, try to
use the same spreedsheet formula within VBA as follow example:

Range("A1") = Evaluate("=SUMPRODUCT((Accounts!S2:S65536= 1) * (Accounts!R2:R65536 = """ """))")

Hope this helps,

Regards

 

[Tinkerz]

I get an compile error
Expected end of list or separater

I seem to have the right amount of Brackets and speech commas


Worksheets("Calculations").Range("E1") = Evaluate("=SUMPRODUCT((Accounts!S2:S65536= 1) * (Accounts!R2:R65536 = """ """))")

I am also trying
'Worksheets("Calculations").Range("C6").Offset(0, offsetcounter) = Application.WorksheetFunction.SumProduct((Worksheets(wsnew.Name).Range("S:S") = 1) * (Worksheets(wsnew.Name).Range("R:R") = """Chr(32)"""))

Chr(32)

I am trying to lookup " ", 2 speech marks and a space so googling it looks like """ """"

I am not sure on excel VBA and space, so i would like to put in CHR(32) instead of a blank space.

But it produces type mis match in runtime

How do i solve it?

 

[Tinkerz]

even a simple

Worksheets("Calculations").Range("E1").Formula = "SUMPRODUCT((Accounts!S2:S65536= 1) * (Accounts!R2:R65536 = """ """))"


is producing a Compile error

Expected: End of statement

 

[cgcamal]

Hi Tinkers,

Try SUMPRODUCT or SUM as examples below:

Excel Workbook
	A	B	C	D	E	F
1	1	data		Using FUNCTION	SUMPRODUCT()	SUM()
2	2			Result	2	2
3	5	data
4	1
5	7	data
6	7
7	1	data
8	1
9	8
(SUM() formula must be enter as array formula, with Ctrl+Shift +Enter)

Excel 2010

Cell Formulas
Range	Formula
E2	=SUMPRODUCT(--($A$1:$A$9=1),--($B$1:$B$9=""))

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

 

[Jonmo1]

Try

Worksheets("Calculations").Range("E1").Value = Evaluate("=SUMPRODUCT((Accounts!S2:S65536= 1) * (Accounts!R2:R65536 = "" ""))")