Excel VBA Question

In the Order Customer file, The A sheet in the file contain prices per unit for all the software packages a mail-order company sells.  There is also an B sheet.  When the company gets and order from a customer, the person receiving the order gets the quantity of type of product the customer wants to purchase, customer's name and the date.  In the A sheet column C, the quantities are written there.  Information in the A sheet is used to create invoices for the customer on the B sheet.  The Current B sheet is a general invoice "template".  

I am trying to write two subs, one that will "ClearData and one that will InvoiceCreation, and attach the buttons at the top of the A sheet. These buttons should do the following.

A. The ClearData sub must be able to clear any quantities from past order from column C of the A sheet.  It must be able to delete any old data from the B sheet from row 5 down.

B. The InvoiceCreation sub should begin running immediately after the person taking the order has received the customer information and has entered the quantities in column C of the A sheet.  This Sub should utilize input boxes to ask for the customers's name and the data, and it should use these to complete the labels in cells A1 & A2 of the B sheet.  Only the relevant data about the products ordered should be transfer to the B sheet, the price for each product ordered (quantity ordered times unit price) should be calculated, it should also calculate the tax for the order (5% sales tax) and the final cost of the order, including tax, in column D. below the prices of individual products, with right labels in column C (such as "5% sales tax" and "Total Cost").

c. To add the finishing touch, add some code to the InvoiceCreation sub to Print the finished invoice.
order-customers.xlsx
MasterOfTheSkyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

simonwaitCommented:
How about the following.  In the second sub, you can change the sales tax rate at the start should it ever change.  Also see the saved sheet.
Sub ClearDataButton_Click()
lastrowa = Sheets("A").UsedRange.Rows.Count
Sheets("A").Range("C4:C" & lastrowa).ClearContents
lastrowb = Sheets("B").UsedRange.Rows.Count
Sheets("B").Range("A5:D" & lastrowb).ClearContents
Sheets("B").Range("A1").Value = "Customer Name:"
Sheets("B").Range("A2").Value = "Purchase Date:"

End Sub
Sub CreateInvoice_Click()

salestax = 5 '% tax rate
st = salestax / 100

Sheets("B").Range("A1").Value = "Customer Name: " & InputBox("Please Enter the customers name.")
Sheets("B").Range("A2").Value = "Purchase Date: " & InputBox("Please Enter the purchase date.", , Date)
lastrowb = Sheets("B").UsedRange.Rows.Count
Sheets("A").Activate
lastrowa = Sheets("A").UsedRange.Rows.Count
If lastrowb = Sheets("B").UsedRange.Rows.Count > 4 Then
lastrowb = Sheets("B").UsedRange.Rows.Count
Else
lastrowb = 5
End If
For Each acell In Range("C4:C" & lastrowa)
If acell <> "" Then

Sheets("B").Range("A" & lastrowb).Value = Sheets("A").Range("A" & acell.Row).Value
Sheets("B").Range("B" & lastrowb).Value = Sheets("A").Range("B" & acell.Row).Value
Sheets("B").Range("C" & lastrowb).Value = Sheets("A").Range("C" & acell.Row).Value
Sheets("B").Range("D" & lastrowb).FormulaR1C1 = "=RC[-2]*RC[-1]"
lastrowb = lastrowb + 1
Else
End If
Next acell
Sheets("B").Range("C" & lastrowb + 1).Value = "Sub Total"
Sheets("B").Range("C" & lastrowb + 2).Value = salestax & "% Sales Tax"
Sheets("B").Range("C" & lastrowb + 3).Value = "Total"

Sheets("B").Range("D" & lastrowb + 1).Formula = "=SUM(D5:D" & lastrowb - 1 & ")"
Sheets("B").Range("D" & lastrowb + 2).FormulaR1C1 = "=R[-1]C*" & st & ""
Sheets("B").Range("D" & lastrowb + 3).FormulaR1C1 = "=R[-2]C+R[-1]C"


End Sub

Open in new window

0
simonwaitCommented:
Would help if I attached it!
Copy-of-order-customers.xls
0
simonwaitCommented:
Forgot to add the printout and definitions

Sub ClearDataButton_Click()
Dim lastrowa As Integer
Dim lastrowb As Integer

lastrowa = Sheets("A").UsedRange.Rows.Count
Sheets("A").Range("C4:C" & lastrowa).ClearContents
lastrowb = Sheets("B").UsedRange.Rows.Count
Sheets("B").Range("A5:D" & lastrowb).ClearContents
Sheets("B").Range("A1").Value = "Customer Name:"
Sheets("B").Range("A2").Value = "Purchase Date:"

End Sub
Sub CreateInvoice_Click()
Dim salestax As String
Dim st As String
Dim lastrowb As Integer
Dim lastrowa As Integer
Dim acell As Range


salestax = 5 '% tax rate
st = salestax / 100

Sheets("B").Range("A1").Value = "Customer Name: " & InputBox("Please Enter the customers name.")
Sheets("B").Range("A2").Value = "Purchase Date: " & InputBox("Please Enter the purchase date.", , Date)
lastrowb = Sheets("B").UsedRange.Rows.Count
Sheets("A").Activate
lastrowa = Sheets("A").UsedRange.Rows.Count
If lastrowb = Sheets("B").UsedRange.Rows.Count > 4 Then
lastrowb = Sheets("B").UsedRange.Rows.Count
Else
lastrowb = 5
End If
For Each acell In Range("C4:C" & lastrowa)
If acell <> "" Then

Sheets("B").Range("A" & lastrowb).Value = Sheets("A").Range("A" & acell.Row).Value
Sheets("B").Range("B" & lastrowb).Value = Sheets("A").Range("B" & acell.Row).Value
Sheets("B").Range("C" & lastrowb).Value = Sheets("A").Range("C" & acell.Row).Value
Sheets("B").Range("D" & lastrowb).FormulaR1C1 = "=RC[-2]*RC[-1]"
lastrowb = lastrowb + 1
Else
End If
Next acell
Sheets("B").Range("C" & lastrowb + 1).Value = "Sub Total"
Sheets("B").Range("C" & lastrowb + 2).Value = salestax & "% Sales Tax"
Sheets("B").Range("C" & lastrowb + 3).Value = "Total"

Sheets("B").Range("D" & lastrowb + 1).Formula = "=SUM(D5:D" & lastrowb - 1 & ")"
Sheets("B").Range("D" & lastrowb + 2).FormulaR1C1 = "=R[-1]C*" & st & ""
Sheets("B").Range("D" & lastrowb + 3).FormulaR1C1 = "=R[-2]C+R[-1]C"

Sheets("B").PrintOut Copies:=1

End Sub

Open in new window

Copy-of-order-customers.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MasterOfTheSkyAuthor Commented:
Great Job!! Thank you very much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.