La méthode replace du VBA Excel ne permet pas de contrôler la zone de recherche: feuille ou classeur entier

Lorsque vous effectuez un remplacer manuel (Ctrl + H), les options vous permettent de sélectionner où le remplacement doit s'effectuer. La portée de cette fonction est soit :

  • La feuille active,
  • Le classeur actif.

Malheureusement, il est impossible de programmer cette portée en VBA:

expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, _
 MatchByte, SearchFormat, ReplaceFormat)

En conséquence si votre code VBA prévoit de faire une recherche ou un remplacement sur une feuille particulière, il suffit qu'un utilisateur ait utilisé la fonction manuellement et changé les options par défaut pour que le code que vous avez écrit ne se comporte plus de la manière escomptée.

Il existe une parade. Il faut effectuer une recherche factice qui va remettre à zéro les options de recherche et de remplacement. Insérez le code suivant avant votre recherche :

 

Dim Z_Reset As Range
Set Z_Reset = Worksheets(1).Range("A1:A1").Find("Reset", LookIn:=xlValues)


Ensuite, soit votre objectif est de rechercher dans la feuille:

Activesheet.Cells.Replace what:=[Source], Replacement:=[Target], LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Voici l'exemple d'un tableau qui va corriger des fautes de frappes

Soit votre objectif est de rechercher dans le classeur et vous effectuez une boucle sur les feuilles. Voici l'exemple d'une macro qui va utiliser le premier tableau de la feuille active comme source d'information pour remplacer les données. La première colonne est le texte cherché et la deuxième colonne est le texte de remplacement.

Sub Prc_ReplaceValuesInSheet()
' This macro scans the 1st table of the active sheet and replaces values from col1 by values in col2
' in entire workbook
    
    Dim Z_Range As Range, Z_Row As Range
    Dim Z_Sheet As Worksheet
    Dim Z_InitialSheet As Worksheet
    Dim Z_Source As String
    Dim Z_Target As String
    Dim Z_Reset As Range
    
    Set Z_InitialSheet = ActiveSheet
    Set Z_Range = Z_InitialSheet.ListObjects(1).DataBodyRange
    Set Z_Reset = Worksheets(1).Range("A1:A1").Find("Reset", LookIn:=xlValues)
    
    For Each Z_Row In Z_Range.Rows
        Z_Source = Z_Row.Cells(1, 1).Value
        Z_Target = Z_Row.Cells(1, 2).Value
        
        For Each Z_Sheet In ActiveWorkbook.Sheets
            If Z_Sheet.Name <> Z_InitialSheet.Name Then ' no replacement in the ActiveSheet
                Z_Sheet.Cells.Replace what:=Z_Source, Replacement:=Z_Target, LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
            End If
        Next Z_Sheet
        
    Next Z_Row
End Sub