# Thread: Help with Macro/Function

1. ## Help with Macro/Function

I am hoping that I can get help with this situation. I am exporting information from an AS400 database into Excel. The report I am trying to generate is a subtotal of all orders received from a date range. The way the information is extracted, it gives line items on all orders. The problem this creates is that I have 40,000 lines from 4000 orders. Is there a way to take the line items on the same orders and combine them to see just the total for that particular order? If I am not making myself clear, I will try to show below what the output actually looks like. I would really appreciate the help! I hate to print out 200 pages when I can do it in 50.
Here's the way it looks:
ORDER NO. QUANTITY PRICE EXTENDED
1234 5 1.00 5.00
1234 10 2.00 20.00
1234 20 0.10 2.00
1234 1 10.00 10.00
2345 20 1.00 20.00
2345 5 1.00 5.00
----------------------------------------
The report goes on and on similar to this. Can I compare the first column and then run a total of the extended price column or is this something that I should do in Access or another database application?
THANK YOU FOR ANY HELP!!!!!!

2. ## Re: Help with Macro/Function

This is the sort of thing I would do in Access, but it does sound like something you could do with Datasubtotal function which will provide a subtotal for each change in a defined column, so every time the code changes the orders total would be calculated, you would have to sort the data by order number first

3. ## Re: Help with Macro/Function

Thanks, I will try to create something with that function. Now, something else that I want to do is if I get the total to erase the lines that are listed on the same order.
Thanks again!

4. ## Re: Help with Macro/Function

Hi,

I tend to like VBA solutions for this sort of thing- perhaps because I don't know as much about Excel as I should- but I also find that at times, I've got so much more control over things that I should have started with VBA in the first place.

Anyway,

Here's what I've come up with. It assumes your data is in Sheet1, and the summary data will be put into Sheet2. It assumes that processing will stop at the first blank cell.

<pre>Option Explicit

Sub Macro1()
Dim i As Long
Dim j As Long
Dim curTotal1 As Currency
Dim curTotal2 As Currency
Dim curTotal3 As Currency
Dim prevAcc As String

i = 1
j = 1
Sheets("Sheet1").Activate
prevAcc = Cells(1, 1)

Do Until Cells(i, 1) = ""
If Cells(i, 1) <> prevAcc Then
With Sheets("Sheet2")
.Cells(j, 1) = prevAcc
.Cells(j, 2) = curTotal1
.Cells(j, 3) = curTotal2
.Cells(j, 4) = curTotal3
j = j + 1
End With
curTotal1 = 0
curTotal2 = 0
curTotal3 = 0

End If
curTotal1 = curTotal1 + Cells(i, 2)
curTotal2 = curTotal2 + Cells(i, 3)
curTotal3 = curTotal3 + Cells(i, 4)
prevAcc = Cells(i, 1)
i = i + 1
Loop
With Sheets("Sheet2")
.Cells(j, 1) = prevAcc
.Cells(j, 2) = curTotal1
.Cells(j, 3) = curTotal2
.Cells(j, 4) = curTotal3
End With

End Sub
</pre>

5. ## Re: Help with Macro/Function

Since I could not figure out any reason why you would want to subtotal the number of items or the unit price of each, I have posted a modification of Geoff's otherwise excellent code below. I have also added one additional feature. My code will clear Sheet2 before it starts inserting the subtotals, and it starts the subtotals in row 2. This way, you can put column headers in row 1, and apply whatever formatting you want to the columns and keep reusing this sheet every time you need to do this.

<pre>Public Sub Macro1()
Dim i As Long
Dim j As Long
Dim curTotal As Currency
Dim prevAcc As String
i = 1
j = 2
Sheets("Sheet1").Activate
prevAcc = Cells(1, 1)
With Sheets("Sheet2")
Range("A2:B16000").ClearContents
End With
Do Until Cells(i, 1) = ""
If Cells(i, 1) <> prevAcc Then
With Sheets("Sheet2")
.Cells(j, 1) = prevAcc
.Cells(j, 2) = curTotal
j = j + 1
End With
curTotal = 0
End If
curTotal = curTotal1 + Cells(i, 4)
prevAcc = Cells(i, 1)
i = i + 1
Loop
With Sheets("Sheet2")
.Cells(j, 1) = prevAcc
.Cells(j, 2) = curTotal
End With
End Sub
</pre>

6. ## Re: Help with Macro/Function

Thank you both for the solutions!
I have a quick question.... How can I learn more about doing these types of functions?

7. ## Re: Help with Macro/Function

One good way is to frequent places like this. Study and understand the code you see posted. Ask questions. There are also a lot of books availble that might help.

8. ## Re: Help with Macro/Function

Another way is by doing it. Try to develop your own code solutions then ask for help with the bits which do not work.

And, above all, have fun.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•