# Thread: USing VBA to Calculate Formula (2007)

1. ## 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

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

2. ## 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. ## 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. ## 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. ## 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
•