Multiple search with sum function
Posted by Jim Ciurczak on March 28, 2000 11:49 AM
Am looking for a way to look at cells A1 thru A10, A20 thru A30 and A40 thru A50. Within the search I need to look at each cell and determine if it is >1 or <6 and if so, sum all of the cell contents that meet the requirement.
Posted by Ivan Moala on March 29, 2000 3:25 AM
Jim
One way to do this is via a UDF (User defined Function)
Function CRg(ParamArray Rg())
Application.Volatile
Dim oCell
Dim Kount
Dim x As Integer
For x = 0 To UBound(Rg())
For Each oCell In Rg(x).Cells
If oCell.Value > 1 And oCell.Value < 6 Then
Kount = Kount + oCell.Value
End If
Next
Next
CRg = Kount
End Function
This particular function will accept multiple
ranges ie. more then 3 range areas through the
use of the Paranarray()
It will also update your value if the range
values change.
Ivan
Posted by bill.roberts on March 29, 2000 7:20 AM
Ivan,
I plugged this function into a MODULE.
I named range A1:C10 as PARAMARRAY
Then, I simply let an arbitrary cell =CRg().
The result is always ZERO.
What have I done incorrectly?
Posted by Ivan Moala on March 29, 2000 8:16 PM
In The Function CRg(Paramarray())
The Paramarray keyword, in this function can accept any number of arguments, which are all stored in the array that follows the Paramarray keyword, ie say for example your have 3 ranges
to evaluate then the Use of Paramarray allows you
to have 3 ranges. In your example you don't have to
name the range just input it as;
=CRg(A1:C10)
If you had more ranges say A1:A30, B24:C35, Z1:Z5
then your formula would be;
=cRg(A1:A30,B24:C35,Z1:Z5)
HTH
Ivan
Posted by bill.roberts on March 30, 2000 8:00 AM
Ahhh!
I did not reazile that PARAMARRAY was a reserved word!!
I renamed A1:C10 uniquely and supplied that name to the function.
It worked!
Thank you much!
Bill
