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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
kedar,
this code runs fine with merged cells, OK, but ...

be aware that if you used merged cells just for layout, it's good practice to center text accross selection instead of using merged cells: you don't "need" it, but other code could bug, especially whenever you are going to copy the info and paste the values, hence Halfaces statement "merged cells is GRRR"

best regards,
Erik
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
To unmerge the cells you can highlight them and from the menu use
Edit > Clear > Formats.

Then to use Center across selection,
highlight the cells of interest and use
Format > Cells... > Alignment tab and in the "Text alignment" section (upper left area) open the "Horizontal" dropdown and choose Center across selection. (Hit OK) and that should do it.

This will (should) result in your layout looking the same as it does with the merged cells, but all the data (in your example) will actuall be in column A only.

(If you do this then you can change your code back to what Erik provided originally. ie.)
Set rng = Columns(1)

Hope it helps.
 

kmyadam

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

The data and layout of worksheets was already done by someone and it was given to me to make modifications like adding formula to Cell value, deleting the unnecessary rows , adding new rows etc. So i am in the process, I would like to do " center text accross selection" as long it does not affect the layout or structure or the data. But i dont know how do i do it.



One more thing i forgot to tell is that, now the new code does not run for the total workbook, i dont know why but it works only for the current active worksheet. Here is the code




Thanks for that,
Kedar


Code:
Sub Cell_formula()
  
  
Dim rng As Range
Dim c As Range
Dim FA As String
Dim sh As Worksheet

 

For Each sh In Worksheets
 
    Set rng = Columns("A:B")
    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
 

kmyadam

New Member
Joined
Oct 6, 2005
Messages
38

ADVERTISEMENT

Hi HalfAce,

I have many worksheets and i cannot do it manually, a kind of very large worksheets. May b i should do that while i create them itself or achieve programmatically.

thanks,
Kedar
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
OK. (your most recent post...) This should loop through all your sheets and unmerge all merged cells in column(s) A & B and convert them to centered across selection.
Code:
Sub UnMergeAndCenter()
Dim sh As Worksheet, mc As Range
For Each sh In Worksheets
    For Each mc In sh.Columns("A:B")
            mc.MergeCells = False
            mc.HorizontalAlignment = xlCenterAcrossSelection
    Next mc
Next sh
End Sub
And in your post above (telling Erik it only performs on the one sheet) you can change the line:
Set rng = Columns("A:B")
to:
Set rng = sh.Columns("A:B")
and it should work.

If you run the unmerge code first, you can change Erik's code back to:
Set rng = sh.Columns(1)

Does that help?
Dan
 

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