vba code for subtotals and percentages

  • Thread starter Thread starter pcscsr
  • Start date Start date
P

pcscsr

I have the following code that when the user clicks on the command
button the selected items are transferred to a separte sheet along with
the calculations. The first part (transferring the selected items)
works great. However, I get a application-defined or object defined
error in the 2nd part. (where ".formula" starts). Is there anyone
that can help me with this? I'd a appreciate greatly as I am under a
deadline. Thank you.

Private Sub CommandButton1_Click()
Dim i As Integer, x As Integer
Dim n As Integer, r As Range
i = 2
Sheets("sheet2").Range("a:d").EntireColumn.ClearContents
With ListBox1
For x = 0 To .ListCount - 1
If .Selected(x) = True Then
For j = 1 To .ColumnCount
Sheets("sheet2").Cells(i, j).Value = .List(x, j - 1)
Next
i = i + 1
End If
Next x
End With

n = 10
Set r = Range("d2:d" & n)
r.Value = 2
n = n + 1
Cells(n, 2) = "subtotal"
Cells(n, 3).FormulaR1C1 = "=sum(r2d:r[-1]d)"
n = n + 1
Cells(n, 2) = "'+6%"
Cells(n, 3).FormulaR1C1 = "=.6*r[-1]d"
n = n + 1
Cells(n, 2) = "subtotal"
Cells(n, 3).FormulaR1C1 = "=r[-2]d + r[-1]d"
n = n + 1
Cells(n, 2) = "' + 35%"
Cells(n, 3).FormulaR1C1 = "=.35*r[-1]d"
n = n + 1
Cells(n, 2) = "subtotal"
Cells(n, 3).FormulaR1C1 = "=r[-2]d + r[-1]d"
n = n + 1
Cells(n, 2) = "' + 10%"
Cells(n, 3).FormulaR1C1 = "=.1*r[-1]d"
n = n + 1
Cells(n, 2) = "Grand total"
Cells(n, 3).FormulaR1C1 = "=r[-2]d + r[-1]d"

UserForm1.Hide
End Sub
 
Since you are using your local format for R1C1 notation, you need to

Change FormulaR1C1 to FormulaR1C1Local

otherwise, you can keep FormulaR1C1 and change your "d" 's to "c" 's
 
Back
Top