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]

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

 

[kmyadam]

wow, thats great.... How do i do that

thanks,
Kedar

 

[HalfAce]

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]

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

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]

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]

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.

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

 

[kmyadam]

Thats fantastic,

It works, thanks for Erik and Dan


Kedar