Search code examples
excelvbaexcel-formulaexcel-2019vba7

Why has my VBA stopped working only in certain parts?


Recently the VBA has stopped working. Normally it auto-populates today's date into column B when you enter data into column C and also populates today's date into column R when you enter data in column K.

I have recently messed around with protecting sheets and in the end, gave up and I have a feeling it has something to do with it.

It is odd because other areas of the VBA still work.

How can fix this so the VBA still auto-populates the columns it needs to?

        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("C:C"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, -1).ClearContents
                Else
                    With .Offset(0, -1)
                        .NumberFormat = "dd mmm yy"
                        .Value = Date
                    End With
                End If
                Application.EnableEvents = True
            End If
        End With
        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("K:K"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, 7).ClearContents
                Else
                    With .Offset(0, 7)
                        .NumberFormat = "dd mmm yy"
                        .Value = Date
                    End With
                End If
                Application.EnableEvents = True
            End If
        End With
    
    Const sCell As String = "K2" ' Source First Cell
    Const dCol As Variant = "J" ' Destination Column Id (String or Index)
    
    Dim irg As Range ' Intersect Range
    Dim cOffset As Long ' Column Offset
    With Range(sCell)
        Set irg = Intersect(.Resize(.Worksheet.Rows.Count - .Row + 1), Target)
        If irg Is Nothing Then Exit Sub
        cOffset = Columns(dCol).Column - .Column
    End With
    
    Dim arg As Range ' Current Area of Intersect Range
    Dim cel As Range ' Current Cell in Current Area of Intersect Range
    For Each arg In irg.Areas
        For Each cel In arg.Cells
            If Not IsError(cel.Value) Then
                cel.Offset(, cOffset).Value = cel.Value
            End If
        Next cel
    Next arg
    
End Sub```

Solution

  • Example using error handler to ensure events aren't left turned off:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim c As Range
    
        If Target.Cells.CountLarge > 1 Then Exit Sub
        
        If Target.Column = 3 Then
            Set c = Target.Offset(0, -1)
        ElseIf Target.Column = 11 Then
            Set c = Target.Offset(0, 7)
        End If
        
        On Error GoTo haveError 'turn on error handling
        
        If Not c Is Nothing Then 'col 3 or 11...
            Application.EnableEvents = False
            If Len(Target.Value) = 0 Then
                c.ClearContents
            Else
                c.NumberFormat = "dd mmm yy"
                c.Value = Date
            End If
            Application.EnableEvents = True
        End If
        
        Exit Sub 'normal exit here
        
    haveError:
        MsgBox "Got an error: " & Err.Description
        Application.EnableEvents = True 'makes sure events are not left off
          
    End Sub