Link to home
Start Free TrialLog in
Avatar of MasterOfTheSky
MasterOfTheSky

asked on

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
Avatar of simonwait
simonwait
Flag of United Kingdom of Great Britain and Northern Ireland image

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

Would help if I attached it!
Copy-of-order-customers.xls
ASKER CERTIFIED SOLUTION
Avatar of simonwait
simonwait
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MasterOfTheSky
MasterOfTheSky

ASKER

Great Job!! Thank you very much