Need help with a VB function
Posted by Richard A on January 23, 2002 11:35 PM
I have written the following function for use in excel;
Function test(a1, a2, a3, b1, b2, b3)
test = 0
If a1 <> "x" Then test = test + b1
If a2 <> "x" Then test = test + b2
If a3 <> "x" Then test = test + b3
End Function
It works as it is, if the cell assigned to each "a" is not equal to "x" then the value in each "b" is added to the total "test". This solution works great with a small number of items.
I would like to do a similar function for a large number of items. I'm looking for help on how to specify a range in the function, such as (a1 thru a200) instead of (a1, a2, a3, etc.), and do something like "for each a <> "x" then test = test + b".
I hope my question makes sense.
Thanks in advance for any help.
Posted by Tom Dickinson on January 24, 2002 12:02 AM
Function test(StrtRw, EndRw)
Dim Cntr as Integer
test = 0
For Cntr = StrtRw to EndRw
If range("A" & Cntr) <> "x" then
test = test + Range("B" & Cntr)
End if
Next
End Function
Posted by Luciana on January 24, 2002 12:12 AM
Function test(rng1 As range, rng2 As range)
Dim cell As range, diff%
diff = rng2.Column - rng1.Column
For Each cell In rng1
If cell.Value <> "x" Then
test = test + cell.Offset(0, diff).Value
End If
Next
End Function
Posted by Juan Pablo G. on January 24, 2002 6:33 AM
Why not use a regular formula ? would be MUCH faster.
=SUMPRODUCT((A1:A200="X")*B1:B200)
Juan Pablo G.
