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

JessK

XLDnaute Nouveau
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 :

VB:
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
 

Pièces jointes

  • exemple adresses.xlsx
    8.2 KB · Affichages: 3
Solution
Hello

un essai avec ce code

VB:
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"...

vgendron

XLDnaute Barbatruc
Hello

un essai avec ce code

VB:
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

XLDnaute Nouveau
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

XLDnaute Barbatruc
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

XLDnaute Nouveau
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

XLDnaute Barbatruc
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

 

Pièces jointes

  • exemple adresses.xlsm
    20.2 KB · Affichages: 2

Discussions similaires

Réponses
8
Affichages
485

Statistiques des forums

Discussions
313 258
Messages
2 096 613
Membres
106 684
dernier inscrit
ekouepatrick