I have a worksheet and i want to assign a formula to a cell. I have large worksheet, and it has data already. What i wanna do is i should scan through column A and whenever i encounter "total" (say at A12), i should activate the cell adjacent to it (A13) and assign the formula (=SUM(B6:B11)) such that that above six column's total should come in B13.

I have many worksheets that is the reason i need Macros to solve this.

Can anyone look into this, I tried with something like this but i messed up somehere and i was not able to activate the exact cell

ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"

thanks,

Kedar

here is the snapshot.

Book1 | ||||||
---|---|---|---|---|---|---|

A | B | C | D | |||

5 | ||||||

6 | b | 2 | ||||

7 | c | 3 | ||||

8 | d | 4 | ||||

9 | e | 5 | ||||

10 | f | 6 | ||||

11 | g | 4 | ||||

12 | total | ? | ||||

13 | ||||||

14 | ||||||

15 | ||||||

16 | ||||||

17 | b | 2 | ||||

18 | c | 3 | ||||

19 | d | 4 | ||||

20 | e | 5 | ||||

21 | f | 6 | ||||

22 | g | 4 | ||||

23 | total | ? | ||||

24 | ||||||

25 | ||||||

26 | ||||||

27 | b | 2 | ||||

28 | c | 3 | ||||

29 | d | 4 | ||||

30 | e | 5 | ||||

31 | f | 6 | ||||

32 | g | 4 | ||||

33 | total | ? | ||||

34 | ||||||

Sheet1 |