Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    USing VBA to Calculate Formula (2007)

    I have VBA code which calculates the totals three rows below the imported data using the following code and this is calculated correctly-see code & sample data below

    Sub Add_Totals()

    Finalrow = Range("A65536").End(xlUp).Row - 1
    Range("A" & Finalrow).ClearContents
    Range("B" & Finalrow).ClearContents
    Range("C" & Finalrow).ClearContents
    Range("D" & Finalrow).ClearContents
    Range("E" & Finalrow).ClearContents
    Range("F" & Finalrow).ClearContents
    Finalrow = Range("A65536").End(xlUp).Row
    Range("A" & Finalrow).ClearContents
    Range("B" & Finalrow).ClearContents
    Finalrow = Range("A65536").End(xlUp).Row + 2
    Range("A" & Finalrow + 2).Value = "Total"
    Range("B" & Finalrow + 2).Formula = "=sum(B6:B" & Finalrow & ")"
    Range("C" & Finalrow + 2).Formula = "=sum(C6:C" & Finalrow & ")"
    Range("D" & Finalrow + 2).Formula = "=sum(D6" & Finalrow & ")"
    Range("E" & Finalrow + 2).Formula = "=sum(E6:E" & Finalrow & ")"
    Range("F" & Finalrow + 2).Formula = "=sum(F6:F" & Finalrow & ")"
    Finalrow = Range("A65536").End(xlUp).Row
    For I = 2 To Finalrow
    Cells(I, 2).NumberFormat = "#,##0.00;(#,##0.00)"
    Cells(I, 3).NumberFormat = "#,##0.00;(#,##0.00)"
    Cells(I, 4).NumberFormat = "#,##0.00;(#,##0.00)"
    Cells(I, 5).NumberFormat = "#,##0.00;(#,##0.00)"
    Cells(I, 6).NumberFormat = "#,##0.00;(#,##0.00)"
    Next I
    End Sub

    I now need calculate the Markdown values in column D:F. The values in line with the word "Total" from col D onwards is multiplied by the percentages in in column . The total in Col D is multiplied by the % in K4 and shown in line with word Markdown in Col D, The total in Col E is multiplied by the % in K5 and shown in line with Markdown in Col E etc

    I have attached sample data

    I need VBA code to do this to calculate the markdown value .See My Code below, which need to be amended. The Data is imported, so the range changes as the dasta changes

    Totalrow = Cells(65536, 1).End(xlUp).Row + 3
    Cells(Totalrow + 3, 1).Value = "Markdown"
    Cells(Totalrow + 3, 3).FormulaR1C1 = "=+R[-6]C*R[-27]C[8]"
    Cells(Totalrow + 3, 3).NumberFormat = "#,##0.00;(#,##0.00)"
    Cells(Totalrow + 3, 4).FormulaR1C1 = "=+R[-6]C*R[-26]C[7]"
    Cells(Totalrow + 3, 4).NumberFormat = "#,##0.00;(#,##0.00)"
    Cells(Totalrow + 3, 5).FormulaR1C1 = "=+R[-6]C*R[-25]C[6]"
    Cells(Totalrow + 3, 5).NumberFormat = "#,##0.00;(#,##0.00)"
    Cells(Totalrow + 3, 6).FormulaR1C1 = "=+R[-6]C*R[-24]C[5]"
    Cells(Totalrow + 3, 6).NumberFormat = "#,##0.00;(#,##0.00)"
    Totalrow = Cells(65536, 1).End(xlUp).Row
    Cells(Totalrow + 3, 1).Value = "Total Markdown"
    Cells(Totalrow + 3, 2).Formula = "=SUM(R[-3]C[1]:R[-3]C[4])"
    Cells(Totalrow + 3, 2).NumberFormat = "#,##0.00;(#,##0.00)'


    Your assistance in this regard will be most appreciated

    Regards

    Howard
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: USing VBA to Calculate Formula (2007)

    Does this do what you want?

    Sub Calc_Markdown()
    Dim TotalRow As Long
    Dim c As Integer
    TotalRow = Cells(65536, 1).End(xlUp).Row
    Cells(TotalRow + 3, 1).Value = "Markdown"
    For c = 3 To 6
    Cells(TotalRow + 3, c).FormulaR1C1 = "=R" & TotalRow & _
    "C" & c & "*R" & c & "C11"
    Cells(TotalRow + 3, c).NumberFormat = "#,##0.00;(#,##0.00)"
    Next c
    End Sub

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: USing VBA to Calculate Formula (2007)

    Hi Hans

    Thanks for the help, code works perfectly.

    What does "R" in the code below represent /mean?

    (TotalRow + 3, c).FormulaR1C1 = "=R" & TotalRow & _
    "C" & c & "*R" & c & "C11"

    Regards

    Howard

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: USing VBA to Calculate Formula (2007)

    The code sets the FormulaR1C1 property of the cell; this is the formula in R1C1 notation. R stands for row and C for column, so for example R5C4 is the cell in row 5 and column 4, i.e. cell D5.

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: USing VBA to Calculate Formula (2007)

    Hi Hans

    Thanks for the explanation

    Regards

    Howard

Posting Permissions

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