Logo GNT
Actualités Tests & Guides Bons Plans
Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11
Entraide Applications Microsoft Office Excel formule trop longue...

formule trop longue...

12 réponses
Avatar
j
bonjour,

j'ai, dans Excel (je sais bien, c'est pas fait pour ça) une (grosse) base de
données contenant uniquement du texte.
Mais, dans certaines cellules, de... (très) longs textes.
Jusque là, tout va bien.

Mais si malencontreusement je m'avise de vouloir remplacer "toto" par "zaza"
par VBA dans tout le texte de toute la page (avec cells.replace), je
constate que VBA
1) fait bien son boulot tant qu'il ne rencontre pas une case trop remplie
2) arrête tout bonnement de remplacer à partir de la première cellule trop
pleine contenant un "toto"... et ce sans aucun avertissement d'erreur !

(Si je fait la même chose avec Edition / remplacer, message d'erreur
"formule trop longue" (en fait de formule je n'ai que du texte).)

Quelqu'un a-t-il une solution à ce pb désagréable ?
J'ai bien trouvé une solution VBA bancale avec Instr, left et right, mais
c'est beaucoup trop lent pour ce que je veux faire (j'ai plusieurs mots à
remplacer dans 20 000 lignes...).

d'avance merci de vos avis éclairés

j@c
http://jacxl.free.fr
Signaler un problème avec ce contenu

10 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
1 2
Avatar
Frédéric Sigonneau
Salut ,

Je crains que ce ne soit assez long également (ça dépendra de ce qu'il y a
exactement dans tes cellules), même si passer par un tableau permet des
traitements en principe rapides :

Sub test()
For i = 1 To 30
For j = 1 To 15
x = Split(Cells(i, j).Value, "titi")
Cells(i, j).Formula = Join(x, "zaza")
Next j
Next i
End Sub

(pour 30 lignes et 15 colonnes contenant au total 1 700 000 caractères : 7s ).

FS
---
Frédéric Sigonneau
http://frederic.sigonneau.free.fr

bonjour,

j'ai, dans Excel (je sais bien, c'est pas fait pour ça) une (grosse) base de
données contenant uniquement du texte.
Mais, dans certaines cellules, de... (très) longs textes.
Jusque là, tout va bien.

Mais si malencontreusement je m'avise de vouloir remplacer "toto" par "zaza"
par VBA dans tout le texte de toute la page (avec cells.replace), je
constate que VBA
1) fait bien son boulot tant qu'il ne rencontre pas une case trop remplie
2) arrête tout bonnement de remplacer à partir de la première cellule trop
pleine contenant un "toto"... et ce sans aucun avertissement d'erreur !

(Si je fait la même chose avec Edition / remplacer, message d'erreur
"formule trop longue" (en fait de formule je n'ai que du texte).)

Quelqu'un a-t-il une solution à ce pb désagréable ?
J'ai bien trouvé une solution VBA bancale avec Instr, left et right, mais
c'est beaucoup trop lent pour ce que je veux faire (j'ai plusieurs mots à
remplacer dans 20 000 lignes...).

d'avance merci de vos avis éclairés


http://jacxl.free.fr







Avatar
j
merci Frédéric
tout dépend du micro, mais 7 s pour 450 cellules, ça doit être à peu près
équivalent à ce que j'ai avec Droite + Gauche + Instr (je vais vérifier)
J'ai pu gagner du temps en utilisant Find avant d'appliquer la fonction de
remplacement, mais ça reste encore trop lent !
(enfin, ça tourne au bureau en ce moment pour la nuit, ça devrait être cuit
demain matin !)
pour le tableau, j'y ai bien pensé, mais j'ai reculé, vu le volume de
données à charger dans le tableau !



"Frédéric Sigonneau" a écrit dans le message de news:

Salut ,

Je crains que ce ne soit assez long également (ça dépendra de ce qu'il y a
exactement dans tes cellules), même si passer par un tableau permet des
traitements en principe rapides :

Sub test()
For i = 1 To 30
For j = 1 To 15
x = Split(Cells(i, j).Value, "titi")
Cells(i, j).Formula = Join(x, "zaza")
Next j
Next i
End Sub

(pour 30 lignes et 15 colonnes contenant au total 1 700 000 caractères :
7s ).


FS
---
Frédéric Sigonneau
http://frederic.sigonneau.free.fr

bonjour,

j'ai, dans Excel (je sais bien, c'est pas fait pour ça) une (grosse)
base de


