vba code for subtotals and percentages
 

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.ClearCo ntents
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


--
pcscsr
------------------------------------------------------------------------
pcscsr's Profile: http://www.excelforum.com/member.php...o&userid=12006
View this thread: http://www.excelforum.com/showthread...hreadid=274838

vba code for subtotals and percentages
 

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

--
Regards,
Tom Ogilvy


"pcscsr" wrote in message
...

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.ClearCo ntents
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


--
pcscsr
------------------------------------------------------------------------
pcscsr's Profile:
http://www.excelforum.com/member.php...o&userid=12006
View this thread: http://www.excelforum.com/showthread...hreadid=274838