XL 2013 VBA - Si cellule contient du texte défini dans une autre cellule

[JessK]

Bonjour,

Je tourne un peu en boucle sans trouver une bonne solution. Je suis débutante en codage VBA pour Excel.
Je dois nettoyer une grande base de données d'adresses, qui ont été mal saisies.
Il devrait avoir que le numéro et la voie de l'adresse, mais dans certains cas, il y a aussi la commune. La commune est connue dans une colonne, mais elle varie en fonction des adresses.
Je cherche donc à identifier si la commune est renseigné dans la cellule "adresse", et si c'est le cas, la remplacer par un vide "". Donc j'aimerais trouver un code qui identifie la commune dans la colonne E et qui puisse la détecter ensuite dans le texte des cellules de la colonne B. J'espère que vous pouvez m'aider à trouver une solution Un grand merci en avance !

En pièce jointe, un fichier exemple.

Voici le code que j'ai écrit mais que ne fonctionne pas :

Sub text_commune()

Dim commune As String

com = Selection.Cells(2, 5).Text

If InStr(1, ActiveSheet.Cells(2, 2), com, vbTextCompare) > 0 Then
    ActiveSheet.Cells(2, 3) = Replace(UCase(ActiveSheet.Cells(2, 2)), com, "")
    Else: ActiveSheet.Cells(2, 3) = ActiveSheet.Cells(2, 2)
    End If

End Sub

 

[vgendron]

Hello

un essai avec ce code

Sub purgeCommune()

Dim TabData() As Variant 'déclaration tablo vba

With Sheets("Feuil1") 'avec la feuille 1
    LastLine = .Range("B" & .Rows.Count).End(xlUp).Row 'dernière ligne NOn vide de la colonne B
    TabData = .Range("B2:G" & LastLine).Value 'on met la plage B2:G4 dans le tablo vba (plus rapide pour le traitement que de travailler directement sur la feuille)
    
    For i = LBound(TabData, 1) To UBound(TabData, 1) 'pour chaque ligne du tablo
        TabData(i, 6) = Trim(WorksheetFunction.Substitute(TabData(i, 1), TabData(i, 4), "")) 'on remplace dans la colonne B, la commune (en colonne E) par du vide, et on trim =suppression des espaces en début et fin de chaine
    Next i
    .Range("B2:G" & LastLine) = TabData 'on colle le résultat dans la feuille
End With
End Sub

 

[JessK]

Merci vgendron pour ta réponse ultra-rapide ! Ca fonctionne et en plus il enlève les espaces vides (pour lesquelles j'avais prévu un code Trim.
J'ai corrigé le range B2:G4, c'est ça que tu voulais mettre? (marqué dans le texte)
Je l'ai testé sur mon fichier excel qui a 24 lignes, et malgré avoir laissé le range sur B1:G4, ça fonctionne.
Du coup, ma question est : est ce qu'il y aura besoin de changer le range pour des feuilles avec beaucoup plus de lignes? J'avoue que je n'ai jamais utilisé TabData.

 

[vgendron]

TabData, c'est juste un nom de variable, tu peux l'appeler Toto si tu veux
pour le nombre de lignes:
LastLine est justement utilisé pour detecter le nombre de lignes de données
dans l'exemple fourni, LastLine=4,
dans ton fichier, ca devient 24

pour expliquer LastLine = .Range("B" & .Rows.Count).End(xlUp).Row

range("B" &rows.count) ==> c'est la DERNIERE ligne de la feuille:
en excel 97, c'était 65536 (c'est pour ca que parfois, on voit encore B65356
sauf qu'à partir d'excel.... le nombre max de lignes d'une feuille est devenu 1million et quelques

donc range("B" &rows.count) c'est la dernière ligne quelque soit la version d'excel
à partir de cette cellule, on remonte vers le haut .end(xlup) (ca "simule" un double clic sur la bordure sup de la dernière cellule (ou ctrl+flèche du haut)
==> ca remonte jusqu'à la dernière cellule NON vide de la colonne
.row pour récuperer le numéro de ligne

ensuite, ce qu'on met dans le tablo vba, c'est la plage qui commence en B2 et qui finit en colonne G"lastline"

==> si tes données restent en colonne B à G==> aucun changement de code à faire
==> si tes données sont déplacées vers la colonne A à F
alors, il faudra changer le code pour avoir TabData = .Range("A2:F" & LastLine).Value


le mieux serait d'avoir toutes tes données sous forme de table structurée==> là.. plus la peine de chercher la position de la table dans la feuille

 

[JessK]

Super, merci pour ces explications, j'ai tout compris
Je vais essayer d'adapter cette fonction Trim et Substitute tout en définissant le range a fur et a mesure. Car dans le reste de mon code j'ai déjà défini la dernière ligne de mon tableau.
Et sinon, je l'intègre comme ça, c'est très bien aussi

 

[vgendron]

pour aller au bout de mes explications, puisque je t'ai parlé de table structurée
en PJ:
deux macros
1) la première, celle que je t'ai déjà donnée==> on doit spécifier la plage de travail en donnant ses cellules de début et fin (B2, et G&lastline)

2) la seconde, travaille sur la table structurée que j'ai nommé "t_Data"
tu remarqueras que dans le code, il n'y a plus aucune référence aux cellules B2 et G&Lastline
on utilise simplement le databodyrange

==> tu peux déplacer la table n'importe ou dans la feuille, le code fonctionnera toujours sans aucun ajustement

avec un peu de lecture pour les tables

Tableaux structurés Excel

Tableaux structurés Excel

fauconnier.developpez.com

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

 

[JessK]

Super merci, tu m'as fait découvrir des nouvelles fonctions très utiles !