Thanks to some of you i was able de answer my first problem, i didn't find a way to update my post but thank you !!

This is my working code :

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    
    Const FIRST_CELL_ADDRESS As String = "A2"
    Dim FeuilleExclue() As Variant: FeuilleExclue = _
        Array("HISTO", "VUE FINALE ANALYTIQUE", "BFT RENDEMENT 2030 CLIMAT")
    
    If IsNumeric(Application.Match(sh.Name, FeuilleExclue, 0)) Then Exit Sub
    
    Dim trg As Range:
    With sh.Range(FIRST_CELL_ADDRESS)
        Set trg = .Resize(sh.Rows.Count - .Row + 1)
    End With
    
    Dim irg As Range: Set irg = Intersect(trg, Target)
    If irg Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    ' This simplification is possible because you cannot paste a non-contiguous
    ' range unless you use VBA, and when you don't plan on clearing values.
    ' If you plan clearing values, loop trough the cells of `irg`,
    ' e.g., 'For Each cell In irg.Cells: With cell.EntireRow',
    ' and add an `If` statement in-between leaving the rest as is.
    With irg.EntireRow
        .Columns("H").FormulaR1C1 = "=IF(RC5<>"""",RC5/RC3 - 1,"""")"
        .Columns("I").FormulaR1C1 = _
            "=IFERROR(IF(AND(RC8<>"""",RC13<>""""),RC8-RC13,""""),"""")"
        .Columns("J").FormulaR1C1 = "=IF(RC9<>"""",ABS(RC9),"""")"
        .Columns("K").FormulaR1C1 = "=IF(R[+1]C7<>"""",R[+1]C7,"""")"
        .Columns("L").FormulaR1C1 = "=IF(R[+1]C7<>"""",RC11-RC7,"""")"
        .Columns("M").FormulaR1C1 = _
            "=IF(RC11<>"""",VLOOKUP(RC11,C15:C17,3,FALSE),"""")"
    End With
     
    Application.EnableEvents = True

End Sub

Now i've tried to add a modification to do the same thing but in column P, and i don't why know why now nothing work, like no modif appear even on column A.

 The "New" code :

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    
    Const FIRST_CELL_ADDRESS_A As String = "A4"
    Const FIRST_CELL_ADDRESS_P As String = "P4"
    Dim FeuilleExclue() As Variant: FeuilleExclue = _
        Array("HISTO", "VUE FINALE ANALYTIQUE", "BFT RENDEMENT 2030 CLIMAT")
    
    If IsNumeric(Application.Match(sh.Name, FeuilleExclue, 0)) Then Exit Sub
    
    Dim trgA As Range:
    With sh.Range(FIRST_CELL_ADDRESS_A)
        Set trgA = .Resize(sh.Rows.Count - .Row + 1)
    End With
    
    Dim trgP As Range:
    With sh.Range(FIRST_CELL_ADDRESS_P)
        Set trgP = .Resize(sh.Rows.Count - .Row + 1)
    End With
    
    Dim irgA As Range: Set irgA = Intersect(trgA, Target)
    Dim irgP As Range: Set irgP = Intersect(trgP, Target)
    
    If irgA And irgP Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    ' This simplification is possible because you cannot paste a non-contiguous
    ' range unless you use VBA, and when you don't plan on clearing values.
    ' If you plan clearing values, loop trough the cells of `irg`,
    ' e.g., 'For Each cell In irg.Cells: With cell.EntireRow',
    ' and add an `If` statement in-between leaving the rest as is.
    If Not irgA Is Nothing Then
        With irgA.EntireRow
            .Columns("H").FormulaR1C1 = "=IF(RC5<>"""",RC5/RC3 - 1,"""")"
            .Columns("I").FormulaR1C1 = _
                "=IFERROR(IF(AND(RC8<>"""",RC13<>""""),RC8-RC13,""""),"""")"
            .Columns("J").FormulaR1C1 = "=IF(RC9<>"""",ABS(RC9),"""")"
            .Columns("K").FormulaR1C1 = "=IF(R[+1]C7<>"""",R[+1]C7,"""")"
            .Columns("L").FormulaR1C1 = "=IF(R[+1]C7<>"""",RC11-RC7,"""")"
            .Columns("M").FormulaR1C1 = _
                "=IF(RC11<>"""",VLOOKUP(RC11,C15:C17,3,FALSE),"""")"
        End With
    End If
    
    If Not irgP Is Nothing Then
        With irgP.EntireRow
            .Columns("Q").FormulaR1C1 = "=IF(RC16 <>"""",RC16 / R[-1]C16 -1, """")"
        End With
    End If
            
    Application.EnableEvents = True
    
    End Sub

Does anyone have a solution ! Thanks for your help!

Thanks to some of you i was able de answer my first problem, i didn't find a way to update my post but thank you !!