données contenant uniquement du texte.
Mais, dans certaines cellules, de... (très) longs textes.
Jusque là, tout va bien.

Mais si malencontreusement je m'avise de vouloir remplacer "toto" par
"zaza"


par VBA dans tout le texte de toute la page (avec cells.replace), je
constate que VBA
1) fait bien son boulot tant qu'il ne rencontre pas une case trop
remplie


2) arrête tout bonnement de remplacer à partir de la première cellule
trop


pleine contenant un "toto"... et ce sans aucun avertissement d'erreur !

(Si je fait la même chose avec Edition / remplacer, message d'erreur
"formule trop longue" (en fait de formule je n'ai que du texte).)

Quelqu'un a-t-il une solution à ce pb désagréable ?
J'ai bien trouvé une solution VBA bancale avec Instr, left et right,
mais


c'est beaucoup trop lent pour ce que je veux faire (j'ai plusieurs mots
à


remplacer dans 20 000 lignes...).

d'avance merci de vos avis éclairés


http://jacxl.free.fr









Avatar
MichDenis
Pour remplacer toto par tata
'--------------------------
Sub test()

Dim V As Range, Are As Range
With Feuil1
Set V = .UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
End With
For Each Are In V.Areas
t = Are
For a = 1 To UBound(t, 1)
For b = 1 To UBound(t, 2)
Are(a, b).Value = Application.Substitute(t(a, b), "toto", "tata")
Next
Next
Next

End Sub
''----------------------------------------



"" a écrit dans le message de news:
bonjour,

j'ai, dans Excel (je sais bien, c'est pas fait pour ça) une (grosse) base de
données contenant uniquement du texte.
Mais, dans certaines cellules, de... (très) longs textes.
Jusque là, tout va bien.

Mais si malencontreusement je m'avise de vouloir remplacer "toto" par "zaza"
par VBA dans tout le texte de toute la page (avec cells.replace), je
constate que VBA
1) fait bien son boulot tant qu'il ne rencontre pas une case trop remplie
2) arrête tout bonnement de remplacer à partir de la première cellule trop
pleine contenant un "toto"... et ce sans aucun avertissement d'erreur !

(Si je fait la même chose avec Edition / remplacer, message d'erreur
"formule trop longue" (en fait de formule je n'ai que du texte).)

Quelqu'un a-t-il une solution à ce pb désagréable ?
J'ai bien trouvé une solution VBA bancale avec Instr, left et right, mais
c'est beaucoup trop lent pour ce que je veux faire (j'ai plusieurs mots à
remplacer dans 20 000 lignes...).

d'avance merci de vos avis éclairés


http://jacxl.free.fr
Avatar
j
sorry, pas tout compris
Dans mon cas, l'utilisation des Areas ne doit pas apporter grand chose,
parce que la plage de cellule est bien rectangulaire et completement
remplie.
Pour ce qui est de substitute, je ne l'avais pas retenu parce que je ne
voulais pas tenir compte des majuscules (mais le problème vient surtout du
balayage cellule par cellule qui est trop long)
En outre, je ne comprends pas pourquoi, mais je bloque sur une
"incompatibilité de type" avec le Ubound...
Merci



"MichDenis" a écrit dans le message de news:
#
Pour remplacer toto par tata
'--------------------------
Sub test()

Dim V As Range, Are As Range
With Feuil1
Set V = .UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
End With
For Each Are In V.Areas
t = Are
For a = 1 To UBound(t, 1)
For b = 1 To UBound(t, 2)
Are(a, b).Value = Application.Substitute(t(a, b), "toto",
"tata")

Next
Next
Next

End Sub
''----------------------------------------



"" a écrit dans le message de news:


bonjour,

j'ai, dans Excel (je sais bien, c'est pas fait pour ça) une (grosse) base
de

données contenant uniquement du texte.
Mais, dans certaines cellules, de... (très) longs textes.
Jusque là, tout va bien.

Mais si malencontreusement je m'avise de vouloir remplacer "toto" par
"zaza"

par VBA dans tout le texte de toute la page (avec cells.replace), je
constate que VBA
1) fait bien son boulot tant qu'il ne rencontre pas une case trop remplie
2) arrête tout bonnement de remplacer à partir de la première cellule trop
pleine contenant un "toto"... et ce sans aucun avertissement d'erreur !

