Self deleting cell formulas

I am new to VBA and am very much finding my way around it. I understand how to attach code to buttons and objects but I am not sure about adding background instructions to achieve something like a unique identifier. Would you be able to point me in the right direction, perhaps suggest the first line so I can have a go at figuring the rest out?

I'm having trouble applying the formula to a column of cells through VBA. In Excel I was able to drag my formula down my column and the cell references would change automatically. In VBA I am able to set ranges or specific cells but am not sure about translating my Excel thinking into VBA. Thank you for the pointer in the last reply - learnt a lot of other things but feel I'm missing some understanding for this particular question.

In VBA, a quick copy/pastevalues example...

Please Login or Register  to view this content.

Thanks for the suggestion (I think I'm slowly getting there!)

When a value appears in the "C" column, the unique number generated is in the corresponding "B" column. Although the Max function bit doesn't work yet (might need help here), I've got something for when a value appears in one cell but how do I replicate it down so I don't have to write a working version of this code for

If Range ("C56")...
If Range ("C56")...
If Range ("C57")...
...and so on (the Database will go up to C1050).

Set myRange = Worksheets("Master_Database").Range("B50:B1050")
If Range("C55").Value <> "" Then
Range("B55").Value = 1 + Max(myRange)
End If

Thanks snb, I'll have a go at your suggestion. Just to clarify my last post, I have managed to make my ID number work for an individual cell but how do I avoid copying the below (now working) code replacing the 3rd line (If Range ("C60...) with

If Range ("C61...
If Range ("C62...
If Range ("C62...
etc. (up to If Range ("C1050...)?


Set myRange = Worksheets("Master_Database").Range("B50:B1050")
answer = Application.WorksheetFunction.Max(myRange)
If Range("C60") <> "" Then
Range("B60") = answer + 1
End If

I've understood the latest suggestion from snb but have a different version of the same issue. Is anyone able to put me out of my misery for my previous post?

Just reposting my reply to comply with rules (apologies):

Thanks snb, I'll have a go at your suggestion. Just to clarify my last post, I have managed to make my ID number work for an individual cell but how do I avoid copying the below (now working) code replacing the 3rd line (If Range ("C60...) with

If Range ("C61...
If Range ("C62...
If Range ("C62...
etc. (up to If Range ("C1050...)?

Please Login or Register  to view this content.

Try this in the worksheet code module...

Please Login or Register  to view this content.

Thanks for putting this together but unfortunately, I don't seem to be able to make it work. Do I need to define any parts of the code? I put it in it's own module and also tried adding it to the userform code too but no luck. I'll post a fuller picture of the setup I have:

My workbook has three worksheets. One is called "Master_Database", the other is "New_Entry" and the last "Print_Copy". The "New_Entry" sheet is just a button that one can click and it makes a userform pop up. All the userform boxes are filled in by the user and then the user clicks on a button at the bottom of the userform that I've called "Submit". This triggers the following code: As you can see, it sends the userform contents to the "Master_Database" worksheet as a row of data (and sends the contents of this row to a "Print_Copy" template (but that's irrelevant for now)), it then clears the userform ready for a new entry:

Please Login or Register  to view this content.

The "Master_Database" worksheet is laid out with the headings "Answer1", "Answer2", "Answer3" in cells "C49", "D49", "E49". In "B49" I've called it "UniqueID". In "B50" I have 000000 and in the rest of the row (C50, D50, E50), I have "Example".

Currently, when the userform is submitted, Columns C, D, E populate successfully but I would like when this happens, for Column B to aswell populate with 000001, 000002,000003 etc.

I think dangelor's suggestion is almost there but just having problems implementing it.

Thank you to everyone so far!

Helpful thread? Here

Hello, after much searching, I think the above thread might be the answer. Would anyone be able to make it work with my question? I've tried but I'm struggling.

To utilize the code, right click the name tab on the Master_Database worksheet. Select View Code. Paste the code into the window that opens. Every time something on the worksheet changes, such as adding a line of data, the code will execute.

Originally Posted by dangelor

To utilize the code, right click the name tab on the Master_Database worksheet. Select View Code. Paste the code into the window that opens. Every time something on the worksheet changes, such as adding a line of data, the code will execute.

I've sort of got it to work now (it's filling in the right column but doesn't recognize that the database headings are on row 50 and the data fills in from row 51, then 52, 53 etc.) but I'll explain why I won't be able to use it:

The background to the issue I had using the spreadsheet formula as shown in my first post was that if an entry in the database was deleted, all the Number IDs would reassign themselves. So where you had 001, 002, 003, 004, 005 for example, if you deleted database row 004, the numbers would change to 001, 002, 003, 004 instead of: 001, 002, 003, 005. This issue could be solved by getting VBA code to assign the number but that's where I got stuck. See my post before last for the full layout of the spreadsheet / database I have.

Thanks for the suggestion though, I feel I'm getting closer to the solution!

Can you post a dummy file?

Originally Posted by royUK

I suggested how to do this, gave you some leads not completed code as you asked. You haven't even used the suggestions.

You have a userform that adds data to the sheet so you don't need sheet formulas for this.

If the unique number is in Column A then

Please Login or Register  to view this content.

Thank you for your help. I did post an attempt with your initial suggestion (Post #12) but as I am still developing a basic understanding of VBA, I spend a lot of time not getting very far. I don't post all these attempts as I was didn't want to fill the board up with code that might divert from my original issue.

I will have a go with the above.