Thread: 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...

Range("B55") = Range("B55").Value

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...)?

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

Try this in the worksheet code module...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCell       As Range

    On Error GoTo errorhandler
    For Each rCell In Intersect(Columns(2), UsedRange).SpecialCells(xlCellTypeBlanks)
        rCell = WorksheetFunction.Max(Columns(2)) + 1
    Next rCell
errorhandler:
End Sub

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:

Private Sub Submit_Click()
If MsgBox("Send to Database?", vbYesNo + vbQuestion) = vbNo Then
   Exit Sub
      
      Else
    Sheets("Print").Range("C7") = Answer1.Text
    Sheets("Print").Range("C8") = Answer2.Text
    Sheets("Print").Range("C9") = Answer3.Text
  
     End If
    
        
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Master_Database")

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

ws.Cells(iRow, 3).Value = Me.Answer1.Value
ws.Cells(iRow, 4).Value = Me.Answer2.Value
ws.Cells(iRow, 5).Value = Me.Answer3.Value

Me.Answer1.Value = ""
Me.Answer2.Value = ""
Me.Answer3.Value = ""
Me.Answer1.SetFocus


End Sub

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.