How do I use a function in a SUMIF in VBA?

[Drawleeh]

Hello, I have the following code which (should) create an array.

Function UnitCheckArr()
Dim UnitValueArr(2 To 250) As Long
Dim UnitValue As Long


For UnitValue = LBound(UnitValueArr) To UBound(UnitValueArr)
UnitValueArr(UnitValue) = Cells(UnitValue, 4) * Cells(UnitValue, 6)
Next UnitValue

End Function

I want to use this array as part of a WorksheetFunction.SUMIF as the [RANGE SUM] for something like this:

Sub NetSumIF()
If [COUNTA(F2:F250)=0] Then

Worksheets("Sheet1").Range("K2:K250") = Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range("I2:I250"), "I2:I250", Worksheets("Sheet1").Range("UnitCheckArr"))

End If
End Sub

But currently its not working and throwing up an application defined run time error. Any help in figuring this out would be much appreciated.

 

[jasonb75]

You can only pass a Range to the [RANGE SUM] argument of sumif (there's a big clue in the tooltip).

As the array appears to be the product of columns D and F, you would be better off using sumproduct with D2:D250 and F2:F250 as ranges in the formula and dropping the array code.

 

[Drawleeh]

You can only pass a Range to the [RANGE SUM] argument of sumif (there's a big clue in the tooltip).

As the array appears to be the product of columns D and F, you would be better off using sumproduct with D2:D250 and F2:F250 as ranges in the formula and dropping the array code.

I was using the array as I didin't want to parse the result of those 2 columns into the worksheet itself and just hold them as an array in a function to be used later? Is there any other way I can hold that calculation as a range to be used in the sumif instead?

 

[jasonb75]

Is there any other way I can hold that calculation as a range to be used in the sumif instead?

A range is exactly what is in the worksheet. Once you make any kind of change to it in vba (a calculation) it becomes an array.

I was using the array as I didin't want to parse the result of those 2 columns into the worksheet itself and just hold them as an array in a function to be used later?

If you use sumproduct instead of sumif then you don't need to do either, it will be done as a single process. Unless you're going to assign the array produced by the function to a variable to be used multiple times in the same procedure, it serves no purpose whatsoever.

What is the correct calculation required for K2 (as a single cell)? Perhaps if the requirement of the output was clearer then it would be possible to suggest a viable alternative.

 

[Drawleeh]

A range is exactly what is in the worksheet. Once you make any kind of change to it in vba (a calculation) it becomes an array.

If you use sumproduct instead of sumif then you don't need to do either, it will be done as a single process. Unless you're going to assign the array produced by the function to a variable to be used multiple times in the same procedure, it serves no purpose whatsoever.

What is the correct calculation required for K2 (as a single cell)? Perhaps if the requirement of the output was clearer then it would be possible to suggest a viable alternative.

Well I've been trying to adapt a worksheet that I had made with formulas into VBA as it's gotten very slow and I've been stumped on how to store output without putting it into the worksheet itself and then use it for further calculation.

I have column A and B which is concatenated to provide the range for my SUMIF. Then I multiply 2 ranges to get my [SUM RANGE] value for the sum-if. Finally I use the unique column that I get from an index match function (in vba) as my sumif criteria.

I've been trying to create a VBA that could do the same as the formulas without the need to show the "Concatenate" and the "Value" columns on the worksheet, thats why I have been trying to use array's to store that information first.

TESTING.xlsm
	A	B	C	D	E	F	G	H	I	J
1	CODE	ORIGIN	Concatenate	Quantity	Unit Net	Value	Net Weight	Gross Weight	UNIQUE	Net Weight HS
2	96082000	GB	96082000 GB	11	20	220			96082000 GB	1220
3	32131000	GB	32131000 GB	24	20	480			32131000 GB	480
4	32139000	JP	32139000 JP	20	20	400			32139000 JP	1700
5	32139000	GB	32139000 GB	41	20	820			32139000 GB	820
6	32139000	CN	32139000 CN	23	20	460			32139000 CN	6460
7	49111090	CN	49111090 CN	11	20	220			49111090 CN	2120
8	49111090	CN	49111090 CN	13	20	260			49111090 GB	1320
9	49111090	GB	49111090 GB	45	20	900			94036030 GB	3580
10	94036030	GB	94036030 GB	54	20	1080			94036030 CN	320
11	94036030	CN	94036030 CN	14	20	280
12	94036030	GB	94036030 GB	20	20	400
13	94036030	GB	94036030 GB	12	20	240
14	94036030	GB	94036030 GB	20	20	400
15	32139000	JP	32139000 JP	21	20	420
16	32139000	JP	32139000 JP	20	20	400
17	49111090	CN	49111090 CN	41	20	820
18	49111090	GB	49111090 GB	21	20	420
19	94036030	GB	94036030 GB	20	20	400
20	94036030	CN	94036030 CN	2	20	40
21	94036030	GB	94036030 GB	21	20	420
22	94036030	GB	94036030 GB	20	20	400
23	94036030	GB	94036030 GB	12	20	240
24	32139000	JP	32139000 JP	12	20	240
25	32139000	JP	32139000 JP	12	20	240
26	32139000	CN	32139000 CN	300	20	6000
27	49111090	CN	49111090 CN	20	20	400
28	49111090	CN	49111090 CN	21	20	420
29	96082000	GB	96082000 GB	50	20	1000
Sheet1

Cell Formulas
Range	Formula
J2:J10	J2	=SUMIF(C$2:C$250,I2,F$2:F$250)
C2:C29	C2	=A2&" "&B2
F2:F29	F2	=D2*E2

 

[jasonb75]

What else do you have in your worksheet?

The reason that I ask is because I think you are looking in the wrong place to improve performance. The formulas in columns C and E are too simple to cause any problems, even with large volumes of data. Even the sumif formula wouldn't cause slow processing unless you have in the region of 10,000 unique records in column I.

You could eliminate the need for concatenating by using SUMIFS instead of SUMIF but I doubt that it will make much difference, if any. I would be more inclined to keep the helper columns (C and E) then generate the output with a pivot table from those 2 columns instead of using formulas.

Things that can cause slowness are heavy use of conditional formatting, formulas (or named range definitions) that use volatile functions such as OFFSET, INDIRECT, TODAY and NOW (there are more but these are the most common culprits).

 

[Drawleeh]

What else do you have in your worksheet?

The reason that I ask is because I think you are looking in the wrong place to improve performance. The formulas in columns C and E are too simple to cause any problems, even with large volumes of data. Even the sumif formula wouldn't cause slow processing unless you have in the region of 10,000 unique records in column I.

You could eliminate the need for concatenating by using SUMIFS instead of SUMIF but I doubt that it will make much difference, if any. I would be more inclined to keep the helper columns (C and E) then generate the output with a pivot table from those 2 columns instead of using formulas.

Things that can cause slowness are heavy use of conditional formatting, formulas (or named range definitions) that use volatile functions such as OFFSET, INDIRECT, TODAY and NOW (there are more but these are the most common culprits).

Unfortunately that is only one snippet I have created for the purposes of explaining the above, the actual worksheet is very heavy with thousands of entries, and these helper columns are 2 of many that are used to create ranges for other formula to work from.

 

[RoryA]

Perhaps you'd be better off with a pivot table for summarising the data.