(Si je fait la même chose avec Edition / remplacer, message d'erreur
"formule trop longue" (en fait de formule je n'ai que du texte).)

Quelqu'un a-t-il une solution à ce pb désagréable ?
J'ai bien trouvé une solution VBA bancale avec Instr, left et right, mais
c'est beaucoup trop lent pour ce que je veux faire (j'ai plusieurs mots à
remplacer dans 20 000 lignes...).

d'avance merci de vos avis éclairés


http://jacxl.free.fr








Avatar
MichDenis
| "incompatibilité de type" avec le Ubound...
*** ta plage doit avoir au moins 2 cellules

| Pour ce qui est de substit
*** tu peux utiliser "Replace" si tu désires

| pas tenir compte des majuscules
*** dans la procédure tu peux utiliser Lcase() ou Ucase()

| le problème vient surtout du balayage cellule par cellule qui est trop long)
*** As-tu essayé, c'est la méthode la plus rapide que je connaisse !

'----------------------------
Sub test()

Dim V As Range
Dim a As Long, b As Long

With Feuil1
Set V = .UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
End With

t = V
Application.ScreenUpdating = False
For a = 1 To UBound(t, 1)
For b = 1 To UBound(t, 2)
V(a, b).Value = Replace(t(a, b), UCase("tata"), "TOTO")
Next
Next
End Sub
'----------------------------



"" a écrit dans le message de news:
sorry, pas tout compris
Dans mon cas, l'utilisation des Areas ne doit pas apporter grand chose,
parce que la plage de cellule est bien rectangulaire et completement
remplie.
Pour ce qui est de substitute, je ne l'avais pas retenu parce que je ne
voulais pas tenir compte des majuscules (mais le problème vient surtout du
balayage cellule par cellule qui est trop long)
En outre, je ne comprends pas pourquoi, mais je bloque sur une
"incompatibilité de type" avec le Ubound...
Merci



"MichDenis" a écrit dans le message de news:
#
Pour remplacer toto par tata
'--------------------------
Sub test()

Dim V As Range, Are As Range
With Feuil1
Set V = .UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
End With
For Each Are In V.Areas
t = Are
For a = 1 To UBound(t, 1)
For b = 1 To UBound(t, 2)
Are(a, b).Value = Application.Substitute(t(a, b), "toto",
"tata")

Next
Next
Next

End Sub
''----------------------------------------



"" a écrit dans le message de news:


bonjour,

j'ai, dans Excel (je sais bien, c'est pas fait pour ça) une (grosse) base
de

données contenant uniquement du texte.
Mais, dans certaines cellules, de... (très) longs textes.
Jusque là, tout va bien.

Mais si malencontreusement je m'avise de vouloir remplacer "toto" par
"zaza"

par VBA dans tout le texte de toute la page (avec cells.replace), je
constate que VBA
1) fait bien son boulot tant qu'il ne rencontre pas une case trop remplie
2) arrête tout bonnement de remplacer à partir de la première cellule trop
pleine contenant un "toto"... et ce sans aucun avertissement d'erreur !

(Si je fait la même chose avec Edition / remplacer, message d'erreur
"formule trop longue" (en fait de formule je n'ai que du texte).)

Quelqu'un a-t-il une solution à ce pb désagréable ?
J'ai bien trouvé une solution VBA bancale avec Instr, left et right, mais
c'est beaucoup trop lent pour ce que je veux faire (j'ai plusieurs mots à
remplacer dans 20 000 lignes...).

d'avance merci de vos avis éclairés


http://jacxl.free.fr








Avatar
j
----- Original Message -----
From: "MichDenis"


| Pour ce qui est de substit
*** tu peux utiliser "Replace" si tu désires
Ben, c'est justement ce qui ne fonctionne pas si le texte de la cellule est

trop long... (cf mon message initial)


| pas tenir compte des majuscules
*** dans la procédure tu peux utiliser Lcase() ou Ucase()
C'est ce que je fais actuellement, mais comme je souhaite remplacer "toto"

ou "Toto" ou "TOTO" ou "tOto" ou "TOto"... par "zaza" sans modifier la casse
du reste du texte, et que je ne veux pas encore alourdir le code, j'utilise
instr+left+right


| le problème vient surtout du balayage cellule par cellule qui est trop
long)

