VBA MACRO Sum Total

[INeedHeeelllppp]

What is the code for each column, add the totals and display the column total two rows below the last ow of data?
And for each row, add the total and display the row total two columns to the right f the last column of data?

 

[hiker95]

INeedHeeelllppp,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


So that we can get it right the first time, can we see your data:


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[INeedHeeelllppp]

1. What version of Excel and Windows are you using?

Excel 13 windows 8

2. Are you using a PC or a Mac?
PC

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]NYC[/TD]
[TD]STL[/TD]
[TD]CHI[/TD]
[TD]MIA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Hotels[/TD]
[TD]500[/TD]
[TD]600[/TD]
[TD]700[/TD]
[TD]900[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Meals[/TD]
[TD]200[/TD]
[TD]800[/TD]
[TD]400[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Transportation[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD]200[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Expenses[/TD]
[TD]800[/TD]
[TD]900[/TD]
[TD]600[/TD]
[TD]400[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I need the sum of:
500+200+300+800 where 1
600+800+300+900 where 2
700+400+200+600 where 3
900+600+100+400 where 4
500+600+700+900 where 5
200+800+400+600 where 6
300+200+200+100 where 7
800+900+600+400 where 8

BUT must be written in maro not using =Sum(B2+B3)

 

[hiker95]

INeedHeeelllppp,

The following macro should adjust for the varying number of rows, and, columns.

Sample raw data in the active worksheet:

Excel 2007
	A	B	C	D	E	F	G	H
1		NYC	STL	CHI	MIA
2	Hotels	500	600	700	900
3	Meals	200	800	400	600
4	Transportation	300	300	200	100
5	Expenses	800	900	600	400
6
7
8
9
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H
1		NYC	STL	CHI	MIA			Total
2	Hotels	500	600	700	900			2700
3	Meals	200	800	400	600			2000
4	Transportation	300	300	200	100			900
5	Expenses	800	900	600	400			2700
6
7
8	Total	1800	2600	1900	2000
9
Sheet1

Cell Formulas
Range	Formula
H2	=500+600+700+900
H3	=200+800+400+600
H4	=300+300+200+100
H5	=800+900+600+400
B8	=500+200+300+800
C8	=600+800+300+900
D8	=700+400+200+600
E8	=900+600+100+400

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub GetTotals()
' hiker95, 10/11/2014, ME811068
Dim r As Long, lr As Long, c As Long, lc As Long
Dim v As String, h As String
Application.ScreenUpdating = False
lr = Cells(2, 1).End(xlDown).Row
lc = Cells(1, 2).End(xlToRight).Column
Range(Cells(1, lc + 3), Cells(lr + 3, lc + 3)).ClearContents
Range(Cells(lr + 3, 1), Cells(lr + 3, lc + 3)).ClearContents
Cells(1, lc + 3).Value = "Total"
Cells(lr + 3, 1).Value = "Total"
v = ""
For c = 2 To lc
  For r = 2 To lr
    If r = 2 Then
      v = "=" & Cells(r, c).Value & "+"
    Else
      v = v & Cells(r, c).Value & "+"
    End If
  Next r
  If Right(v, 1) = "+" Then v = Left(v, Len(v) - 1)
  Cells(lr + 3, c) = v
  v = ""
Next c
h = ""
For r = 2 To lr
  For c = 2 To lc
    If c = 2 Then
      h = "=" & Cells(r, c).Value & "+"
    Else
      h = h & Cells(r, c).Value & "+"
    End If
  Next c
  If Right(h, 1) = "+" Then h = Left(h, Len(h) - 1)
  Cells(r, lc + 3) = h
  h = ""
Next r
Columns(1).Resize(, lc + 3).AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetTotals macro.

 

[INeedHeeelllppp]

Works perfect thank you!!

 

[Peter_SSs]

Does this more compact alternative also do the job?

Sub Add_Totals()
  Dim rc As Range
  
  With Range("A1").CurrentRegion
    For Each rc In .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1).Rows
      rc.Offset(, rc.Columns.Count + 2).Resize(, 1).Formula = "=" & Join(Application.Index(rc.Value, 1, 0), "+")
    Next rc
    For Each rc In .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1).Columns
      rc.Offset(rc.Rows.Count + 2).Resize(1).Formula = "=" & Join(Application.Transpose(rc.Value), "+")
    Next rc
    Union(.Cells(1, .Columns.Count + 3), .Cells(.Rows.Count + 3, 1)).Value = "Total"
  End With
End Sub

 

[hiker95]

INeedHeeelllppp,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.