Duplicate row and modify if a certain cell in the row meets condition

[masben]

Hi,

I want a macro to look trough all cells in a column and for all cells that meets a certain condition it should duplicate that entire row and make adjustment to the new row and the original row.

Why? I have a list (product name - source - volume) that I need to go through each month and for some products the source is not 100% Sweden as it sais in the table, but 70% Sweden and 30% Germany. So I manually find these products, duplicate the rows and, for Sweden multiply the volume with 70% and for Germany multiply the volume with 30%. Would love if a macro could do that for me!!

The number of rows in the table changes from month to month.

Please see attched example file.

Many thanks,
Martin

[alansidman]

What is the criteria to know that product B is 70% Swedish and 30% German. What will be the key determinant to discover this for your entire dataset?

[masben]

What is the criteria to know that product B is 70% Swedish and 30% German. What will be the key determinant to discover this for your entire dataset?

Hi!

Thanks for showing intrest in my problem. Sorry I havent replied until now - I have been travelling the last couple of weeks. There is no criteria... Product B is allways 70% Swedisch and 30% German. I just want to duplicate any rows with Product B and split the volume 70/30 on the two new rows.

Thanks
//Martin

[Alf]

Perhaps like this?

Please Login or Register  to view this content.

Macro is based on the layout of your uploaded file i.e. "Product", "Source" and "Volume" assumed to be in columns "D", "E" and "F" and data starts in row 4.

Alf

[masben]

Perhaps like this?

Please Login or Register  to view this content.

Macro is based on the layout of your uploaded file i.e. "Product", "Source" and "Volume" assumed to be in columns "D", "E" and "F" and data starts in row 4.

Alf

Thanks Alf!
This is excatley what I was looking for :-) Reputation added!

Is it possible to modify the code so that more columns are multiplied with 0,7 and 0,3 respectively? I realised that I have two more columns that needs to be changed (col G - cost and col H - revenue) that I didn't include in the example...

Thanks again,
Martin

[Alf]

Yes that should be possible. Assuming earliar layout with the excption that col G an H are added it could be done like this

Please Login or Register  to view this content.

Code could certainly be better "written" but as I'm soon of to Gothenburg I'm a bit stressed for time.

Thanks for feedback and rep.

If this solves you problem don't forget to mark thread "Solved"

Alf

Ps

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

[Alf]

Having tested this macro a bit more I see one problem. There is the row where you sum the totals and as this macro uses "shift:=xlDown" and if D4 = B then the sum range is shifted down one row from row 4 to row 5.

All other insert of rows will not affect the "sum" formula as it will adjust to this automatically. So in order to adjust for the case that D4 = B add the following lines to the macro just after the "Next" line.

Please Login or Register  to view this content.

Alf

[masben]

Having tested this macro a bit more I see one problem. There is the row where you sum the totals and as this macro uses "shift:=xlDown" and if D4 = B then the sum range is shifted down one row from row 4 to row 5.

All other insert of rows will not affect the "sum" formula as it will adjust to this automatically. So in order to adjust for the case that D4 = B add the following lines to the macro just after the "Next" line.

Please Login or Register  to view this content.

Alf

Thanks for all your help Alf!
This macro will save a lot of time for me so I really apriciate it!! TACK!!

Ha det bra!
//Martin

[Alf]

Det e lugnt! Bara kul att kunna hjälpa till lite.

Alf