Formula VBA inside corresponding cell, help?

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.

Please Login or Register  to view this content.

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!

It gives me the same error, and if I delete the Rows(number) part it won't go to the exact row for the cell I want to delete for the T column. In reality the formula eventually has to be changed to something like this:

"=IF(Pnumber="""","""",CONCATENATE(""s16"",LEFT(""00000"",5-LEN((ROW(tnumber-3)),ROW(Tnumber)-3)" The 4s in P4 and T4s need to address the row number inputted in the User Form Delete.