Sub de remplacement via tableau de correspondance dynamique

Bonsoir à tou(te)s,

Mon objectif : créer une sub qui confronte les valeurs dans ma sélection à une table dans un autre onglet, et effectue un remplacement en cas de correspondance.

Ma progression : j'arrive au résultat voulu, mais via un tableau statique.

Mon problème : quand je tente de déclarer un tableau dynamique, puis de le redimensionner (pour pouvoir ajouter de nouvelles correspondances à l'avenir par exemple, je me confronte à une erreur.

Qu'est-ce qui m'échappe ?

Sub accentuation()

Dim index()

'Dim index (123,1) en statique fonctionne très bien

Dim i As Long
Dim limite As Long

'C'est quand je passe en dynamique et que je tente de redimensionner que ça bloque
ReDim index(UBound(index), 1)

For i = 0 To UBound(index)

        index(i, 0) = Sheets("index_prenoms").Range("A" & i + 2)
        index(i, 1) = Sheets("index_prenoms").Range("B" & i + 2)

For Each cellule In Selection
valeur = cellule.Value

If InStr(cellule.Value, index(i, 0)) > 0 Then
valeur = Replace(valeur, index(i, 0), index(i, 1))
cellule.Value = valeur
End If

Next

Next

End Sub

Merci à vous!

Bonjour Guillaume,

Le redimensionnement d'un tableau à 2 dimension se fait avec 1 To X

Sinon, il ne faut jamais utiliser une variable portant le nom d'une instruction VBA (Index)

Voici le code comme il devrait être

Sub Accentuation()
  Dim MonTab(), Inc As Long
  Dim dLig As Long, Lig As Long
  Dim Cel As Range, Valeur As String
  Dim Pos As Long
  ' Dernière ligne de la table des prénoms
  dLig = Sheets("index_prenoms").Range("A" & Rows.Count).End(xlUp).Row
  ' Dimensionnement du tableau 2 dimensions
  ReDim MonTab(1 To dLig, 1)
  ' Mise en tableau
  MonTab = Sheets("index_prenoms").Range("A2:B" & dLig).Value
  ' Boucle sur les prénoms
  For Each Cel In Selection
    Valeur = Cel.Value
    ' Recherche le prénom dans la 1ère colonne du tableau
    On Error Resume Next
    Pos = 0
    Pos = Application.Match(Valeur, Application.Index(MonTab, , 1), 0)
    On Error GoTo 0
    ' Si prénom trouvé
    If Pos > 0 Then
      Valeur = Replace(Valeur, MonTab(Pos, 1), MonTab(Pos, 2))
      Cel.Value = Valeur
    End If
  Next
End Sub

@+

Bonjour Bruno,

Merci pour ta réponse. Je salue ta pédagogie (bien noté, on ne m'y reprendra plus à nommer une variable "index") et tes commentaires (j'ai presque tout compris du coup, j'ai l'impression d'être un peu moins bête). Et bien entendu, cela fonctionne parfaitement.

Si j'osais, je te demanderai en bonus (et pour continuer à apprendre) :
(1) s'il existe un moyen de "stocker" ce tableau de correspondance dynamique (index_prénoms) pour que ta procédure une fois rapatriée dans personnal soit utilisable à chaque fois que je crée un fichier excel ?

(2) comment je pourrais adapter ce code à la marge pour qu'au lieu d'une correspondance stricte, la reconnaissance d'une partie du contenu de la première colonne suffise à déclencher le remplacement ? j'imagine que ça se passe ici :

Pos = Application.Match(Valeur, Application.Index(MonTab, , 1), 0)

et qu'un InStr serait utile ?

J'ai déjà obtenu la réponse à mes questions, donc c'est vraiment du bonus. Merci néanmoins à celles et ceux qui voudront / pourront m'aider.

Guillaume