Hello everyone,

I am new to the forums and fairly new to VBA as well, I have been trying to solve this problem I've been getting and can't seem to fix it correctly. This is what I'm trying to do.

idformulaworks.JPG

This formula in the T column produces a unique ID for "new customers" every time there is an input in the P row. =IF(P4="","",CONCATENATE("S16",LEFT("00000",5-LEN(ROW(T4)-3)),ROW(T4)-3))
However, when adding the information for a "previous customer" with another User Form I created, that cell gets overwritten with the "previous customer's" Unique ID. In this event every S1600001

IdsOver.JPG

So now every time a "previous customer's" information is added his/her unique ID gets hard coded into the respective cell (No more formula), which won't cause a problem except that...

I have a Delete button which clears all of the "Customers" information (except columns with formulas) in the row of your choice. The problem arises when you delete a "previous customer's" information as the cell with the ID had already lost the formula it had in the beginning. What I am trying to do is the moment I confirm I want to delete the information, I want the macro to input the formula again to the respective ID cell.

This is my Delete Button VBA code.

Private Sub ConfirmButton_Click()

Dim number As String
number = DeleteForm.RowNumberBox.Value

Dim range1 As Range
Dim range2 As Range
Dim range3 As Range
Dim range4 As Range

Set range1 = Range("C1:R1")
Set range2 = Range("V1:AD1")
Set range3 = Range("AM1:AO1")
Set range4 = Range("T1")

   If MsgBox("Are you sure that you wish to delete the contents of row " & number & " ?", vbYesNo, "Confirm") = vbYes Then
         range1.Rows(number).ClearContents
         range2.Rows(number).ClearContents
         range3.Rows(number).ClearContents
         range4.Rows(number).Formula = "=IF(P4="""","""",CONCATENATE(""s16"",LEFT(""00000"",5-LEN((ROW(t4-3)),ROW(T4)-3)"
         
         Me.Hide
         
     End If
     
End Sub
This is the error I am getting when running the macro:

Debug issue.JPG

And this is the row it highlights when I click on debug.

highlight issue.JPG

I think that the problem is the way I am setting up the Range Object, it is not accepting me the .Formula range4.Range(number).Formula but I am not sure because with .ClearContents it works perfectly on the other range variables. Either that or that is just not the way to write a formula in VBA, I don't know if it accepts CONCATENATE inside the formula on VBA, I have seen other formulas where they use Application.WorksheetFunction. Not sure what the rules are on that.

Any help would be really appreciated!

Thanks!