Dynamic insertion or deleting of blank cells

[margal]

Hi Everyone,

I don't know if it is possible to do what im hoping, but its worth asking the question.

I work with accounts and apply for values of projects based on percentage complete, it would be laid out similar to below

HEADER WORK TITLE VALUE PERCENTAGE SPLIT PERCENTAGE VALUE
CONTRACTED WORKS 1920: INSTALL FANS £10000
CONTRACTED WORKS 1930: ISTALL LIGHTS £20000

What I would like to do in VBA is that we sometimes have to split it into percentages, this could be in 10% increments, 20%, 25%, 50% or 100% dependant on the client

If in percentage split we selected on a data validation 20% on install fans, then I would like 5 blank lines inserted underneath with each value being split into 20% increments, so £2000 each one, if they decide to change this to 10% increments, then this should adapt it to 10 cells inserted in total and each value to be £1000, if they adapted it to 50% increments then it should be just to blank cells each worth £5000

It would have to be a pretty clever code as you may have to insert cells if the percentage is reduced, but if the percentage is higher, then it would have to delete cells.

I do hope that make sense, and would really appreciate any help.

Kind regards,

Simon.

[Bernie Deitrick]

Select a cell with the value to be split, and run this macro:

Please Login or Register  to view this content.

[margal]

Hi Bernie,

Firstly many thanks for taking the time to respond,

Ive made a start with my code, and im pretty much there, the only I need to do now, is if I add blank cells, below, so if ive changed it from 100% to 50% I need it to copy the data from the above cell, past below, but split the value in half etc.

Below is my code

Please Login or Register  to view this content.

im trying to figure out how to attach an example!!

[Bernie Deitrick]

Try it like this - enter the number of splits you want (2 to n) in oolumn L

Please Login or Register  to view this content.

[margal]

Again,

Much appreciated for your help with this, im very close to what I need now, again below is where I have adapted your code, and where I still have a little issue.

From the percentage split now its very good on when we change to split the value it will give the value correct based on some formulas (please see attached spreadsheet).

The issue I have is if you then change a value say from 50 percent, where it has now duplicated a line, back to 100% your value is too high, therefore, we can only ever allow an ID no to ever be 100%, if its sees duplicates, it then needs to delete it, below is the code of where im at, and what will need to be adapted is the first bit of code that deletes with the deleting of cells, would appreciate if you can help again with this.

Much appreciated,

Simon

Please Login or Register  to view this content.

[Bernie Deitrick]

Try these two macros:

Please Login or Register  to view this content.