Calcul des événements de changement en VBA

Question

J'essaie d'utiliser l'événement de changement de feuille de travail dans Excel VBA, mais cela ne semble pas fonctionner comme je le pensais.

Je veux essentiellement calculer la valeur d'une cellule (Q2) lorsque la valeur d'une autre cellule (R2) est modifiée ou vice versa.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("O:R")) Is Nothing Then Exit Sub

    Application.EnableEvents = False

    If Target.Column = 3 Then
    'User has changed something in column Q:
        Target.Offset(0, 1).Value = Cells(2, 3) * Cells(2, 1)
    If Target.Column = 4 Then
    'User has changed something in column R:
        Target.Offset(0, -1).Value = Cells(2, 3) / Cells(2, 1)
    End If

    Application.EnableEvents = True
End Sub

Answer 1

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("O:R")) Is Nothing Then Exit Sub

    Application.EnableEvents = False

    'If Target.Column = 17 Then 'CHANGED HERE!
        'User has changed something in column Q:
         'Target.Offset(0, 1).Value = Cells(2, 3) * Cells(2, 1)
    'End If
    'If Target.Column = 18 Then 'CHANGED HERE!
        'User has changed something in column R:
        'Target.Offset(0, -1).Value = Cells(2, 3) / Cells(2, 1)
    'End If

    ' I leave the If-versions above for info, but Select Case is better sometimes
    Select Case Target.Column
    Case 17 ' column Q
        Target.Offset(0, 1).Value = Cells(2, 3) * Cells(2, 1)
    Case 18 ' column R
        Target.Offset(0, -1).Value = Cells(2, 3) / Cells(2, 1)
    End Select

    Application.EnableEvents = True
End Sub

La colonne Q est le numéro 17, et la colonne R est le numéro 18, comme indiqué ci-dessus.

Answer 2

N'évitez pas de travailler avec plusieurs cellules comme cible. Intersect peut rapidement analyser, même en supprimant plusieurs colonnes entières, la plage appropriée et la restreindre davantage à la plage utilisée de la feuille de calcul.

Ajoutez un contrôle des erreurs, en particulier pour l'opération de division. Une cellule vide en A2 étouffera rapidement le calcul lors d'une division par zéro.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    'deal with multiple cells as bel;ow; don't avoid them
    'If Target.Cells.Count > 1 Then Exit Sub

    'use the Intersect to determine if relevant cells have been chanmged
    'note: columns Q:R, not O:R and restrict to the used range
    If Not Intersect(Target, Target.Parent.UsedRange, Range("Q:R")) Is Nothing Then
        On Error GoTo Safe_Exit
        Application.EnableEvents = False
        Dim trgt As Range
        For Each trgt In Intersect(Target, Target.Parent.UsedRange, Range("Q:R"))
            Select Case trgt.Column
                Case 17
                    'guard against multiplying a number by text
                    If Not IsError(Cells(2, 3).Value2 * Cells(2, 1).Value2) Then
                        trgt.Offset(0, 1) = Cells(2, 3).Value2 * Cells(2, 1).Value2
                    End If
                Case 18
                    'guard against possible #DIV/0! error and divding a number by text
                    If Not IsError(Cells(2, 3).Value2 / Cells(2, 1).Value2) Then
                        trgt.Offset(0, -1) = Cells(2, 3).Value2 / Cells(2, 1).Value2
                    End If
            End Select
        Next trgt
    End If  

Safe_Exit:
    Application.EnableEvents = True
End Sub

Je suis presque sûr que le calcul réel devrait impliquer une variable comme trgt.Row mais le calcul que vous avez affiché n'utilisait que C2 et A2 comme références de cellules statiques pour diviser/multiplier l'une par rapport à l'autre.