This is my working code :

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    
    Const FIRST_CELL_ADDRESS As String = "A2"
    Dim FeuilleExclue() As Variant: FeuilleExclue = _
        Array("HISTO", "VUE FINALE ANALYTIQUE", "BFT RENDEMENT 2030 CLIMAT")
    
    If IsNumeric(Application.Match(sh.Name, FeuilleExclue, 0)) Then Exit Sub
    
    Dim trg As Range:
    With sh.Range(FIRST_CELL_ADDRESS)
        Set trg = .Resize(sh.Rows.Count - .Row + 1)
    End With
    
    Dim irg As Range: Set irg = Intersect(trg, Target)
    If irg Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    ' This simplification is possible because you cannot paste a non-contiguous
    ' range unless you use VBA, and when you don't plan on clearing values.
    ' If you plan clearing values, loop trough the cells of `irg`,
    ' e.g., 'For Each cell In irg.Cells: With cell.EntireRow',
    ' and add an `If` statement in-between leaving the rest as is.
    With irg.EntireRow
        .Columns("H").FormulaR1C1 = "=IF(RC5<>"""",RC5/RC3 - 1,"""")"
        .Columns("I").FormulaR1C1 = _
            "=IFERROR(IF(AND(RC8<>"""",RC13<>""""),RC8-RC13,""""),"""")"
        .Columns("J").FormulaR1C1 = "=IF(RC9<>"""",ABS(RC9),"""")"
        .Columns("K").FormulaR1C1 = "=IF(R[+1]C7<>"""",R[+1]C7,"""")"
        .Columns("L").FormulaR1C1 = "=IF(R[+1]C7<>"""",RC11-RC7,"""")"
        .Columns("M").FormulaR1C1 = _
            "=IF(RC11<>"""",VLOOKUP(RC11,C15:C17,3,FALSE),"""")"
    End With
     
    Application.EnableEvents = True

End Sub

Now i've tried to add a modification to do the same thing but in column P, and i don't why know why now nothing work, like no modif appear even on column A.

 The "New" code :

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    
    Const FIRST_CELL_ADDRESS_A As String = "A4"
    Const FIRST_CELL_ADDRESS_P As String = "P4"
    Dim FeuilleExclue() As Variant: FeuilleExclue = _
        Array("HISTO", "VUE FINALE ANALYTIQUE", "BFT RENDEMENT 2030 CLIMAT")
    
    If IsNumeric(Application.Match(sh.Name, FeuilleExclue, 0)) Then Exit Sub
    
    Dim trgA As Range:
    With sh.Range(FIRST_CELL_ADDRESS_A)
        Set trgA = .Resize(sh.Rows.Count - .Row + 1)
    End With
    
    Dim trgP As Range:
    With sh.Range(FIRST_CELL_ADDRESS_P)
        Set trgP = .Resize(sh.Rows.Count - .Row + 1)
    End With
    
    Dim irgA As Range: Set irgA = Intersect(trgA, Target)
    Dim irgP As Range: Set irgP = Intersect(trgP, Target)
    
    If irgA And irgP Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    ' This simplification is possible because you cannot paste a non-contiguous
    ' range unless you use VBA, and when you don't plan on clearing values.
    ' If you plan clearing values, loop trough the cells of `irg`,
    ' e.g., 'For Each cell In irg.Cells: With cell.EntireRow',
    ' and add an `If` statement in-between leaving the rest as is.
    If Not irgA Is Nothing Then
        With irgA.EntireRow
            .Columns("H").FormulaR1C1 = "=IF(RC5<>"""",RC5/RC3 - 1,"""")"
            .Columns("I").FormulaR1C1 = _
                "=IFERROR(IF(AND(RC8<>"""",RC13<>""""),RC8-RC13,""""),"""")"
            .Columns("J").FormulaR1C1 = "=IF(RC9<>"""",ABS(RC9),"""")"
            .Columns("K").FormulaR1C1 = "=IF(R[+1]C7<>"""",R[+1]C7,"""")"
            .Columns("L").FormulaR1C1 = "=IF(R[+1]C7<>"""",RC11-RC7,"""")"
            .Columns("M").FormulaR1C1 = _
                "=IF(RC11<>"""",VLOOKUP(RC11,C15:C17,3,FALSE),"""")"
        End With
    End If
    
    If Not irgP Is Nothing Then
        With irgP.EntireRow
            .Columns("Q").FormulaR1C1 = "=IF(RC16 <>"""",RC16 / R[-1]C16 -1, """")"
        End With
    End If
            
    Application.EnableEvents = True
    
    End Sub

Does anyone have a solution ! Thanks for your help!

• excel
• vba

• I have sometimes a mismatch 13 error – user29982961 Commented Mar 19 at 9:10
• Which line throws the error? If you execute the code step-wise, are all lines executed as expected? (If irgA And irgP Is Nothing looks strange) – Shrotter Commented Mar 19 at 9:17
• 1 The correct line is If irgA Is Nothing And irgP Is Nothing Then Exit Sub, – VBasic2008 Commented Mar 19 at 9:18

1 Answer 1

As already pointed out in the comments: The following commmand is causing the issue:

If irgA And irgP Is Nothing Then Exit Sub

What you want is

If irgA Is Nothing And irgP Is Nothing Then Exit Sub

Writing irgA And irgP Is Nothing is not checking if irgA is Nothing and irgP is Nothing.

The VBA runtime is interpreting this term as irgA And (irgP Is Nothing). It evaluates first irgP Is Nothing which is a boolean (might be true or false). Then it evaluates the And-operator on irgA and the boolean result. This operation fails with type mismatch (most of the times) because the And-operator expects boolean or numeric values.