Method 'Formula' of object 'Range' failed on Worksheet Change Event

what am i trying to accomplish:

I have a column of cells that have a formula (P5:P100), but i also want to be able to manually insert a value in those cells. Im trying to do this modifying Worksheet Change Event for the sheet. The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then GoTo Done

    If Application.Intersect(Target, ActiveSheet.Range("P5:P100")) Is Nothing Then GoTo Done


        If ActiveCell.Value = "" Then
        ActiveCell.formula = _
        "=IF(N5="" "",XLOOKUP(LEFT(O5,9),Table6[SKU],Table6[Costo Unitario (DOP)],""Revisar"",-1,-1),"""")"

        End If

Done:
End Sub

I get the " Method 'Formula' of object 'Range' failed" error on the formula line. I tried using a simpler formula and it doesnt give me the error.

CodePudding user response：

firstly I agree with data_sc that you may want to use this with caution.

Fixing the issue though:

I ran this through a few times and this is actually a duplicate of this question though its not obvious why at first glance!

when your code runs it changes a cell actually triggering itself again i.e.

user updates the cell -> code runs -> code updates a cell -> code runs

On the second run your code then evaluates:

ActiveCell.Value = ""

however because of the if statement placed in the first run of the code (the one you actually wanted) if n5 is not then the value of the if statement is ""

if you change to

If ActiveCell.Formula = ""

then your code will run and not error

a more full solution, removing the goto behaviour and using the solution at the link (and this for intersect) would be:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo ErrHandler
    
    If Target.Cells.Count < 2 And _
    Not Application.Intersect(Target, ActiveSheet.Range("P5:P100")) Is Nothing And _
    ActiveCell.Formula = "" Then
              ActiveCell.Formula = "=IF(N5="" "",2,"""")"
    End If
    
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    Application.EnableEvents = True
End Sub

hope it helps

CodePudding user response：

It worked great. This is how the final code looks:

Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ErrHandler

If Target.Cells.Count < 2 And _
Not Application.Intersect(Target, ActiveSheet.Range("P5:P100")) Is Nothing And _
Target.formula = "" Then
          Target.FormulaR1C1 = _
    "=IF(RC[-2]="" "",XLOOKUP(LEFT(RC[-1],9),Table6[SKU],Table6[Costo Unitario (DOP)],""Revisar"",-1,-1),"""")"
End If

Application.EnableEvents = True
Exit Sub

ErrHandler: Application.EnableEvents = True End Sub