Find if a given value is in a cell, if so then try next value until unique

[bigfoot007]

I have the below sub that checks on a separate worksheet if the created number in textbox8 already exists, at the moment there is a message box that alerts the user that the part number already exists, they have to click OK, then the number is incremented by 1, the process is repeated until a unique number is found. This is the written to the worksheet along with some other data.

What I need to do is remove the message box so it will automatically search and find the next available number.

I added the following code to the sub, but this has no effect:

Please Login or Register  to view this content.

Code for sub

Please Login or Register  to view this content.

[ARowbot]

What about:

Please Login or Register  to view this content.

[bigfoot007]

Unfortunatley this give a compile error: Next without For.
If I correct this to the following

Please Login or Register  to view this content.

I now get a run-time error '9': subscript out of range

[JOHN H. DAVIS]

Review your code and you'll know why you're getting a subscript out of range.

Please Login or Register  to view this content.

[bigfoot007]

Sorry but i'm not sure what you mean?

Review your code and you'll know why you're getting a subscript out of range.

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Check the spelling of your sheet names. They don't match. Shouldn't they?

[bigfoot007]

You are right, my eyesight must be failing me!!

After the correction, it works without an error, but will not increment by 1 on the textbox26 value, it just creates the same number and inserts this onto the next row.

[JOHN H. DAVIS]

Not sure about what you are trying too do, but that specific piece of code seems to me is only selecting the cell, then continuing with the Loop. I don't where it takes any action. Is it supposed too?

[ARowbot]

my bad on the spelling! Try...

Please Login or Register  to view this content.

I'm a bit of an amateur so there may be slight mistakes in the coding as we don't have a template to work with!

[bigfoot007]

I have attached the worksheet, it is simple to use, just click on the big gery button, user form will appear, just need to select from department dropdown "Parts" , then from the type dropdown "Machined Parts",click the radio button, then click creatye final article number.
it will come up with 501-0001, but this is incorrect as it already exists in the existing worksheet, what it should do is go to 501-0002
Article Number Request v5aset.xlsm

[ARowbot]

Did you try that code to see if it did what you wanted?

[bigfoot007]

I have tried the code but this still does the same, overwrites the first number and doesnt increase

[ARowbot]

Slight adjustment...

Please Login or Register  to view this content.

[bigfoot007]

sorry still not working, no errors, but now it just appends the same number to the end of the list on the sheet "existing".
I thought this would be easy!!

[ARowbot]

I believe I have spotted the problem...
Textbox 26 is "-0001" By default.
I now have a code to increase this by 1...However that will go to 0 Rather than your requested "-0002"
You need to change your idea slightly!
Here is the latest code to increase it by one once you have done that!

Please Login or Register  to view this content.

EDIT: I have now corrected your Code for you...Only problem is it no longer leaves it in the correct format of "0000" for you. I'm unsure on that bit of coding so you will have to correct that bit

Please Login or Register  to view this content.

[bigfoot007]

Brilliant, that has done the trick, thanks for all the help.