Macro creation question

  • Thread starter Thread starter Ted
  • Start date Start date
T

Ted

I have a spreadsheet that has 20 rows of figures that I want to calculate
with a resulting total at the bottom. I can easily create a macro that will
automate this task so I could run it on other spreadsheets. However, the
other spreadsheets (there are hundreds of them) may have any number of rows
to calculate.(The raw data is coming from scanned documents directly into
Excel). Can I have a macro that will do the calculations I want no matter
how many rows there are in the spreadsheet?

All the columns are identical, I just want to automate an otherwise time
consuming job.

Thanks,
Ted
 
Ted,

For example, to put a sum at the bottom on the current column, with your
numbers starting in Row 2

Sub SumAtBottomOfCurrentColumn()
Dim myCell As Range
Set myCell = Cells(65536, ActiveCell.Column).End(xlUp)(2)
With myCell
.Formula = "=SUM(" & _
Range(.Offset(-1, 0), _
Cells(2, .Column)).Address(False, False) & ")"
End With
End Sub

To do columns A through H, you could use

Sub PutSums()
Dim i As Integer
For i = 1 To 8
Cells(1,i).Select
SumAtBottomOfCurrentColumn
End Sub

HTH,
Bernie
MS Excel MVP
 
Back
Top