*** As-tu essayé, c'est la méthode la plus rapide que je connaisse !
Je n'arrête pas de balayer, mais quand je n'ai pas le problème des textes

trop longs qui genent Replace, l'utilisation de Replace sur une grande plage
évite de tester cellule après cellule et est beaucoup plus rapide (ne traite
que les cellules qui contiennent effectivement "toto". Comme il y en a peu,
l'amélioration est spectaculaire)

merci


http://jacxl.free.fr





'----------------------------
Sub test()

Dim V As Range
Dim a As Long, b As Long

With Feuil1
Set V = .UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
End With

t = V
Application.ScreenUpdating = False
For a = 1 To UBound(t, 1)
For b = 1 To UBound(t, 2)
V(a, b).Value = Replace(t(a, b), UCase("tata"), "TOTO")
Next
Next
End Sub
'----------------------------



"" a écrit dans le message de news:


sorry, pas tout compris
Dans mon cas, l'utilisation des Areas ne doit pas apporter grand chose,
parce que la plage de cellule est bien rectangulaire et completement
remplie.
Pour ce qui est de substitute, je ne l'avais pas retenu parce que je ne
voulais pas tenir compte des majuscules (mais le problème vient surtout du
balayage cellule par cellule qui est trop long)
En outre, je ne comprends pas pourquoi, mais je bloque sur une
"incompatibilité de type" avec le Ubound...
Merci



"MichDenis" a écrit dans le message de news:
#
Pour remplacer toto par tata
'--------------------------
Sub test()

Dim V As Range, Are As Range
With Feuil1
Set V = .UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
End With
For Each Are In V.Areas
t = Are
For a = 1 To UBound(t, 1)
For b = 1 To UBound(t, 2)
Are(a, b).Value = Application.Substitute(t(a, b), "toto",
"tata")

Next
Next
Next

End Sub
''----------------------------------------



"" a écrit dans le message de news:


bonjour,

j'ai, dans Excel (je sais bien, c'est pas fait pour ça) une (grosse)
base


de
données contenant uniquement du texte.
Mais, dans certaines cellules, de... (très) longs textes.
Jusque là, tout va bien.

Mais si malencontreusement je m'avise de vouloir remplacer "toto" par
"zaza"

par VBA dans tout le texte de toute la page (avec cells.replace), je
constate que VBA
1) fait bien son boulot tant qu'il ne rencontre pas une case trop
remplie


2) arrête tout bonnement de remplacer à partir de la première cellule
trop


pleine contenant un "toto"... et ce sans aucun avertissement d'erreur !

(Si je fait la même chose avec Edition / remplacer, message d'erreur
"formule trop longue" (en fait de formule je n'ai que du texte).)

Quelqu'un a-t-il une solution à ce pb désagréable ?
J'ai bien trouvé une solution VBA bancale avec Instr, left et right,
mais


c'est beaucoup trop lent pour ce que je veux faire (j'ai plusieurs mots
à


remplacer dans 20 000 lignes...).

d'avance merci de vos avis éclairés


http://jacxl.free.fr













Avatar
MichDenis
| C'est ce que je fais actuellement, mais comme je souhaite remplacer "toto"
| ou "Toto" ou "TOTO" ou "tOto" ou "TOto"... par "zaza" sans modifier la casse
| du reste du texte, et que je ne veux pas encore alourdir le code, j'utilise
| instr+left+right

Tu utilises ceci et pour la partie sans égard à la casse, tu place dans le haut
du module où tu copieras cette procédure la ligne de code "Option Compare Text"
et adieu la casse !

Il faut tout leur dire à ces jeunes ... ;-))


Option Compare Text

'-------------------------------------------
Sub test()

Dim V As Range
Dim a As Long, b As Long

With Feuil1
Set V = .UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
End With

t = V
Application.ScreenUpdating = False
For a = 1 To UBound(t, 1)
For b = 1 To UBound(t, 2)
V(a, b).Value = Replace(t(a, b), "toto" ,"ZAZA")
Next
Next
End Sub
'-------------------------------------------
Avatar
j
ok, le replace fonctionne :
1) ActiveCell.Replace "toto", "zaza" ne fait rien pour une cellule
contenant un texte très long
2) ActiveCell.Value = Replace(ActiveCell, "toto", "ZAZA") marche
toujours
(sais pas trop pourquoi...)

