recherche d'une donnée sans bouton

Private Sub Worksheet_Change(ByVal Cible As Range)
Dim Client$, Encours$, SoldeClient$

'========= Paramètres à adapter :

'1. dans cette feuille :
Client = "F8"
Encours = "G8"

'2. dans les feuilles "clientsx"
SoldeClient = "D"
'================================

  If Not Intersect(Cible, Range(Client)) Is Nothing Then
    On Error Resume Next
    With Worksheets(Range(Client).Value)
      If Err.Number Then
        Range(Encours).Value = Empty
        MsgBox "Tabernacle !" & vbLf & vbLf _
          & "Le client """ & Range(Client).Value & """ n'existe pas.", vbCritical, "Liste des clients foireuse..."
      Else
        Range(Encours).Value = .Cells(.Rows.Count, SoldeClient).End(xlUp).Value
      End If
    End With
    On Error GoTo 0
  End If

End Sub

Sub Bouton2_Cliquer()
Dim Client$, Encaissement$, EncaissementClient$, Encours$, Date1$, Date2$, SoldeClient$

'========= Paramètres à adapter :

'1. dans cette feuille :
Date1 = "E8"
Client = "F8"
Encaissement = "G11"
Encours = "G8"

'2. dans les feuilles "clientsx"
Date2 = "A"
EncaissementClient = "C"
SoldeClient = "D"
'================================

  On Error Resume Next
  With Worksheets(Range(Client).Value)
    If Err.Number Then
      MsgBox "Jésus, Marie, Joseph !" & vbLf & vbLf _
        & "Le client """ & Range(Client).Value & """ n'existe pas.", vbCritical, "Liste des clients foireuse..."
    Else
      With .Cells(.Rows.Count, Date2).End(xlUp).Offset(1)
        .Value = Range(Date1).Value
        .Offset(, Columns(EncaissementClient).Column - Columns(Date2).Column).Value = Range(Encaissement).Value
        .Offset(, Columns(SoldeClient).Column - Columns(Date2).Column).FormulaR1C1 = "=IF(ROW()>2,IF(RC[-2]=0,IF(RC[-1]=0,0,R[-1]C-RC[-1]),R[-1]C+RC[-2]-RC[-1]),RC[-2]-RC[-1])"
      End With
      Range(Encours).Value = Range(Encours).Value - Range(Encaissement).Value
      Range(Encaissement).Value = Empty
    End If
  End With
  On Error GoTo 0

End Sub

Private Sub Worksheet_Change(ByVal Cible As Range)
Dim i%, j%, x%, u(), v(1 To 5, 1 To 4)
Dim Client$, Encours$, SoldeClient$, Date3$, Date4$

'========= Paramètres à adapter :

'1. dans cette feuille :
Client = "F8"
Encours = "G8"
Date4 = "E17"

'2. dans les feuilles "clientsx"
SoldeClient = "D"
Date3 = "A"
'================================

  If Not Intersect(Cible, Range(Client)) Is Nothing Then
    On Error Resume Next
    With Worksheets(Range(Client).Value)
      If Err.Number Then
        Range(Encours).Value = Empty
        MsgBox "Veuillez selectionner un client.", vbCritical, "Liste des clients foireuse..."
      Else
        Range(Encours).Value = .Cells(.Rows.Count, SoldeClient).End(xlUp).Value
        x = .Cells(Rows.Count, Date3).End(xlUp).Row - 1
        If x > 1 Then
          u = .Range(.Cells((x - 2 + Abs(x - 6)) / 2, 1), .Cells((x + 2 + Abs(x - 2)) / 2, 4)).Value
          For i = UBound(u) To 1 Step -1: For j = 1 To 4: v(UBound(u) + 1 - i, j) = u(i, j): Next j, i
        End If
        Range(Date4).Resize(UBound(v, 1), UBound(v, 2)).Value = v
      End If
    End With
    On Error GoTo 0
  End If

End Sub

        .Offset(, Columns(SoldeClient).Column - Columns(Date2).Column).FormulaR1C1 = "=IF(ROW()>2,IF(RC[-2]=0,IF(RC[-1]=0,0,R[-1]C-RC[-1]),R[-1]C+RC[-2]-RC[-1]),RC[-2]-RC[-1])"

        With .Offset(, Columns(SoldeClient).Column - Columns(Date2).Column)
          .FormulaR1C1 = "=IF(ROW()>2,IF(RC[-2]=0,IF(RC[-1]=0,0,R[-1]C-RC[-1]),R[-1]C+RC[-2]-RC[-1]),RC[-2]-RC[-1])"
          .Value = .Value
        End With