Increment value when a new line is inserted

[gtbear]

Hi All,

Sorry but I've been trying to search for this but without any success.

I'm trying to get a some code to increment a number whenever a new line is inserted and then have the number appear in the new line.

The incremental number needs to appear in column J when anything is inserted into column B.

I have attached the spreadsheet for reference.

Thank You

David

[taps]

Hi,

Query 1: What should be the newly generated number? Is it 1,2,3...or 100,101,102....
My question is what is the logic of creating new number?
Query 2: What happen if column B is blank?

Regards
taps

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[gtbear]

Hi taps,

Thanks for coming back to be.

In answer to query one the number should be a six digit number with leading zeros . i.e. 000001

Query 2 if column b is blank then J should be blank.

Regards

David

[taps]

Hi,

You can also use below codes

Please Login or Register  to view this content.

Note that, it does not depend on whether you are inserting any rows or not. If you are entering any value in a cell in B column
and if the cell in J column in blank then it search the Max value in J column and update the current cell in J column with incrementing 1.

However, don't create two blank rows simultaneously. Create one row and update it and then again create another row.
You can also update the data at the bottom of the data (without keeping any blank row).

If you want to modify any cell in B column then don't press enter, press right/left arrow or Tab button, so that above code does not work.

Also my request to JOHN is please help me to understatnd your code, how it works . Thanks in advance.

Regards
taps

[gtbear]

Hi everyone, thanks for coming back to me and helping.

Sorry I didn't acknowledge you help, in the middle of changing jobs and too many things going on together.

I got some this code to work.

Please Login or Register  to view this content.

Was completely happy with the code so I then compied the worksheet 12 times so I had one for each calendar month, put the code into each sheet. But when I enter a new line on any sheet, the variable PwInc increments but 96 each time. I can't figure out why this would happen.

I have tried to attached a copy of this sheet but the sheet is too large.

Thanks

David

[gtbear]

Worked this out now, I needed to put

Please Login or Register  to view this content.

at the beginning and end of the code.

Thanks for taking the time to look

David