Erreur d'exécution '9' : mais fonctionne a chaque fois que je relance
Bonjour à tous,
Je viens vers vous car j'ai un soucis et je ne comprends pas pourquoi ça ne veut pas fonctionner.
Mon code à pour but de récupérer mon texte d'une cellule de vérifier si il fait plus ou moins de 38 caractères puis si il fait plus de récupérer le surplus et de le coller dans le cellule à côté.
Cependant quand je le lance j'ai ce message d'erreur qui arrive "Erreur d'exécution '9' : L'indice n'appartient pas à la selection" qui vient aléatoirement ligne 40 ou ligne 45
Sub decoupe()
'Déclaration des premières variables
Dim bonneadresse As Boolean
Dim nbLignes
Dim nombre
'Récupération du nombre de ligne que contient mon fichier
Range("A2").Select
nbLignes = Range("A2", Selection.End(xlDown)).Cells.Count
'A1 pour trouver ma bonne appelation ici : complement de nom
Range("A1").Select
'Boucle tant que je n'ai pas True je continue
Do Until bonneadresse = True
'Décalage de 1 a 1 vers la droite
ActiveCell.Offset(0, 1).Select
'Si bonne appelation je rentre dans mon SI
If ActiveCell.Value = "complement de nom" Then
'Boucle sur le nombre totale de mes lignes
For nombre = 1 To nbLignes
'Déclaration des autres variables
Dim a
Dim result
Dim tmp
Dim i
Dim phrase
Dim NCarMax
Dim valeurcellule1
Dim valeurcellule2
Dim Tableau
Dim valeurcellulesipascopy
'Décalage de 1 a 1 vers le bas
ActiveCell.Offset(1, 0).Select
'Récupération de la valeur de la cellule et mis en place de mon nombre max de caractères
phrase = ActiveCell.Value
NCarMax = 38
'Si ma phrase est supéreur à mon nombre max de caractere > rentre dans ma condition
If Len(phrase) > NCarMax Then
'Découpe ma valeur de ma cellule
a = Split(Left(phrase, NCarMax + 1))
' result = a(i) => est l'une des lignes qui fait planter
result = a(i)
i = 1
'Tant que result n'est pas supérieur à mon nombre max de caractères je continu
Do While Len(result) <= NCarMax
' tmp = result & " " & a(i) => est l'une des lignes qui fait planter
tmp = result & " " & a(i)
If Len(tmp) > NCarMax Then
Exit Do
Else
result = tmp
i = i + 1
End If
Loop
'Récupération de la phrase en trop
valeurcellule1 = Len(phrase)
valeurcellule2 = InStr(phrase, result)
Tableau = Split(phrase, result)
ActiveCell.Offset(0, 1).Select
'Si ma cellule à côté est pleine alors pas de copié/collé
If ActiveCell.Value <> "" Then
ActiveCell.Offset(0, -1).Select
'Sinon copié/collé de ma valeur puis je reviens sur la cellule principale
Else
ActiveCell.Value = Tableau(1)
ActiveCell.Offset(0, -1).Select
ActiveCell.Value = result
End If
Else
'Sinon je colle ma valeur puis je reviens sur la cellule principale
result = phrase
End If
bonneadresse = True
Next nombre
Else
bonneadresse = False
End If
Loop
End SubDe plus si je clique sur "Réinitialiser" puis "Continuer" mon copier/coller fonctionne normalement pour replanter ligne suivante...
Je débute dans le VBA désolé du dérangement !
Les colonnes concernées "complement de nom" et "Adresse 1".
Merci par avance
Bonjour,
Il y a pas mal de choses à revoir :
- Déclaration de variables non typées (donc inutiles) et pas toutes en début de macro (pourtant fortement conseillé)
- Utilisation de "Activate" et "Select", qui alourdissent le code et son source de problème alors qu'on peut s'en passer
- Split est normalement utilisé pour séparer une chaîne de caractères en fonction d'un séparateur défini : utilisation à revoir dans ton cas
- Pas besoin de contrôler une variable booléenne comme ceci :
If bonneadresse = True Then...Il suffit d'écrireIf bonneadresse Then...
Je te propose de partir d'une feuille blanche, ou plutôt de ton fichier exemple.
bonjour
salut Pedro au passage
pourquoi pas une formule ?
amitiés à tous
Je ne comprend pas bien ce que tu as essayé de faire. Pour être plus clair :
- Si une cellule contient plus de 38 caractères, tu reportes la partie supérieure à 38 caractères en cellule de droite ?
- Si la partie à reporter comporte plus de 38 caractères, tu reportes encore le reste à droite et ainsi de suite ?
edit : salut jmd !
Bonjour,
Il y a pas mal de choses à revoir :
- Déclaration de variables non typées (donc inutiles) et pas toutes en début de macro (pourtant fortement conseillé)
D'accord j'ai mis toutes mes déclarations de variables au début avec leur attribution.
- Utilisation de "Activate" et "Select", qui alourdissent le code et son source de problème alors qu'on peut s'en passer
Par quoi puis-je les remplacer ?
- Split est normalement utilisé pour séparer une chaîne de caractères en fonction d'un séparateur défini : utilisation à revoir dans ton cas
Le premier split découpe en fonction des espaces et le deuxième découpe après ma chaîne de caractère. Je ne vois pas comment faire autrement
- Pas besoin de contrôler une variable booléenne comme ceci :
If bonneadresse = True Then...Il suffit d'écrireIf bonneadresse Then...
D'accord modification faite
Je ne comprend pas bien ce que tu as essayé de faire. Pour être plus clair :
- Si une cellule contient plus de 38 caractères, tu reportes la partie supérieure à 38 caractères en cellule de droite ?
- Si la partie à reporter comporte plus de 38 caractères, tu reportes encore le reste à droite et ainsi de suite ?
edit : salut jmd !
Je fais de la gestion de fichiers pour du routage nous avons comme limitation par la poste de 38 caractère sinon ça nous coupe l'information. Nous avons aussi comme indication comme quoi si le complément de nom est supérieur à 38 caractères ET que l'Adresse 1 (champ suivant) est vide alors je colle qu'elle que soit mon nombre de caractère. Sinon si mon champ est plein alors je ne fais rien.
Code mis à jour
Sub decoupe()
'Déclaration des premières variables
Dim bonneadresse As Boolean
Dim nbLignes As Long
Dim nombre As Long
Dim a() As String
Dim result As String
Dim tmp As String
Dim i As Byte
Dim phrase As String
Dim NCarMax As Byte
Dim valeurcellule1 As Byte
Dim valeurcellule2 As Byte
Dim Tableau() As String
'Récupération du nombre de ligne que contient mon fichier
Range("A2").Select
nbLignes = Range("A2", Selection.End(xlDown)).Cells.Count
'A1 pour trouver ma bonne appelation ici : complement de nom
Range("A1").Select
'Boucle tant que je n'ai pas True je continue
Do Until bonneadresse
'Décalage de 1 a 1 vers la droite
ActiveCell.Offset(0, 1).Select
'Si bonne appelation je rentre dans mon SI
If ActiveCell.Value = "complement de nom" Then
'Boucle sur le nombre totale de mes lignes
For nombre = 1 To nbLignes
'Décalage de 1 a 1 vers le bas
ActiveCell.Offset(1, 0).Select
'Récupération de la valeur de la cellule et mis en place de mon nombre max de caractères
phrase = ActiveCell.Value
NCarMax = 38
'Si ma phrase est supéreur à mon nombre max de caractere > rentre dans ma condition
If Len(phrase) > NCarMax Then
'Découpe ma valeur de ma cellule
a = Split(Left(phrase, NCarMax + 1))
' result = a(i) => est l'une des lignes qui fait planter
result = a(i)
i = 1
'Tant que result n'est pas supérieur à mon nombre max de caractères je continu
Do While Len(result) <= NCarMax
' tmp = result & " " & a(i) => est l'une des lignes qui fait planter
tmp = result & " " & a(i)
If Len(tmp) > NCarMax Then
Exit Do
Else
result = tmp
i = i + 1
End If
Loop
'Récupération de la phrase en trop
valeurcellule1 = Len(phrase)
valeurcellule2 = InStr(phrase, result)
Tableau = Split(phrase, result)
ActiveCell.Offset(0, 1).Select
'Si ma cellule à côté est pleine alors pas de copié/collé
If ActiveCell.Value <> "" Then
ActiveCell.Offset(0, -1).Select
'Sinon copié/collé de ma valeur puis je reviens sur la cellule principale
Else
ActiveCell.Value = Tableau(1)
ActiveCell.Offset(0, -1).Select
ActiveCell.Value = result
End If
Else
result = phrase
End If
bonneadresse = True
Next nombre
Else
bonneadresse = False
End If
Loop
End SubBjr tout le monde,
à tester
Sub decoupe()
'Déclaration des premières variables
Dim bonneadresse As Boolean
Dim nbLignes
Dim nombre
'Récupération du nombre de ligne que contient mon fichier
Range("A2").Select
nbLignes = Range("A2", Selection.End(xlDown)).Cells.Count
'A1 pour trouver ma bonne appelation ici : complement de nom
Range("A1").Select
'Boucle tant que je n'ai pas True je continue
Do Until bonneadresse = True
'Décalage de 1 a 1 vers la droite
ActiveCell.Offset(0, 1).Select
'Si bonne appelation je rentre dans mon SI
If ActiveCell.Value = "complement de nom" Then
'Boucle sur le nombre totale de mes lignes
For nombre = 1 To nbLignes
'Déclaration des autres variables
Dim a
Dim result
Dim tmp
Dim i
Dim phrase
Dim NCarMax
Dim valeurcellule1
Dim valeurcellule2
Dim Tableau
Dim valeurcellulesipascopy
'Décalage de 1 a 1 vers le bas
ActiveCell.Offset(1, 0).Select
'Récupération de la valeur de la cellule et mis en place de mon nombre max de caractères
phrase = ActiveCell.Value
NCarMax = 38
'Si ma phrase est supéreur à mon nombre max de caractere > rentre dans ma condition
If Len(phrase) > NCarMax Then
'Découpe ma valeur de ma cellule
a = Split(Left(phrase, NCarMax + 1))
' result = a(i) => est l'une des lignes qui fait planter
result = a(i)
i = 1
'Tant que result n'est pas supérieur à mon nombre max de caractères je continu
Do While Len(result) <= NCarMax
' tmp = result & " " & a(i) => est l'une des lignes qui fait planter
tmp = result & " " & a(i)
If Len(tmp) > NCarMax Then
Exit Do
Else
result = tmp
i = i + 1
End If
Loop
i = 0
'Récupération de la phrase en trop
valeurcellule1 = Len(phrase)
valeurcellule2 = InStr(phrase, result)
Tableau = Split(phrase, result)
ActiveCell.Offset(0, 1).Select
'Si ma cellule à côté est pleine alors pas de copié/collé
If ActiveCell.Value <> "" Then
ActiveCell.Offset(0, -1).Select
'Sinon copié/collé de ma valeur puis je reviens sur la cellule principale
Else
ActiveCell.Value = Tableau(1)
ActiveCell.Offset(0, -1).Select
ActiveCell.Value = result
End If
Else
'Sinon je colle ma valeur puis je reviens sur la cellule principale
result = phrase
End If
bonneadresse = True
Next nombre
Else
bonneadresse = False
End If
Loop
End Subbonjour,
sans avoir essayé de comprendre ce que tu cherches à faire. tu oublies d'(re)initialiser ta variable i.
Essaie ceci
i = 0
a = Split(Left(phrase, NCarMax + 1))Bjr tout le monde,
à tester
CodeSub decoupe() 'Déclaration des premières variables Dim bonneadresse As Boolean Dim nbLignes Dim nombre 'Récupération du nombre de ligne que contient mon fichier Range("A2").Select nbLignes = Range("A2", Selection.End(xlDown)).Cells.Count 'A1 pour trouver ma bonne appelation ici : complement de nom Range("A1").Select 'Boucle tant que je n'ai pas True je continue Do Until bonneadresse = True 'Décalage de 1 a 1 vers la droite ActiveCell.Offset(0, 1).Select 'Si bonne appelation je rentre dans mon SI If ActiveCell.Value = "complement de nom" Then 'Boucle sur le nombre totale de mes lignes For nombre = 1 To nbLignes 'Déclaration des autres variables Dim a Dim result Dim tmp Dim i Dim phrase Dim NCarMax Dim valeurcellule1 Dim valeurcellule2 Dim Tableau Dim valeurcellulesipascopy 'Décalage de 1 a 1 vers le bas ActiveCell.Offset(1, 0).Select 'Récupération de la valeur de la cellule et mis en place de mon nombre max de caractères phrase = ActiveCell.Value NCarMax = 38 'Si ma phrase est supéreur à mon nombre max de caractere > rentre dans ma condition If Len(phrase) > NCarMax Then 'Découpe ma valeur de ma cellule a = Split(Left(phrase, NCarMax + 1)) ' result = a(i) => est l'une des lignes qui fait planter result = a(i) i = 1 'Tant que result n'est pas supérieur à mon nombre max de caractères je continu Do While Len(result) <= NCarMax ' tmp = result & " " & a(i) => est l'une des lignes qui fait planter tmp = result & " " & a(i) If Len(tmp) > NCarMax Then Exit Do Else result = tmp i = i + 1 End If Loop i = 0 'Récupération de la phrase en trop valeurcellule1 = Len(phrase) valeurcellule2 = InStr(phrase, result) Tableau = Split(phrase, result) ActiveCell.Offset(0, 1).Select 'Si ma cellule à côté est pleine alors pas de copié/collé If ActiveCell.Value <> "" Then ActiveCell.Offset(0, -1).Select 'Sinon copié/collé de ma valeur puis je reviens sur la cellule principale Else ActiveCell.Value = Tableau(1) ActiveCell.Offset(0, -1).Select ActiveCell.Value = result End If Else 'Sinon je colle ma valeur puis je reviens sur la cellule principale result = phrase End If bonneadresse = True Next nombre Else bonneadresse = False End If Loop End Sub
J'ai copié collé et ça fonctionne ! Mais je ne comprends pas ce que tu as modifié
Edit :
J'ai trouvé le
i = 0bonjour,
sans avoir essayé de comprendre ce que tu cherches à faire. tu oublies d'(re)initialiser ta variable i.
Essaie ceci
i = 0 a = Split(Left(phrase, NCarMax + 1))
C'est ce qu'a rajouté m3ellem1 et ça fonctionne !
je suis vraiment bête
Merci à tous ! Vous êtes au top !
Serait-il possible de savoir ce que je peux améliorer (et comment si possible) ?
bonjour
salut Pedro au passage
pourquoi pas une formule ?
amitiés à tous
Le but est de créer une application qui récupère le fichier excel à modifier puis de lancer les différents modules.
en quoi une formule serait-elle incompatible ?
Voilà 2 propositions de macro :
Avec report tant que l'excédent de phrase dépasse 38 caractères :
Sub decoupeMod38()
'Déclaration des variables
Dim nbLignes As Long, i As Long
Dim NCarMax As Integer, Col As Integer
Dim phrase As String
NCarMax = 38
With Sheets("Feuil1") 'Toute instruction commençant par "." s'y rattache
nbLignes = .Range("A" & .Rows.Count).End(xlUp).Row 'Dernière ligne à traiter
For i = 2 To nbLignes 'Boucle sur les lignes
Col = .Range("E" & i).Column 'Détermination de la colonne de travail
phrase = .Cells(i, Col).Value 'Affectation de la phrase dans une variable
Do While Len(phrase) > NCarMax 'Tant que la phrase excède 38 caractères, on reporte à droite
Col = Col + 1 'Colonne de report
If Not IsEmpty(.Cells(i, Col)) Then Exit Do 'Arrêt ici si cellule de droite non vide
.Cells(i, Col) = Mid(phrase, NCarMax + 1, Len(phrase) - NCarMax) 'On reporte a partir du 39ème caractère jusqu'au dernier
.Cells(i, Col - 1) = Left(phrase, NCarMax) 'On ne conserve dans la cellule de gauche que les 38 premiers caractères
phrase = .Cells(i, Col).Value 'On attribue la partie reportée dans la variable "phrase"
Loop
Next i
End With
End SubAvec report unique de l'ensemble de l’excédent de phrase (>38 caractères) :
Sub decoupe38()
'Déclaration des variables
Dim nbLignes As Long, i As Long
Dim NCarMax As Integer
Dim phrase As String
NCarMax = 38
With Sheets("Feuil1") 'Toute instruction commençant par "." s'y rattache
nbLignes = .Range("A" & .Rows.Count).End(xlUp).Row 'Dernière ligne à traiter
For i = 2 To nbLignes 'Boucle sur les lignes
phrase = .Range("E" & i).Value
If Len(phrase) > NCarMax Then 'Comparaison de la longueur de phrase avec le maximum autorisé
If IsEmpty(.Range("F" & i)) Then .Range("F" & i) = Mid(phrase, NCarMax + 1, Len(phrase) - NCarMax) 'On reporte a partir du 39ème caractère jusqu'au dernier si la cellule de droite est vide
.Range("E" & i) = Left(phrase, NCarMax) 'On ne conserve dans la cellule de gauche que les 38 premiers caractères
End If
Next i
End With
End SubEn espérant avoir bien compris la problématique !
bonjour
salut Pedro au passage
pourquoi pas une formule ?
amitiés à tous
Le but est de créer une application qui récupère le fichier excel à modifier puis de lancer les différents modules.
en quoi une formule serait-elle incompatible ?
Je ne sais pas à vrai dire :/
Voilà 2 propositions de macro :
Avec report tant que l'excédent de phrase dépasse 38 caractères :
Code 1Sub decoupeMod38() 'Déclaration des variables Dim nbLignes As Long, i As Long Dim NCarMax As Integer, Col As Integer Dim phrase As String NCarMax = 38 With Sheets("Feuil1") 'Toute instruction commençant par "." s'y rattache nbLignes = .Range("A" & .Rows.Count).End(xlUp).Row 'Dernière ligne à traiter For i = 2 To nbLignes 'Boucle sur les lignes Col = .Range("E" & i).Column 'Détermination de la colonne de travail phrase = .Cells(i, Col).Value 'Affectation de la phrase dans une variable Do While Len(phrase) > NCarMax 'Tant que la phrase excède 38 caractères, on reporte à droite Col = Col + 1 'Colonne de report If Not IsEmpty(.Cells(i, Col)) Then Exit Do 'Arrêt ici si cellule de droite non vide .Cells(i, Col) = Mid(phrase, NCarMax + 1, Len(phrase) - NCarMax) 'On reporte a partir du 39ème caractère jusqu'au dernier .Cells(i, Col - 1) = Left(phrase, NCarMax) 'On ne conserve dans la cellule de gauche que les 38 premiers caractères phrase = .Cells(i, Col).Value 'On attribue la partie reportée dans la variable "phrase" Loop Next i End With End SubAvec report unique de l'ensemble de l’excédent de phrase (>38 caractères) :
Sub decoupe38() 'Déclaration des variables Dim nbLignes As Long, i As Long Dim NCarMax As Integer Dim phrase As String NCarMax = 38 With Sheets("Feuil1") 'Toute instruction commençant par "." s'y rattache nbLignes = .Range("A" & .Rows.Count).End(xlUp).Row 'Dernière ligne à traiter For i = 2 To nbLignes 'Boucle sur les lignes phrase = .Range("E" & i).Value If Len(phrase) > NCarMax Then 'Comparaison de la longueur de phrase avec le maximum autorisé If IsEmpty(.Range("F" & i)) Then .Range("F" & i) = Mid(phrase, NCarMax + 1, Len(phrase) - NCarMax) 'On reporte a partir du 39ème caractère jusqu'au dernier si la cellule de droite est vide .Range("E" & i) = Left(phrase, NCarMax) 'On ne conserve dans la cellule de gauche que les 38 premiers caractères End If Next i End With End SubEn espérant avoir bien compris la problématique !
Ha oui c'est beaucoup plus simple et rapide !! Petit hic (j'ai pris le deuxième bout de code) j'avais mis mon bout de code pour ne pas couper mes mots (sinon c'est moche)
Do While Len(result) <= NCarMax
' tmp = result & " " & a(i) => est l'une des lignes qui fait planter
tmp = result & " " & a(i)
If Len(tmp) > NCarMax Then
Exit Do
Else
result = tmp
i = i + 1
End If
LoopComment puis je l'intégrer à ton code ?
Ha oui c'est beaucoup plus simple et rapide !! Petit hic (j'ai pris le deuxième bout de code) j'avais mis mon bout de code pour ne pas couper mes mots (sinon c'est moche)
Comment puis je l'intégrer à ton code ?
Je vois pas ce que tu veux dire, peux-tu l'illustrer avec un ou deux exemple(s) ?
Bonjour le fil,
Effectivement il y avait beaucoup de chose à revoir car pas essentielles
Voilà ton code optimisé à tester
Option Explicit
Sub Decoupe()
'Déclaration des premières variables
Dim Col As Long, Lig As Long, DerLig As Long
Dim Rng As Range
Dim Inc As Integer, Ind As Integer, NbCarMAx As Integer
Dim Phrase As String, Result() As String, sTmp As String
'Récupération du nombre de ligne que contient mon fichier
DerLig = Range("A2").End(xlDown).Row
' Colonne à traitée
Col = ColFind("Feuil1", 1, "complement de nom")
' Pour chaque ligne à traiter
For Lig = 2 To DerLig
' Définir la celluel à traiter
Set Rng = Cells(Lig, Col)
' Vérifier si la cellule contient une valeur, sinon on passe à la suivante
If Rng.Value = "" Then GoTo SuiteLigne
' Sinon Récupération de la valeur de la cellule et mis en place de mon nombre max de caractères
Phrase = Rng.Value
NbCarMAx = 38
'Si ma phrase est supéreur à mon nombre max de caractere > rentre dans ma condition
Do While Len(Phrase) > NbCarMAx
' Incrémenter le tableau
Ind = Ind + 1
' Redimensionner le tableau
ReDim Preserve Result(Ind)
' Décopuer la valeur en focntion du dernier espace
sTmp = Left(Phrase, NbCarMAx)
sTmp = Left(sTmp, InStrRev(sTmp, " "))
' Enregistrer la valeur
Result(Ind) = sTmp
' Suite de la phrase
Phrase = Mid(Phrase, Len(sTmp) + 1)
Loop
' On récupère le dernbier morceau si existe
If Phrase <> "" Then
' Incrémenter et redimensionner le tableau
Ind = Ind + 1
ReDim Preserve Result(Ind)
Result(Ind) = Phrase
End If
' Pour chaque
For Inc = 1 To Ind
Rng.Offset(0, Inc - 1).Value = Result(Inc)
Next Inc
' Efface le tableau
ReDim Result(0)
Ind = 0
SuiteLigne:
Next Lig
' Effacer les variables objet pour libérer la mémoire
Set Rng = Nothing
End Sub
Function ColFind(sFeuil As String, NumLig As Integer, Quoi As Variant)
On Error Resume Next
With Sheets(sFeuil).Rows(NumLig)
ColFind = 0
ColFind = .Find(What:=Quoi, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False).Column
End With
On Error GoTo 0
End FunctionA+
Ha oui c'est beaucoup plus simple et rapide !! Petit hic (j'ai pris le deuxième bout de code) j'avais mis mon bout de code pour ne pas couper mes mots (sinon c'est moche)
Comment puis je l'intégrer à ton code ?
Je vois pas ce que tu veux dire, peux-tu l'illustrer avec un ou deux exemple(s) ?
Alors BrunoM45 m'a donné la solution avec mon code
Mais pour expliquer si j'ai "INRA - Unité des Recherches Forestières Méditerranéennes"
38 caractères : "INRA - Unité des Recherches Forestière"
Coupe : "s Méditerranéennes"
Il faut donc que j'ai : "INRA - Unité des Recherches"
Puis : ''Forestières Méditerranéennes"
Je ne sais pas à vrai dire :/
une formule de ce genre n'est pas simple, mais elle est certainement plus facile que du code, et sera plus fiable dans le temps
amitiés
Ha oui c'est beaucoup plus simple et rapide !! Petit hic (j'ai pris le deuxième bout de code) j'avais mis mon bout de code pour ne pas couper mes mots (sinon c'est moche)
Comment puis je l'intégrer à ton code ?
Je vois pas ce que tu veux dire, peux-tu l'illustrer avec un ou deux exemple(s) ?
Salut Bruno,
Avec ton post je viens de comprendre la question précédente...
On peut donc effectivement déterminer où couper entre 2 mots juste avant d'atteindre 38 caractères grâce à :
NCarMax-InStrRev(Left(phrase, NCarMax), " ")InStr recherche la 1ère position d'un élément (par exemple un espace) dans une chaîne de caractères. Pour avoir la dernière, il suffit d'inverser la chaine avec StrRev (comme "reverse").