J'économise un pouillème de seconde par rapport à left+instr+right mais je
dois toujours balayer les cellules

J'ai encore gagné un poil en combinant un find("toto") avec le remplacement
(le find trouve même dans les cellules trop remplies) mais je vais encore
tourner toute la nuit !





"MichDenis" a écrit dans le message de news:

| C'est ce que je fais actuellement, mais comme je souhaite remplacer
"toto"

| ou "Toto" ou "TOTO" ou "tOto" ou "TOto"... par "zaza" sans modifier la
casse

| du reste du texte, et que je ne veux pas encore alourdir le code,
j'utilise

| instr+left+right

Tu utilises ceci et pour la partie sans égard à la casse, tu place dans le
haut

du module où tu copieras cette procédure la ligne de code "Option Compare
Text"

et adieu la casse !

Il faut tout leur dire à ces jeunes ... ;-))


Option Compare Text

'-------------------------------------------
Sub test()

Dim V As Range
Dim a As Long, b As Long

With Feuil1
Set V = .UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
End With

t = V
Application.ScreenUpdating = False
For a = 1 To UBound(t, 1)
For b = 1 To UBound(t, 2)
V(a, b).Value = Replace(t(a, b), "toto" ,"ZAZA")
Next
Next
End Sub
'-------------------------------------------




Avatar
MichDenis
| mais je vais encore tourner toute la nuit !


En utilisant la macro suivante sur 20 000 cellules
chaque cellule ayant 942 caractères
chaque cellule avait 95 fois le mot Denis à remplacer par toto
durée de l'opération 26 secondes sur un vieux P2 400

Dois-je convenir que les nuits sont courtes dans ton coin de pays ???????


'--------------------------------------------------
Sub test()
Dim V As Range
Dim a As Long, b As Long
Dim T, F As Double, S As Double
S = Time
Feuil2.Range("A1") = Time
Application.ScreenUpdating = False
Application.EnableEvents = False
With Feuil1
Set V = .UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
End With

T = V
Application.ScreenUpdating = False
For a = 1 To UBound(T, 1)
For b = 1 To UBound(T, 2)
V(a, b).Value = Replace(T(a, b), "Denis", "toto")
Next
Next
Application.EnableEvents = True
Feuil2.Range("A2") = Time
MsgBox F - S
End Sub
'--------------------------------------------------
Avatar
MichDenis
J'oubliais ceci :

Replace est une MÉTHODE de l'objet Range
Range("A1").Replace...
*************************************
Replace est une FONCTION de la bibliothèque VBA au même
titre que toutes les autres fonctions Left() , Asc(), Mid() ...
Range("A1") = Replace()

Il faut éviter de mélanger les genres !







"" a écrit dans le message de news:
ok, le replace fonctionne :
1) ActiveCell.Replace "toto", "zaza" ne fait rien pour une cellule
contenant un texte très long
2) ActiveCell.Value = Replace(ActiveCell, "toto", "ZAZA") marche
toujours
(sais pas trop pourquoi...)

J'économise un pouillème de seconde par rapport à left+instr+right mais je
dois toujours balayer les cellules

J'ai encore gagné un poil en combinant un find("toto") avec le remplacement
(le find trouve même dans les cellules trop remplies) mais je vais encore
tourner toute la nuit !





"MichDenis" a écrit dans le message de news:

| C'est ce que je fais actuellement, mais comme je souhaite remplacer
"toto"

| ou "Toto" ou "TOTO" ou "tOto" ou "TOto"... par "zaza" sans modifier la
casse

| du reste du texte, et que je ne veux pas encore alourdir le code,
j'utilise

| instr+left+right

Tu utilises ceci et pour la partie sans égard à la casse, tu place dans le
haut

du module où tu copieras cette procédure la ligne de code "Option Compare
Text"

et adieu la casse !

Il faut tout leur dire à ces jeunes ... ;-))


Option Compare Text

'-------------------------------------------
Sub test()

Dim V As Range
Dim a As Long, b As Long

With Feuil1
Set V = .UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
End With

t = V
Application.ScreenUpdating = False
For a = 1 To UBound(t, 1)
For b = 1 To UBound(t, 2)
V(a, b).Value = Replace(t(a, b), "toto" ,"ZAZA")
Next
Next
End Sub
'-------------------------------------------




1 2