XL 2013 [RESOLU] Replace avec point virgule

[JLE]

Bonjour,

C'est toujours un réel plaisir de venir s'instruire sur ce forum !

Voilà plusieurs années que j'utilise la combinaison macro PERSONAL.XLA + fichiers en xlsx client + base de données en xls pour produire des documents pour des clients sous Office 2013. Depuis plusieurs mois, je me heurte à un problème que je n'arrive pas à résoudre.

En effet, il arrive que ma bdd en .xls s'ouvre en plein travail et mes liaisons sautent et donnent lieu à des #REF! Ce que je ne comprend d'ailleurs pas pourquoi...

Et pour résoudre ce problème, je souhaite remettre ces liaisons et le seul moyen qui fonctionne c'est de passer par CTRL+H, et de modifier !#REF!;E par !$A:$IV;E et #REF!;E par $A:$A;0 :
=SI(C5099<>"";INDEX('L:\Chiffrages\[Code profilés.xls]Accessoires'!#REF!;EQUIV(C5099;'L:\Chiffrages\[Code profilés.xls]Accessoires'!#REF!;0);2);"")
=SI(C5099<>"";INDEX('L:\Chiffrages\[Code profilés.xls]Accessoires'!$A:$IV;EQUIV(C5099;'L:\Chiffrages\[Code profilés.xls]Accessoires'!$A:$A;0);2);"")

Mais en passant par cette macro, cela ne fonctionne pas :
Application.ScreenUpdating = False
Cells.Replace What:="!#REF!;E", Replacement:="!$A:$IV;E"
Cells.Replace What:="!#REF!;0", Replacement:="!$A:$A;0"
Application.ScreenUpdating = True

Alors que si j'enlève le ;E ou le ;0, cela fonctionne !
Y a t il un moyen de signaler ce ; correctement à vba ?

Je vous remercie pour votre réponse.
JLE

 

[Hasco]

Bonjour,

Essayez de remplacer le point-virgule par une virgule.
Cells.Replace What:="!#REF!,E", Replacement:="!$A:$IV,E"

Le remplacement est fait dans la propriété .Formula et non dans .FormulaLocal de chaque cellule.

Cordialement

 

[JLE]

Merci de vous soucier de mon problème.
Je viens de faire le test, bizarrement cela fonctionne avec le zéro mais pas avec le E
Je précise pour être clair :
Cells.Replace What:="!#REF!,E", Replacement:="!$A:$IV,E" --> ne fonctionne pas
Cells.Replace What:="!#REF!,0", Replacement:="!$A:$A,0" --> fonctionne

 

[Hasco]

Re,

Parce que en anglais EQUIV est MATCH
Ne pensez pas FormulaLocal (français) mais Formula (anglais)
Dans la barre de formule, vous voyez EQUIV, mais si vous passez par vba en sous-jacent c'est MATCH
Donc Cells.Replace What:="!#REF!,M", Replacement:="!$A:$IV,M ou
Cells.Replace What:="!#REF!,MATCH", Replacement:="!$A:$IV,MATCH

Cordialement

 

[JLE]

Mais oui, je n'y ai pas pensé du tout, pour moi c'était un remplacement donc je pensais qu'il prenait cela comme du texte...
GRRRR.....
Je vous remercie beaucoup pour le temps de que vous m'avez consacré.

Je constate que cela prend énormément de temps pour les 80 000 cellules à modifier alors si vous avez une solution pour cette autre soucis, je suis preneur, en tous cas, merci !

 

[Hasco]

Re,

Cela prend du temps pour plusieurs raisons:

1 - le remplacement se fait cellule par cellule,
2 - à chaque fois la feuille (et/ou le classeur suivant le cas) est recalculée.

Avant ré-écriture de la formule ou remplacement, mettre
Application.Calculation = xlCalculationManual
et à la fin, son petit suivant:
Application.Calculation = xlCalculationAutomatic

Pour ré-écrire la formule en une fois:

Cells.FormulaLocal = "=SI(C5099<>"";INDEX('L:\Chiffrages\[Code profilés.xls]Accessoires'!$A:$IV;EQUIV(C5099;'L:\Chiffrages\[Code profilés.xls]Accessoires'!$A:$A;0);2);"") "

Si ça ne fonctionne pas, sélectionnez la première cellule qui contient la formule( sans erreur ou corrigez là manuellement),
Dans l'éditeur VBA, ouvrez la fenêtre d'éxécution (CTRL+G) si ce n'est déjà fait
tapez: ?Activecell.FormulaR1C1 puis validez
la fenêtre d'éxécution vous affichera une ligne du style (ce n'est qu'un exemple):

=VLOOKUP("M307_10 - Total",R[2]C[-1]:R[496]C[8],9,FALSE)

Copiez la ligne et vous n'aurez plus qu'à faire:

Cells.FormulaR1C1 = "=....."

Où .... est la ligne de formule que vous aura donné la fenêtre exécution.

PS: si la formule contient des apostrophes, doublez-les. Par ex pour la formule ci-dessus:

Cells.FormulaR1C1 = "=VLOOKUP(""M307_10 - Total"",R[2]C[-1]:R[496]C[8],9,FALSE)"

cordialement

 

[JLE]

Merci pour votre réponse.
J'ai tenter ce que vous me dites mais c'est encore plus long.

Je vais en rester là pour ce problème.

Si toutefois, on pouvait m'expliquer pourquoi ces liaisons sautent avec l'ouverture de la bdd, peut être que cela me permettrai de trouver une autre parade !

Merci. A bientot.

 

[Hasco]

Re,

Si vous aviez mis un fichier exemple, peut-être une autre solution aurait-elle été trouvée.

Cordialement

 

[JLE]

Je viens de trouver pourquoi cela posait problème, donc je vous mets la solution :
Le fichier de BDD étant en xls, quand il ouvre la BDD, la référence $A:$IV semble sauter car la bdd a une limite à 65536 ligne et ne comprend pas $A:$IV...donc les transforme en REF!

Quand je transforme la formule $A:$IV par $A1:$IV65536, cela ne saute plus....
Merci à toi pour tout.

 

[Hasco]

re,

Merci de ce retour.

cordialement