Adding a total column programatically 1

[meumax]

I'm an Access VBA programmer but have recently been asked to write Excel macros and I am lost. If I have the following data in a worksheet:

A B C
1 #1 #2 Tot
2 10 40
3 49 45
4 36 10
5 75 23

How can I write a macro that will 1. add the "tot" column to the worksheet and then add a formula to add the two numbers in the same row such that the result looks like

A B C
1 #1 #2 Tot
2 10 40 50
3 49 45 94
4 36 10 46
5 75 23 98

The macro will need to work no matter how many rows there are since the data is constantly changing. If someone could point me in the right direction I would be most grateful.

 

[rmikesmith]

meumax,

This should do it:

Sub AddTotalColumn()
Dim LastUsedRow As Long
Dim i As Long

  Application.ScreenUpdating = False
  With ActiveSheet
    .Cells(1, 3).Value = "Tot"
    LastUsedRow = .Cells(65536, 1).End(xlUp).Row
    For i = 2 To LastUsedRow
      .Cells(i, 3).Formula = "=A" & i & "+B" & i
    Next i
  End With
  Application.ScreenUpdating = True

End Sub

With your background in VBA, once you learn Excel's object model, you shouldn't have any trouble.

Regards,
Mike

 

[Zathras]

This will solve the problem as stated, plus allowing for more than 2 columns to be summed if needed. If there are any other complications, it may need revision:


Option Explicit

Sub WriteTotals()
'Sample macro to demo WriteTotalsInColumn macro
WriteTotalsInColumn "C1"

End Sub

Sub WriteTotalsInColumn(TotCol As String)
'Macro to generate a column of row totals
'TotalColumn should be the top cell in the column - will have "Tot"
Dim i As Double
Dim TotalColumn As Range
Dim ColumnsToSum As Integer
Set TotalColumn = Range(TotCol)
ColumnsToSum = 2 ' Change here if more than 2 columns needed to sum
TotalColumn.Value = "Tot"
i = 1
While TotalColumn.Offset(i, -1).Value <> &quot;&quot;
TotalColumn.Offset(i, 0).FormulaR1C1 = &quot;=SUM(RC[-&quot; + Trim(ColumnsToSum) + &quot;]:RC[-1])&quot;
i = i + 1
Wend
Set TotalColumn = Nothing
End Sub

 

[meumax]

Thanks Mike,

The idea of xlUping from the 65000th row and looping to it from row 2 was just the clue I needed to make my macro work.