Macro and assign formula to a cell

kmyadam

New Member
Joined
Oct 6, 2005
Messages
38
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, kmyadam,
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
    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

New Member
Joined
Oct 6, 2005
Messages
38
Thanks Erik,

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

Thanks a lot,
Kedar
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
        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

New Member
Joined
Oct 6, 2005
Messages
38

ADVERTISEMENT

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
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

New Member
Joined
Oct 6, 2005
Messages
38

ADVERTISEMENT

Thanks HalfAce,

it works!!! great...
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

New Member
Joined
Oct 6, 2005
Messages
38
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
kmyadam said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,588
Messages
5,597,046
Members
414,116
Latest member
sfullnet

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top