# Macro and assign formula to a cell

##### New Member
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
ABCD
5
6b2
7c3
8d4
9e5
10f6
11g4
12total?
13
14
15
16
17b2
18c3
19d4
20e5
21f6
22g4
23total?
24
25
26
27b2
28c3
29d4
30e5
31f6
32g4
33total?
34
Sheet1

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
try this as a start
Code:
``````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
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

Thanks Erik,

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

Thanks a lot,
Kedar

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)
Code:
``````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
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

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
ABCD
7
8
92
105
1122
122
133
143
15total?
16
17
18
192
205
2122
222
233
243
25total?
26
27
28
Sheet2

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.

Thanks HalfAce,

it works!!! great...

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

Hi Erik,

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

regards,
kedar

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.

Replies
2
Views
157
Replies
3
Views
86
Replies
12
Views
799
Replies
3
Views
216
Replies
12
Views
552

1,216,574
Messages
6,131,499
Members
449,653
Latest member
aurelius33

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back