Autosum with VBA

Hi there,

I need a VBA segment to generate the sum formulas into cells in column A.

The code would look at each cell in column B (from B1 to the last row with entries in row C eg

Range("b1:b" & Range("c" & Rows.Count).End(xlUp).Row).Select

If for example,

B3 contains "x" then the formula in A3 will be sum(A1:A2);

B4:B10 are all blank, therefore A4:A10 will be blank;

B11 has "x" then the formula in A11 will be sum(A4:A10)

I could do a sumif based on column B, however, data in column B would be discarded upon the completion of the report.

Thanks in advance. Can't attach the example from work

Hi,
It's lengthy

The arrangement in column A in the original report is as follow:

4 blank rows, the 5th will sum the above 4 ie A1:A4 is blank, the formula in A5 is sum(A1:A4). Next, A6:A9 is blank, the formula in A10 is sum(A6:A9).

The problem is, the users will mess it up each month by deleting/adding rows to the report. Hence, the cells with the sum formulas won't sum properly.

For example, after the modification, the user sends me the report back with:

A5 is sum(A1:A4)
A11 is sum(A7:A10)
A14 is sum(etc.

I use column C to flag any cell in column A which contain the word "Sum". Therefore, I will have the following cells in column C with the word "x".

c5, c11, c14. the other cells in c column will be blank. Thanks.

Pl see the attached file with macro "FomulaInB".
If your problem is solved mark the thread "SOLVED".

Hi,

Thanks for the solution. The sample file works perfectly.