Data Sum Up Macro

[Rado88]

Hi

I'm trying to improve my macro skills and create a MACRO that would calculate sum from purchase/sales made by a client in different offices. However I've encountered some issues and I'm not sure how to proceed further.

Example - I've a data sheet with client name buy amount sell amount and where they were made. It looks like this (the product names and clients are made up, however the data I'm looking for would always be in those columns):

A

B

C

D

E

F

G

H

I

J

K

L

M

N

Data

Data

Client name

Data

Data

Data

Data

Buy product

BQuantity

Sell product

Sell Quantity

Data

Data

Data

Data

Data

John

xxx

Data

Data

Data

Wood

1000

metal

555

Data

Data

Data

Data

Data

Tim

xxx

Data

Data

Data

Coal

230

Wood

1000

Data

Data

Data

Data

Data

John

xxx

Data

Data

Data

metal

1000

Wood

220

Data

Data

Data

Data

Data

Xyz

xxx

Data

Data

Data

metal

500

Coal

1000

Data

Data

Data

Data

Data

Xyz

xxx

Data

Data

Data

Coal

1000

Wood

1500

Data

Data

Data

Data

Data

Ron

xxx

Data

Data

Data

Wood

2500

Coal

200

Data

Data

Data

Data

Data

Ron

xxx

Data

Data

Data

Wood

1000

metal

40

Data

Data

Data

Data

Data

John

xxx

Data

Data

Data

Paper

111

Wood

67

Data

Data

Data

<tbody>
</tbody>

The above data will change per each trade date.

What I would like to make from this data table, is to creata a button which whne pressed, would copy the above data into new sheet (created in same workbook) and remove duplicates. At the end it should sum up the sales for each product and add the results with product name name near the client. The end product should look like this in sheet(2):

A	B	C
Client name	product	Quantity
John	Wood	1933
John	metal	-555
John	Paper	111
Ron	Wood	3500
Ron	Coal	-200
Ron	metal	-40
Tim	Wood	-1000
Tim	Coal	230
Xyz	Wood	1500
Xyz	Coal	0
Xyz	metal	500

<tbody>
</tbody>

Alternatively the above could be shown in such format

A	B
John (in A1 merged with B1)
Wood	1933
metal	-555
Paper	111

<tbody>
</tbody>

It would be easy to sum up the values using sumifs function in my macro, however I'm not sure how to make VBA create a new table each time when the product names and client names will differ (to add search criteria for sumifs). Excel can loop through the data sheet each time new data file is added, but how to make sure that all the products will be added only once (from buy and sell column) and that nothing will be omitted (not to mention the correct format) ;/

The products and clients may change each time a new file with data is copied into macro sheet.

Would it be possible to add a cell colour in the alternative method mentioned above?

Thank you very much in advance for your help

 

[Fluff]

What line of code gave that error?

 

[Rado88]

There is no line highlited. Only The popup with this error appears on vba sheet when you input The Code. When I Run this macro normally (on Excel sheet) it says only object required

 

[Fluff]

When you get the error click debug, which line of code gets highlighted?

 

[Rado88]

I know how to use the debaug option. Unfortunatelly it's not available ;(

It looks like error with yellow triangle from this page

https://stackoverflow.com/questions...-error-424-object-required-when-deleting-rows

 

[Fluff]

If you step through the code using F8, what happens?

 

[Rado88]

It goes to this line and after pressing f8 again The error appears.

The last highlited line:

v1 = Cl.Value & "," & Cl.Offset(, 2).Value

 

[Rado88]

I believe that I managed to fix this issue. I changed The New string value to x instead of v and it starter to work :s

Im not sure why - maybe there was a typo or sth.

Thank you for all the help Fluff

May I only ask one last question?

Is there any Guide on how to work with such Marcos? I mean if I for example had to again add 1 more collumn to sheet 2?

 

[Fluff]

Glad you got it sorted & thanks for the feedback.

Is there any Guide on how to work with such Marcos?

Not sure there is anything dealing with nested dictionaries, but for normal dictionaries have a look here.