# Thread: Dividing one cell with another cell in a macro Thanks: 0 Likes: 0

1. ## Dividing one cell with another cell in 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

2. ## Re: Dividing one cell with another cell in a 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.
Could you show us the macro?

3. ## Re: Dividing one cell with another cell in a macro

Originally Posted by Akuini
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

4. ## Re: Dividing one cell with another cell in a macro

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```

5. ## Re: Dividing one cell with another cell in a macro

Originally Posted by 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

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

6. ## Re: Dividing one cell with another cell in a macro

Another option
Code:
```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