Simplify Count Code

Good day everyone,

I'm using this crude code to do some counting and I want to ask if someone knows how to make the code smaller and thus quicker.


Thank you for any help offered,

Roberto Lucesi

Please Login or Register  to view this content.

Why the need for A1:F6 etc... why not just

Please Login or Register  to view this content.

Maybe

Please Login or Register  to view this content.

EDIT

SOrry didn't see your second post...

untested but perhaps (if I've interpreted your code correctly)

Please Login or Register  to view this content.

of course the above is simply designed to use only row 84 - if in fact you want to have the formulae in rows 70:75 then you can... let us know.

Originally Posted by roberto1111

.FormulaR1C1 = "=SUMPRODUCT(--(MOD(ROW(R88C:R114C)-1,6)<3),--ISNUMBER(R88C:R114C))"

Roberto, I'm not sure what you're asking in the above...

If you're saying you want to conduct a COUNT of say rows:

88:90, 94:96, 100:102, 106:108, 112:114

then you can change the <3 to >=3 in the formula.

I believe the above voids the below query ?

Originally Posted by roberto1111

.FormulaR1C1 = "=SUMPRODUCT(--(MOD(ROW(R91C:R117C)-1,6)<3),--ISNUMBER(R88C:R114C))"

(ie both concerned with conducting a count against 88:114 in batches of alternating 3 rows commencing from row 88 which I believe the first suggestion resolves)

Probably easier to outline the premise of the function rather than continually adapting the code lines.

Using your last example - where you want to conduct a COUNT against rows

90:94, 100:104, 110:114, 120:124

we can see then that though the ranges are non-contiguous they do follow a pattern - ie COUNT 5 rows then ignore 5 rows, COUNT 5 rows, then ignore 5 rows etc...

Given this "pattern" we can look to utilise the MOD function to help us identify which rows in the range 90:124 are to have the COUNT function applied to them and which are to be ignored.

The MOD function

=MOD(number,divisor)

will return the remainder once "number" has been divided by specified "divisor", eg:

=MOD(10,3) --> returns 1

this is of course because the greatest multiple of 3 <= 10 is 9 and 10 - 9 = 1

We can use this function to identify the rows we need by setting the divisor and remainder test appropriately.

In this instance our divisor will be 10 given we're working in batches of 10 rows at a time (5 "on", 5 "off" so to speak)
We will use ROW function to establish our "number, if we use the example of range RG90 this means initially our "number" will be 90, for RG99 it would be 99

So using range RG90:RG99 as an example

=MOD(ROW(RG90),10) -> 0
=MOD(ROW(RG91),10) -> 1
=MOD(ROW(RG92),10) -> 2
=MOD(ROW(RG93),10) -> 3
=MOD(ROW(RG94),10) -> 4
=MOD(ROW(RG95),10) -> 5
...
=MOD(ROW(RG99),10) -> 9

It follows that RG100:RG109 will equally result in 0 -> 9

So we know then that in this instance the MOD remainders we're interested in will always be 0 to 4 and so we can set our SUMPRODUCT accordingly, ie in native XL terms:

Please Login or Register  to view this content.

At this point it's worth stressing the fact that both arrays in the SUMPRODUCT will return 0 / 1 values - a value for each cell in the range (FALSE/TRUE respectively) - these two arrays are then multiplied together.
So in reality the ISNUMBER test is applied to each cell in RG90:RG124 however, only if the MOD of the ROW is between 0 & 4 will that test be taken into account - ie if RG95 is a number the output would still be 0 given the MOD test would return 0 and 0 * 1 is 0

For more info. on SUMPRODUCT see the link in my sig.

So in VBA terms - applying the above formula to your range:

Please Login or Register  to view this content.

In some cases - as outlined earlier depending on the setup of the ranges etc it's necessary to adjust the ROW value (-1) before applying the MOD to ensure you get the desired results - the fact that in this instance you're working in blocks of 10 rows here means that is not necessary.
The key is to ensure the MOD function applied returns a consistent set of values for the entire range such that you can calculate based on the remainder.
Also worth noting that this is only possible because there is a consistent pattern in terms of establishing ranges to be included in calcs and those to be excluded... if it wasn't an even pattern (ie 5 on 4 off, 5 on 3 off) you would not be able to use this approach.