method formula of object range failed

user2950612 0
Viewed 27

Currently, trying to create a sheet that logs time as soon as I input into a specific column. Unfortunately I am now getting the error ( Method 'Formula' of object 'Range' failed on Worksheet Change event. This Code used to work but now is being asked to be used again after several years and when I sent the file it continues to throw this error? I am not exactly great at this sort of thing and hoping anyone can help. I apologize for the spaghetti

Private Sub Worksheet_Change(ByVal Target As Range)
Dim loop_ctr As Integer
loop_ctr = 1


On Error GoTo Error_Handler


Do

Error_Handler:
If Err.Number <> 0 Then
MsgBox Err.Description
Debug.Print Err.Description
End If

'    If Target.Locked = True Then
'    Application.Undo
'    MsgBox "Please use only the Blue columns, Time stamps can only be edited by entering and removing data from these columns "
'    Resume
'    End If



Sheets("Primary").Protect UserInterfaceOnly:=True
Range("L2:L300").Formula = "=K2-B2"


With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

If Target.Column = 5 Then
    
    If Target.Column = 5 And Target.Offset(0, -3).Value = "" Then                         ' Print Notification Timestamp after entering in problem
    Target.Offset(0, -3) = Format(Now(), "HH:MM:SS")
     ElseIf Not Intersect(Target, Range("E:E")) Is Nothing Then
        If Target = "" Then
            Range("B" & Target.Row).Clear
        Else
        End If
     Else
     End If
     
        
    
ElseIf Target.Column = 7 Then
    
    If Target.Column = 7 And Target.Offset(0, -1).Value = "" Then                         ' Print Dispatch time after mechanic entered
    Target.Offset(0, -1) = Format(Now(), "HH:MM:SS")
     ElseIf Not Intersect(Target, Range("G:G")) Is Nothing Then
        If Target = "" Then
            Range("F" & Target.Row).Clear
        Else
        End If
     Else
     End If
        
    
ElseIf Target.Column = 8 Then
    
    If Target.Column = 8 And Target.Offset(0, -2).Value = "" Then                       ' Print Dispatch Timestamp after specialist entered
    Target.Offset(0, -2) = Format(Now(), "HH:MM:SS")
     ElseIf Not Intersect(Target, Range("H:H")) Is Nothing Then
        If Target = "" Then
            Range("F" & Target.Row).Clear
        Else
        End If
    Else
    End If
        
    
ElseIf Target.Column = 10 Then
    
    If Target.Column = 10 And Target.Offset(0, 1).Value = "" Then                       ' Print Resolution Timestamp after resolution entered
    Target.Offset(0, 1) = Format(Now(), "HH:MM:SS")
     ElseIf Not Intersect(Target, Range("J:J")) Is Nothing Then
        If Target = "" Then
            Range("K" & Target.Row).Clear
        Else
        End If
     Else
     End If
        

ElseIf Target.Column = 17 Then
    
        
    If Target.Column = 17 And Target.Offset(0, 1).Value = "" Then                       ' Print Break Timestamp after personell is entered
    Target.Offset(0, 1) = Format(Now(), "HH:MM:SS")
     ElseIf Not Intersect(Target, Range("Q:Q")) Is Nothing Then
        If Target = "" Then
            Range("R" & Target.Row).Clear
        Else
        End If
     Else
     End If

Else
End If
     
    ' Application.CutCopyMode = False                                                    ' disable copy cut paste for format reasons
     With Application
        .ScreenUpdating = True
        .EnableEvents = True
     End With
    
Loop Until loop_ctr

End Sub
vba excel
1 Answers

Less spaghetti: the only thing which depends on the column number is the offset for the timestamp, so you can reduce your code quite a bit by focusing on that:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, n As Long
    
    If Target.Cells.CountLarge > 100 Then Exit Sub 'some suitable cut-off
    
    On Error GoTo Error_Handler
    
    Application.EnableEvents = False
    
    Me.Protect UserInterfaceOnly:=True
    Me.Range("L2:L300").Formula = "=K2-B2"
    
    For Each c In Target.Cells
        n = 0  'clear offset
        Select Case c.Column
            Case 5: n = -3 'choosing offset according to column number...
            Case 7: n = -1
            Case 8: n = -2
            Case 10, 17: n = 1
        End Select
        
        If n <> 0 Then   'monitored column?
            With c.Offset(0, n)
                If Len(c.Value) > 0 Then
                    If Len(.Value) = 0 Then .Value = Format(Now(), "HH:MM:SS")
                Else
                    .ClearContents
                End If
            End With
        End If
    Next c
    
Error_Handler:
    If Err.Number <> 0 Then
        MsgBox Err.Description
        Debug.Print Err.Description
    End If
    Application.EnableEvents = True

End Sub
useryB020
Related