Macro and assign formula to a cell

[kmyadam]

Hello everyone,

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

 

[erik.van.geit]

Hello, kmyadam,
try this as a start

Sub add_totals()
Dim rng As Range
Dim c As Range
Dim FA As String

Set rng = Columns(1)
With rng
Set c = .Find("total", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If Not c Is Nothing Then
    FA = c.Address
        Do
        c.Offset(0, 1).Formula = "=SUM(R[-6]C:R[-1]C)"
        Set c = .FindNext(c)
        Loop While FA <> c.Address And Not c Is Nothing
    End If
End With
End Sub

do you want it to loop for all sheets in the workbook ?
kind regards,
Erik

 

[kmyadam]

Thanks Erik,

That works great, How do i do apply for the total workbook.??

Thanks a lot,
Kedar

 

[erik.van.geit]

Kedar,
try this
EDIT: there is a "little mistake in this code"
so it won't work for all sheets
Set rng = Columns(1) should be Set rng = sh.Columns(1)

Sub add_totals()
Dim rng As Range
Dim c As Range
Dim FA As String
Dim sh As Worksheet

For Each sh In Worksheets
    Set rng = Columns(1)
    With rng
    Set c = .Find("total", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
        If Not c Is Nothing Then
        FA = c.Address
            Do
            c.Offset(0, 1).Formula = "=SUM(R[-6]C:R[-1]C)"
            Set c = .FindNext(c)
            Loop While FA <> c.Address And Not c Is Nothing
        End If
    End With
Next sh
End Sub

you can see how it is put in a loop
for each sh in sheets
...
next sh

best regards,
Erik

 

[kmyadam]

Thanks Erik,


This is working fine but i have column A and B Merged, So when i put this code in actual document to be executed this is not executing. I thinks the code is not executing because column A and Column B are merged, can you suggest me the changes. My actual workbook looks something like this,

Book1.xls
	A	B	C	D
7
8
9			2
10			5
11			22
12			2
13			3
14			3
15	total		?
16
17
18
19			2
20			5
21			22
22			2
23			3
24			3
25	total		?
26
27
28
Sheet2

 

[HalfAce]

Hello Kedar,
I don't see Erik at the moment, so I'll jump in.
What you're experiencing is why you'll often hear (at least I did), that "merged cells are the devil!" :x

You should be able to get away with setting rng as columns A and B instead of just A. Try changing the line:
Set rng = Columns(1)
to:
Set rng = Columns("A:B")

Hope it helps.

 

[kmyadam]

Thanks HalfAce,

it works!!! great...

 

[erik.van.geit]

kmyadam,

did you try to get rid of the merged cells ?
do you really need them ?
do you have data in both columns or is it just for layout puposes ?

do you know about "center text across selection"
example
A1: total
select A1:B1
menu Format / cellproperties / 2nd tab (how is this in english, please tell me)
horizontal allignment
choose "center across selection" (right in english ?)

best regards,
Erik

 

[kmyadam]

Hi Erik,

I used the merged cells for Layout purpose. I think i dont need "center text across selection", thanks for that.


regards,
kedar

 

[HalfAce]

I used the merged cells for Layout purpose. I think i dont need "center text across selection", thanks for that.

Hello Kedar,
You're right as far as not needing the Center across selection. What Erik's suggesting is to use it instead of merging your cells, thereby avoiding the undesireable effects that merging can have. (such as the one you found that required setting rng to include both columns.)

Center across selection would make it look the same as it does with merged cells, but the values (in this case) would all actually be in column A, and therefore easier on the programmer to not have to remember to take the merging into account.

Hope it helps.