Could you show us the macro?I have column K full of numbers and column I full of numbers. I then have totals of all the numbers in both columns. These totals are summed by a macro.
I know that dividing one cell with another cell in a macro is very easy to do. However here is my problem. I have column K full of numbers and column I full of numbers. I then have totals of all the numbers in both columns. These totals are summed by a macro. It doesn't matter if I add more rows with numbers, when I run the macro, the sum always shows up. So now I want to divide the sum in column K by the sum in column I by a macro so that if I add more rows in both columns, when I run the macro, the sum in column K gets divided by the sum in column I. I want the result in column L. Any help would be greatly appreciated.
Thanks
Could you show us the macro?I have column K full of numbers and column I full of numbers. I then have totals of all the numbers in both columns. These totals are summed by a macro.
Here is the macro that sums Column K. The macro that sums column I has the same format.
Sub SumsAtBottomColumnK()
On Error Resume Next
Range("B4").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "TOTALS"
ActiveCell.Select
ActiveCell.Offset(O, 9).Select
'This line tells you what row you want the result in
'This row tells you the row that you are starting at.
vRowTop = 4
'This row also tells you the row that you are starting at.
vRowBottom = ActiveCell.Offset(-1, 0).Row
vDiff = vRowBottom - vRowTop + 1
'Note: the R[-*]C in the row below must match the ActiveCell.Offset(*, 0).Range("A1") above
Selection.FormulaR1C1 = "=SUM(R[" & -vDiff & "]C:R[-2]C)"
ActiveCell.Select
Selection.NumberFormat = "$#,##0.00"
End Sub
HI
I suggest
Code:Sub SUManDIV() Set res = Union([L:L], [b:b]) res.ClearContents vROWTOP = 4 Set rng = Range("B" & vROWTOP) ai = Application.Transpose(Range("I4:I" & Cells(Rows.Count, 9).End(xlUp).Row)) ak = Application.Transpose(Range("K4:K" & Cells(Rows.Count, 11).End(xlUp).Row)) rng.Offset(UBound(ak)) = "TOTALS" rng.Offset(UBound(ak), 10) = Application.Sum(ak) / Application.Sum(ai) rng.Offset(UBound(ak), 10).NumberFormat = "$#,##0.00" End Sub
Another optionThis will put the formulae into I, K & LCode:Sub pincivma() With Range("B" & Rows.Count).End(xlUp).Offset(2) .Value = "TOTALS" .Offset(, 7).FormulaR1C1 = "=sum(r4c:r[-2]c)" .Offset(, 9).FormulaR1C1 = "=sum(r4c:r[-2]c)" .Offset(, 10).FormulaR1C1 = "=rc[-3]/rc[-1]" .Offset(, 7).Resize(, 4).NumberFormat = "$#,##0.00" End With End Sub
- Posting Data try one of these tools
- Posting guidelines, forum rules and terms of use
- Read the FAQs
Running Office 365 on Win 10
Like this thread? Share it with others