Formula VBA inside corresponding cell, help?

[avd88]

Hello everyone,

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.

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

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.

[COLOR=#333333]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">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</code>[/COLOR]

This is the error I am getting when running the macro:

Run Time Error '1004' Application-defined or Object-Defined error.

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

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!

 

[StephenCrump]

I haven't tried to follow what you're doing, and I don't know if your code will do what you want ...

But your immediate problem is just syntax:

'You need to change:
range4.Rows(number).Formula = "=IF(P4="""","""",CONCATENATE(""S16"",LEFT(""00000"",5-LEN((ROW(T4-3)),ROW(T4)-3)"

'To
range4.Rows(number).Formula = "=IF(P4="""","""",CONCATENATE(""S16"",LEFT(""00000"",5-LEN(ROW(T4)-3)),ROW(T4)-3))"

'Or, more succinctly:
range4.Rows(number).Formula = "=IF(P4="""","""",""S16""&RIGHT(""0000""&ROW(T4)-3,5))"

 

[avd88]

Hey thanks!! Your first suggestion works. I am now trying to replace P4 as Cells(number, 16) and T4 as Cells(number, 20). I tried this, but it won't work, any ideas?

Dim range5 As range
Dim range6 As range


    range5 = Cells(number, 16)
    range6 = Cells(number, 20)
    
range4.Rows(number).Formula = "=IF(range5="""","""",CONCATENATE(""S16"",LEFT(""00000"",5-LEN(ROW(range6)-3)),ROW(range6)-3))"

 

[StephenCrump]

I tried this, but it won't work, any ideas?

It's always easier if you explain what "won't work" means.

For example: Are you getting error messages? If so, what is the message, and which code line is affected? Or is the code running, but producing unexpected results? If so, what do you expect to happen and what is actually happening?

Not a big problem here, because it's just a little code snippet:

1. You need to Set range objects

Set range5 = Cells(number, 16)
Set range6 = Cells(number, 20)

Also, have you set range4?

2. VBA variables won't mean anything to an Excel formula, so you need to pass their values like this:

range4.Rows(number).Formula = "=IF(" & range5.Address & "="""","""",CONCATENATE(""S16"",LEFT(""00000"",5-LEN(ROW(" & range6.Address & ")-3)),ROW(" & range6.Address & ")-3))"

Again, this could be done more succinctly, e.g. don't use range5 or range6 at all, and simply say

range4.Rows(number).Formula = "=IF(P" & number = """" ....

 

[avd88]

It works perfectly!! Thanks Stephen, this was driving me crazy!

 

[StephenCrump]

You're welcome.

I probably won't be signing into the Forum for a week or so.

I hope that if you have any follow up questions someone else will jump in.