Count the blank cells to the given input value

[kiran_10]

In Excel sheet,
Cell A5 will be given input value (ie., 1 to 5).
If A5 = 1, then in cell range A8 to A30, it should count cell to the input value [which is 1. ie., A8, A9 etc] and that each cell will run small formula "=SUM(O17:T17)" and blank cells in between it should print "-"

If A5 = 2, then in cell range A8 to A30, it should count cell to the input value [ie, A8, A10(A8+2), A12(A10+2) etc., upto A30]and in these each cell will run small formula "=SUM(O17:T17)" and blank cells in between it should print "-"

If A5 = 3, then in cell range A8 to A30, it should count cell to the input value (ie, A8, A11(A8+3), A14 (A11+3) etc.,upto A30) and in these each cell will run small formula "=SUM(O17:T17)" and blank cells in between it should print "-"

If A5 = 4, then in cell range A8 to A30, it should count cell to the input value (ie, A8, A12(A8+4), A16(A12+4) etc.,upto A30) and in these each cell will run small formula "=SUM(O17:T17)" and blank cells in between it should print "-"

If A5 = 5, then in cell range A8 to A30, it should count cell to the input value (ie, A8, A13(A8+5), A18(A13+5) etc.,upto A30) and in these each cell will run small formula "=SUM(O17:T17)" and blank cells in between it should print "-"

And the input values will range from 1 to 5.
And input cells will be from A5 to A60.
It would be relally appreciate, if any one one could give/wite VB script please.

Thanks.
Kir.

[kiran_10]

Sorry, I have done some mistake in my question (confused in columns/rows, I have revised it below, experts please do respond. Thanks.

Excel sheet,
Cell A5 will be given input value (ie., 1 to 5).
If A5 = 1, then in cell range A8 to AD8, it should count cell to the input value [which is 1. ie., A8, B8, C8 etc] and that each cell will run small formula "=SUM(O17:T17)" and blank cells in between it should print "-"

If A5 = 2, then in cell range A8 to AD8, it should count cell to the input value [ie, A8, C8(A8+2), E8(C8+2) etc., upto AD8]and in these each cell will run small formula "=SUM(O17:T17)" and blank cells in between it should print "-"

If A5 = 3, then in cell range A8 to AD8, it should count cell to the input value (ie, A8, D8(A8+3), G8 (D8+3) etc.,upto AD8) and in these each cell will run small formula "=SUM(O17:T17)" and blank cells in between it should print "-"

If A5 = 4, then in cell range A8 to AD8, it should count cell to the input value (ie, A8, E8(A8+4), I8(E8+4) etc.,upto AD8) and in these each cell will run small formula "=SUM(O17:T17)" and blank cells in between it should print "-"

If A5 = 5, then in cell range A8 to AD8, it should count cell to the input value (ie, A8, F8(A8+5), K8(F8+5) etc.,upto AD8) and in these each cell will run small formula "=SUM(O17:T17)" and blank cells in between it should print "-"

And the input values will range from 1 to 5.
And input cells will be from A5 to A60.
It would be relally appreciate, if any one one could give/wite VB script please.

Thanks.
Kir.

[Teme64]

This can be done with a simple Excel formula so you don't need any VBA scripting for this.

First you have to figure out how the column number is related to input value (1-5). I used MOD() function to find out when a row "matches" the input value. Cell range (A8:AD8) starts from the column 1 so I calculate MOD(1; <input value>). With the given input range 1-5 column numbers which should have SUM() formula are MOD(<column num>; <input value>) = 1 for input values 2-5. Input value 1 is a "special case" so I used a separate test if it is 1.

Some formulas you need are:
- COLUMN() which gives the column number of the current column
- MOD() to calculate modulus
- IF() to output sum value or "-"

I try to attach a few pictures how the final result looks and how the formulas look.

But here is the formula:
- in the cell A8 (actually every cell in the range (A8:AD60): =IF(AND($A$5 >= 1; $A$5 <= 5); (IF(OR($A$5 = 1; MOD(COLUMN(); $A$5) = 1); SUM($O$2:$T$2); "-")); "")
the first (outer) IF() formula is just for testing that the input value is valid. The second (inner) IF() formula does the actual testing if this column should have a SUM() or "-". I used absolute references in the SUM() formula and filled cell range (O2:T2) with values 1, 2, 3, ..., 6.

I changed the cell range (O17:T17) to (O2:T2) because cell range (O17:T17) overlaps with (A8:AD60) and would cause a circular reference error.

This was quite easy one, right? And the final word of caution: I use Finnish localization and semicolon in the formulas. You have to change semicolons to comma or whatever formula separator character is used in your locale.

HTH