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):
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Client name[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Buy product[/TD]
[TD]BQuantity[/TD]
[TD]Sell product[/TD]
[TD]Sell Quantity[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]John[/TD]
[TD]xxx[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Wood[/TD]
[TD]1000[/TD]
[TD]metal[/TD]
[TD]555[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Tim[/TD]
[TD]xxx[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Coal[/TD]
[TD]230[/TD]
[TD]Wood[/TD]
[TD]1000[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]John[/TD]
[TD]xxx[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]metal[/TD]
[TD]1000[/TD]
[TD]Wood[/TD]
[TD]220[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Xyz[/TD]
[TD]xxx[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]metal[/TD]
[TD]500[/TD]
[TD]Coal[/TD]
[TD]1000[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Xyz[/TD]
[TD]xxx[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Coal[/TD]
[TD]1000[/TD]
[TD]Wood[/TD]
[TD]1500[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Ron[/TD]
[TD]xxx[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Wood[/TD]
[TD]2500[/TD]
[TD]Coal[/TD]
[TD]200[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Ron[/TD]
[TD]xxx[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Wood[/TD]
[TD]1000[/TD]
[TD]metal[/TD]
[TD]40[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]John[/TD]
[TD]xxx[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Paper[/TD]
[TD]111[/TD]
[TD]Wood[/TD]
[TD]67[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
</tbody>[/TABLE]




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):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Client name[/TD]
[TD]product[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Wood[/TD]
[TD]1933[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]metal[/TD]
[TD]-555[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Paper[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]Wood[/TD]
[TD]3500[/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]Coal[/TD]
[TD]-200[/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]metal[/TD]
[TD]-40[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]Wood[/TD]
[TD]-1000[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]Coal[/TD]
[TD]230[/TD]
[/TR]
[TR]
[TD]Xyz[/TD]
[TD]Wood[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]Xyz[/TD]
[TD]Coal[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Xyz[/TD]
[TD]metal[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]

Alternatively the above could be shown in such format

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]John (in A1 merged with B1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wood[/TD]
[TD]1933[/TD]
[/TR]
[TR]
[TD]metal[/TD]
[TD]-555[/TD]
[/TR]
[TR]
[TD]Paper[/TD]
[TD]111[/TD]
[/TR]
</tbody>[/TABLE]

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.