Dividing one cell with another cell in a macro

[pincivma]

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

 

[Akuini]

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.

Could you show us the macro?

 

[pincivma]

Could you show us the 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

 

[mohadin]

HI
I suggest

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

 

[pincivma]

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 mohadin

Thanks for the code. I will give it a try today and see if it worked for me.

 

[Fluff]

Another option

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

This will put the formulae into I, K & L