Advanced users please look! - MACRO help Needed!

[pauldaddyadams]

Hi All,

Thank you for looking at my problem, hope you can help!!!

What I need to do…..

From the table below the first column shows the product number
(2001079, 2001196 and 2001219), in the end column shows how many times
the product has been sold (-1, -2, -2 etc) Underneath is an empty box
which I need subtotalled. I need a way which can subtotal all the
products and then list the products on a different page, only showing
the product number and the total sold.

E.g. the example would be:
2001079 3
2001196 6
2001219 27

But this is easier said than done! Im told I need a macro created or
maybe a relationship formula set up but I am unable to create such a
complex formula.

CAN ANYONE PLEASE HELP?


2001079 FOP 5.0 CL G 3210x2250 LS 22
201 GI for cost ce 1 -1
201 GI for cost ce 1 -2

2001196 FOP 6.0 BZ G 3210x2250 LS 18
201 GI for cost ce 1 -2
201 GI for cost ce 1 -1
201 GI for cost ce 1 -1
201 GI for cost ce 1 -1
201 GI for cost ce 1 -1

2001219 -27 FOP 6.0 CL G 2100x1100 LS 35
201 GI for cost ce 1 -4
201 GI for cost ce 1 -4
201 GI for cost ce 1 -4
201 GI for cost ce 1 -3
201 GI for cost ce 1 -2
201 GI for cost ce 1 -2
201 GI for cost ce 1 -6
201 GI for cost ce 1 -2

 

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim sCode
Dim nAmount

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
sCode = Range("A1").Value
nAmount = 0
For i = 2 To iLastRow + 1
If Cells(i, "A").Value = "" Then
Cells(i, "A").Value = sCode
Cells(i, "G").Value = nAmount
nAmount = 0
sCode = Cells(i + 1, "A").Value
i = i + 1 ' skip next line
Else
nAmount = nAmount - Cells(i, "G").Value
End If
Next i

End Sub


--
HTH

Bob Phillips

"pauldaddyadams"

 

[pauldaddyadams]

Thank you for getting back to me!!

I am not sure how to act on your reply, is that a VBA script or macro?
How do i go about executing what you said to try?

Paul

 

[STEVE BELL]

Check out Pivot Tables. They do a nice job of this.

Also - you can record what you are doing and find a way for
Excel to automatically make or adjust a Pivot Table.

 

[Bob Phillips]

Go to the VBE (ALt-F11), create a code module (Insert>Module), copy the code
in, go back to Excel, and run the macro (Tools>Macro>Macros..., select the
macro and Run).

--
HTH

Bob Phillips

"pauldaddyadams"