Search code examples
excelvba

How to solve the VBA error invalid inside procedure


I am currently trying to run this VBA code and keep getting the repeated error 'Invalid inside procedure'. Could anyone suggest what part of the code is wrong?

I have attempted to fault find however can not seem to find the root of the problem.

        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 yyyy"
                        .Value = Now
                    End With
                End If
                Application.EnableEvents = True
            End If
        End With
        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("G:G"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, 12).ClearContents
                Else
                    With .Offset(0, 12)
                        .NumberFormat = "dd MMM yyyy"
                        .Value = Now
                    End With
                End If
                Application.EnableEvents = True
            End If
        End With
    Option Explicit
    
    Const sCell As String = "G2" ' 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

  • Try to declare Option Explicit before any procedure -- not within it.

    enter image description here