Probleme .FormulaArray

Bonjour le forum

Voilà mon soucis. J'ai un fichier Excel avec au moins 150 feuilles Dans chacune d'elles se trouve toujours au même endroit une formule que j'ai besoins de modifier. De plus c'est une formule matricielle...

Dans cette formule j'ai besoin de remplacer $C$3 (qui peut être aussi $C$4) par Indirect("C3")

(Je veux faire le remplacement car cette formule fait référence à un tableau. Si jamais une ligne est insérée à ce niveau la formule se retrouve décalée. C''est pourquoi je veux utiliser Indirect)

J'ai commencé à écrie un code mais je bloque très vite avec le message d'erreur "Impossible de définir la propriété FormulaArray de la classe Range."

Je ne peux fournir pour des raisons de confidentialité.

Par avance merci de l’intérêt porté au sujet

Sub Remplacer()
Dim f As Worksheet
Dim Var, NewVar  As String

Set f = ActiveSheet

Var = f.Range("L3").FormulaLocal
MsgBox Var
NewVar = Replace(Var, "$C$4", "INDIRECT(" & Chr(34) & "C3" & Chr(34) & ")")
MsgBox NewVar
f.Range("L3").FormulaArray = NewVar

End Sub

Bonjour,

Ajouter un égal dans :

NewVar = Replace(Var, "$C$4", "=INDIRECT(" & Chr(34) & "C3" & Chr(34) & ")")

Mais je pense qu'il faudrait utiliser des cellules nommées, c'est plus facile à gérer.

Merci du retour

Je ne pense pas que le problème vienne du "="

j'ai modifié le code comme ci-dessous et ça marche, mais sur certaine feuille ça plante... J'investigue

Sub Macro4()
On Error GoTo Err
'On Error Resume Next

For i = 53 To Worksheets.Count

Var = Sheets(i).Range("L3").FormulaLocal
NewVar = Replace(Var, "$C$4", "INDIRECT(" & Chr(34) & "C3" & Chr(34) & ")")
Sheets(i).Range("L3").FormulaLocal = NewVar

Var2 = Sheets(i).Range("L3").FormulaLocal
NewVar2 = Replace(Var, "$C$3", "INDIRECT(" & Chr(34) & "C3" & Chr(34) & ")")
Sheets(i).Range("L3").FormulaLocal = NewVar2

Sheets(i).Range("L3").FormulaArray = Sheets(i).Range("L3").Formula

Next i

Exit Sub
Err:
Sheets(i).Select
MsgBox "il y a eu une erreur sur cette feuille (" & i & ")", vbCritical, "Erreur"

En fait mon code fonctionne si je n'apporte pas de modification. Seulement si il y à modification de la formule via VBA impossible de valider en matricielle via VBA mais possible manuellement

GGautier a écrit :

Je ne pense pas que le problème vienne du "="

En es-tu sûr ?

Oui je suis sur que ça ne marche pas avec le = devant INDIRECT

Bonjour,

pour pas t'embêter avec ça met ta formule en normal avec .Formula et revalide-là en matricielle avec :

.FormulaArray = .Formula

C'est intéressant surtout pour .FormulaR1C1 ou .FormulaLocal qui n'existent pas en version Array.

eric

Effectivement c'est ce que j'ai finit par faire. Tout se passais bien pour les 50 premières feuille et puis d'un coup, sans raisons apparentes, le code plante. Impossible de valider en matricielle. Pourtant il s'agit bien de la même formule vu que chaque feuille est issue d'un copier coller d'une feuille de référence.

Le problème vient d’ailleurs que du code je pense...

Je remet le code utilisé ci-dessous

Sub Macro4()
On Error GoTo Err

For i = 1 To Worksheets.Count

Var = Sheets(i).Range("L3").Formula
NewVar = Replace(Var, "$C$3", "INDIRECT(" & Chr(34) & "C3" & Chr(34) & ")")
Sheets(i).Range("L3").Formula = NewVar
Sheets(i).Range("L3").FormulaArray = Sheets(i).Range("L3").Formula

Next i

Exit Sub
Err:
Sheets(i).Select
MsgBox "il y a eu une erreur sur cette feuille (" & i & ")", vbCritical, "Erreur"
End Sub

J'ai isolé un onglet qui pose problème, le code se trouve dans le module1

De toute façon, sans la formule ni le fichier on ne pouvait en dire plus...

Qqq chose m'échappe. Pourquoi -LIGNE($C$3)+1 ?

C'est équivalent à :

Ligne()+1

si tu comptes changer $C$3 à chaque ligne. Non ?!?

eric

A vrai dire le pourquoi du -LIGNE($C$3)+1, je ne sais pas, j'avais récupéré cette formule sur un site. Etant donné quelle fonctionne je ne me suis jamais posé plus de question que ça.

C'est une formule que je tape uniquement en L3, je la valide en matricielle puis je l'étire sur 15 lignes. Elle me permet d'avoir une liste sans doublon.

Etant donnée que la formule se trouve dans un tableau je n'ai besoin que de "mettre à jour" la cellule L3 pour que le reste se modifie tout seul (l'avantage d'un tableau)

Si c'est pour faire ça en passant par du vba, autant utiliser un filtre avancé.

Par contre, vu que tu utilises des tableaux structurés, ton titre en L2 doit être le même qu'en C2, soit "Colonne3".

    Range("Tableau14101[[#All],[Colonne3]]").AdvancedFilter Action:=xlFilterCopy _
        , CopyToRange:=Range("Tableau25102[[#All],[Colonne3]]"), Unique:=True

Tu ne seras pas perdant devant une matricielle, surtout si tu as beaucoup de lignes. Même en utilisant l'événement Change pour mettre à jour.

eric

Je teste ça quand je retourne au bureau, je te tiens au courant

Je n'ai pas testé le code, mais si je le comprend bien je vais obtenir au final des données fixes. Je ne veux que le tableau où se trouve la liste reste dynamique car le tableau principale peu évoluer. De plus je ne veux pas utiliser de VBA dans le fichier (politique d’entreprise)

A l'origine je veux juste trouver un code VBA pour me permettre de modifier rapidement les 150 feuilles du classeur sans me prendre la tête à reprendre une par une chaque cellule.

Ce que je cherche, c'est à modifier le $C$3 par INDIRECT("C3") -> Jusque là tout va bien. Là où ça coince c'est de valider la formule en matricielle. Et là c'est pareille, je ne veux pas perdre un temps considérable à re-valider toutes les cellules L3 des feuilles du classeur...

(Le code est juste là pour m'aider à modifier rapidement tout le classeur, après je supprimerais ce code)

Ah...

Ta formule fait 283 caractères, je pense que tu es gêné par une limite à 255 car.

Dans ce cas modifie directement dans la feuille :

    Columns("L:L").Replace What:="$C$3", Replacement:="Indirect(""$C$3"")", LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

On est obligé de faire toute la plage car apparemment, pour le tableau excel ne prend pas en compte le changement sur une seule cellule pour le reporter dans la colonne.

A ta place j'ajouterai un test pour voir si ça n'a pas déjà été fait. Une erreur est vite faite et ça t'évitera une galère pour rétablir.

eric

PS : pour mettre une " dans une chaine il suffit de la doubler, pas la peine de t'encombrer avec Chr(34)