scb998
03-04-2013, 10:09 PM
Hello All,
I am trying to create a "user friendly" invoice for a mates business. It relies on the user entering several paramaters (name, address etc) and putting them in a pre-formatted worksheet (see attached document). the user will enter the number of items being ordered, and then the following procedure will allow the user to select the respective objects and quantity:
Private Sub CommandButton1_Click()
Dim formula As String
Dim ws As Worksheet
Set ws = Worksheets("Sheet4")
row = 21
col = 1
On Error Resume Next
Do
If Items_entered <> Items Then
quantity = ComboBox2.Value
prod_desc = ComboBox1.Value
Cells(row, col).Value = quantity
col = col + 1
Cells(row, col).Value = prod_desc
Cells(row, col).Select
varlookup = ActiveCell.Address
col = col + 1
Cells(row, col).Select
Cells(row, col).Value = Application.WorksheetFunction.VLookup(prod_desc, Worksheets("Sheet4").Range(a2, b50), 2, False) '<<<<<< Here is where im getting my error
col = col + 1
Cells(row, col).Value = Cells(row, col - 1).Value * quantity
UserForm1.Hide
MsgBox ("item Added to Invoice")
row = row + 1
col = 1
Items_entered = Items_entered + 1
End If
UserForm1.Show
Loop Until Items_entered = Items
End Sub
i am having trouble with putting a Vlookup formula into a cell. im getting the "application or object based" runtime error.
if anyine could shed some light on where i am going wrong, and a possible solution, i would be extremely greatful!!!
I am trying to create a "user friendly" invoice for a mates business. It relies on the user entering several paramaters (name, address etc) and putting them in a pre-formatted worksheet (see attached document). the user will enter the number of items being ordered, and then the following procedure will allow the user to select the respective objects and quantity:
Private Sub CommandButton1_Click()
Dim formula As String
Dim ws As Worksheet
Set ws = Worksheets("Sheet4")
row = 21
col = 1
On Error Resume Next
Do
If Items_entered <> Items Then
quantity = ComboBox2.Value
prod_desc = ComboBox1.Value
Cells(row, col).Value = quantity
col = col + 1
Cells(row, col).Value = prod_desc
Cells(row, col).Select
varlookup = ActiveCell.Address
col = col + 1
Cells(row, col).Select
Cells(row, col).Value = Application.WorksheetFunction.VLookup(prod_desc, Worksheets("Sheet4").Range(a2, b50), 2, False) '<<<<<< Here is where im getting my error
col = col + 1
Cells(row, col).Value = Cells(row, col - 1).Value * quantity
UserForm1.Hide
MsgBox ("item Added to Invoice")
row = row + 1
col = 1
Items_entered = Items_entered + 1
End If
UserForm1.Show
Loop Until Items_entered = Items
End Sub
i am having trouble with putting a Vlookup formula into a cell. im getting the "application or object based" runtime error.
if anyine could shed some light on where i am going wrong, and a possible solution, i would be extremely greatful!!!