?
Solved

Excel VBA Question

Posted on 2010-04-05
4
Medium Priority
?
320 Views
Last Modified: 2013-11-25
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
0
Comment
Question by:MasterOfTheSky
  • 3
4 Comments
 
LVL 1

Expert Comment

by:simonwait
ID: 29795483
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
 
LVL 1

Expert Comment

by:simonwait
ID: 29795597
Would help if I attached it!
Copy-of-order-customers.xls
0
 
LVL 1

Accepted Solution

by:
simonwait earned 2000 total points
ID: 29829217
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
 

Author Closing Comment

by:MasterOfTheSky
ID: 31710896
Great Job!! Thank you very much
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question