Results 1 to 6 of 6

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

  1. #1
    Board Regular
    Join Date
    Dec 2004
    Posts
    165
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,050
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Dec 2004
    Posts
    165
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dividing one cell with another cell in a macro

    Quote Originally Posted by Akuini View Post
    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. #4
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    256
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular
    Join Date
    Dec 2004
    Posts
    165
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dividing one cell with another cell in a macro

    Quote Originally Posted by pincivma View Post
    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.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,133
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default 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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •