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
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