Solved: Macro to Sum 2 cells of the Lastrow on another sheet

[JimS]

I have a sheet that can have varying number of rows, always 10 columns though.
I have a macro that sums columns B – J on the sheet.

I need to be able to reference 2 of these sumed columns on another sheet but I’m not sure how to determine which row the totals will be in due to the vary length.

Here are the 6 formulas that I need to populate a table:

=SUM(('Totals IOPS'!Cx)+('Totals IOPS'!Dx*2))
=SUM(('Totals IOPS'!Cx)+('Totals IOPS'!Dx*4))
=SUM(('Totals IOPS'!Fx)+('Totals IOPS'!Gx*2))
=SUM(('Totals IOPS'!Fx)+('Totals IOPS'!Gx*4))
=SUM(('Totals IOPS'!Ix)+('Totals IOPS'!Jx*2))
=SUM(('Totals IOPS'!Ix)+('Totals IOPS'!Jx*4))


Where x is the last row on the “Totals IOPS” sheet.

I have included a sample workbook.

Thanks for any and all help…

Jim

[Bob Phillips]

Public Sub AddFormulae()
Dim LastRow As Long

With Worksheets("Totals IOPS")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

With Worksheets("Results")

.Range("C3").Formula = "=SUM(('Totals IOPS'!C" & LastRow & ")+('Totals IOPS'!D" & LastRow & "*2))"
.Range("C4").Formula = "=SUM(('Totals IOPS'!C" & LastRow & ")+('Totals IOPS'!D" & LastRow & "*4))"
.Range("C5").Formula = "=SUM(('Totals IOPS'!F" & LastRow & ")+('Totals IOPS'!G" & LastRow & "*2))"
.Range("C6").Formula = "=SUM(('Totals IOPS'!F" & LastRow & ")+('Totals IOPS'!G" & LastRow & "*4))"
.Range("C7").Formula = "=SUM(('Totals IOPS'!I" & LastRow & ")+('Totals IOPS'!J" & LastRow & "*2))"
.Range("C8").Formula = "=SUM(('Totals IOPS'!I" & LastRow & ")+('Totals IOPS'!J" & LastRow & "*4))"
End With
End Sub

[Paul_Hossler]

I was not sure if you were looking for a VBA or a worksheet formula since your example sort of looked like a WS soulution.


=2*(INDEX('Totals IOPS'!$A:$J,COUNTA('Totals IOPS'!$A:$A),3)+INDEX('Totals IOPS'!$A:$J,COUNTA('Totals IOPS'!$A:$A),4))


Paul

[rbrhodes]

Hi,

I tweaked the formulas a little so they:

"Need to sum the totals row from the Totals IOPS sheet Columns F & G and muliple it by 2"

As on your example sheet.


Sub AddTotals()
'This macro sums both the columns In columns B:J
'The column sums are placed after the last row.
'The last row is determined by the longest column of data.

Dim LastRow As Long
Dim iCol As Integer
Dim sht As Worksheet
LastRow = 0

Application.ScreenUpdating = False

For Each sht In ActiveWorkbook.Sheets

If Left(sht.Name, 6) = "Totals" Then

LastRow = 0

'Find last row in Columns B:J
For iCol = 2 To 10
iRow = sht.Cells(sht.Rows.Count, iCol).End(xlUp).Row
If iRow > LastRow Then LastRow = iRow
Next iCol

With sht

Range(.Cells(1, 1), .Cells(LastRow, 10)).Sort _
Key1:=.Cells(2, 1), Order1:=xlAscending, Header:=xlYes

'Place column totals in row after current last row
For iCol = 2 To 10

.Cells(LastRow + 1, iCol) = Application.Sum(Range(.Cells(2, iCol), .Cells(LastRow, iCol)))
Next iCol

.Cells(LastRow + 1, 1) = "Totals:"
End With

End If

Next sht

'//ADD ME

'Increment
LastRow = LastRow + 1

With Sheets("Results")
.Range("C3") = "=SUM(('Totals IOPS'!C" & LastRow & "+'Totals IOPS'!D" & LastRow & ")*2)"
.Range("C4") = "=SUM(('Totals IOPS'!C" & LastRow & "+'Totals IOPS'!D" & LastRow & ")*4)"
.Range("C5") = "=SUM(('Totals IOPS'!F" & LastRow & "+'Totals IOPS'!G" & LastRow & ")*2)"
.Range("C6") = "=SUM(('Totals IOPS'!F" & LastRow & "+'Totals IOPS'!G" & LastRow & ")*4)"
.Range("C7") = "=SUM(('Totals IOPS'!I" & LastRow & "+'Totals IOPS'!J" & LastRow & ")*2)"
.Range("C8") = "=SUM(('Totals IOPS'!I" & LastRow & "+'Totals IOPS'!J" & LastRow & ")*4)"
End With

'//END ADD ME


Application.ScreenUpdating = True

End Sub

[JimS]

Thanks to all for your responses.

You all never cease to amaze me with your depth of knowledge.

Jims