Split into 2 rows IF - VBA

[wrightyrx7]

Hi all,

I am putting together a macro that takes hours worth of work away by doing all the calculations and splitting.

I have managed to do all the basic stuff but this is a little more complicated.

My spreadsheet has a couple of thousand rows of data, i need the macro to check for the following:-
If DATA14 (Column N) = YES and RATE (Column D) = 21.81

I need this line splitting into two to replace the original. (would be better if i could move the original data to another sheet to show the ones that have been split)

One with 87% of the rate
One with 13% of the rate (Data8 will always = OLHA on this row)

Then the total will be recalculated based on the new rate.

Please see screenshot below.

If you need any more information please let me know.





Thanks in advance
Chris

[p45cal]

Could you attach a file?

[wrightyrx7]

Hi P45cal

Thank you for your reply.

Could you attach a file?

Unfortunately I only have the file where the image is attached. This only contains the TEST data you can see in the image.

The real data is at work. is there anything else i can do?

[p45cal]

Unfortunately I only have the file where the image is attached. This only contains the TEST data you can see in the image.
is there anything else i can do?

Well that file would have been a start - it would save me from creating my own version of your file from the screenshot, not knowing what columns/rows are involved, assumptions about the values in cells (are the values as seen or formatted to n decimal places?) and so on. It would save me making mistakes, save us both time, give me something to experiment on.

I have created such a file (attached). There are two sheets,
one called Sheet1 which should be left alone (no pressing the buttons on that sheet) and…
another sheet called Test Sheet which you can experiment with.

There are two buttons,
one calls the macro which creates a new sheet to receive copies of split rows and which splits the rows on Test Sheet at the same time.
The other button, deletes Test Sheet and then copies Sheet1 and renames it Test Sheet, ready for more experiment.

[wrightyrx7]

Considering you was working with very little data (my fault). You have done a great job!

Works exactly how i wanted it to, thank you so so much this will save me hours of work every month.

Thanks again

[snb]

Did you try autofilter or advancedfilter ?