Enlever @ dans formule créé par itération via VBA

Bonjour à tous,

Avant tout de chose, les tableaux croisés dynamiques ne sont pas adaptés dans mon cas car l'outil que j'ai créé est un outil de calcul utilisant des tableaux à taille et nombre variable selon les inputs de l'utilisateur qui réalise ensuite des calculs itératifs pour optimiser certaines variables.

Comme c'est un peu compliqué je vais illustrer avec un exemple simple même si ce ne correspond pas exactement au but recherché mais la structure sera la même que celle de l'outil.

Prenons le cas d'une concession automobile vendant X modèles de voitures et où Y vendeurs y travaillent.

Le nombre de ventes mensuelles de chaque voiture est rangé dans tableaux par modèle comme ceci

Il y a donc X tableaux de ce type.

Afin de réaliser des statistiques l'utilisateur renseigne un tableau de ce type où chaque case est une liste déroulante avec le nom des modèles de voiture

Le tableau suivant est obtenu à côté.

Je sais que cela ne semble pas du tout optimisé pour l'exemple mais moi j'ai vraiment besoin que cela fonctionne comme ceci pour mon besoin.

Je n'ai pas trouvé meilleur exemple pour illustrer désolé.

J'ai joint un fichier exemple pour que vous vous fassiez une meilleur idée du rendu.

Mon problème vient du VBA, tous ces tableaux sont générés correctement sauf le dernier avec les résultats.

La formule qui affiche le résultat est un enchainement de SI, ou en fonction du modèle renseigné dans le deuxième tableau, la recherche (via INDEX/EQUIV) ne se fait pas dans le même tableau (car il y a X tableaux 1).

Ainsi cette formule a une taille qui varie en fonction du nombre de modèle et donc je la construis avec une boucle For :

For r = 1 To 12

formule = ""

For u = 1 To NBMODEL

cellule_model = Cells(,).Address

nom_model = Cells(,).Address

tableau_vente = Range(Cells(,), Cells(,)).Address

colonne_vendeur = Range(Cells(,),Cells(,)).Address

ligne_mois = Range(Cells(,), Cells(,)).Address

vendeur = Cells(,).Address

mois= Cells(,).Address(True, False)

formule = formule + "IF(" & cellule_model & "=" & nom_model & ",INDEX(" & tableau_vente & ",EQUIV(" & vendeur & "," & colonne_vendeur & ",0),EQUIV(" & mois & "," & ligne_mois & ",0)),"

Next

formule = formule + "0"

For u = 1 To NBMODEL

formule = formule + ")"

Next

Cells(,).Formula = "=" & formule & ""

Next

Le problème que lorsque la formule est écrite, je me retrouve avec @ devant chaque SI et chaque EQUIV et les cases ne se calculent pas (erreur #NOM?). Apparemment, ce serait une réaction de sécurité d'Excel pour je ne sais quelle raison.

Si je fais ctrl H sur les tableaux pour les enlever à la main ça fonctionne parfaitement mais si je fais un ctrl H via VBA :

Range().Select

Selection.Replace What:="@", Replacement:="", LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Et là toutes les cases sont en erreur #NOM? encore et je suis obligé de rentrer manuellement dans chaque case, faire entrer pour que cela fonctionne.

Et cela même si l'option de calcul de la feuille est en automatique. (faire Calculer maintenant et Calculer la feuille ne fonctionnent pas).

Comment faire pour se débarrasser automatique des @ sans que cela ne vienne perturber la recherche de la formule ?

Merci d'avance pour votre aide,

bonjour azerty 1234, et si vous utilisez formula2

Cells(,).Formula2 = "=" & formule & ""

Bonjour Bart,

Merci pour la proposition, je ne connaissais pas cette option.

J'ai essayé, je n'ai plus l'@ dans les formules par contre toutes mes cases ont l'erreur #NOM?.

Et comme précédemment, je suis obligé de rentrer dans chaque case une par une et faire entrer pour que la formule s'exécute :/

Cdt,

Bonjour

a tout hasard rajouter *1 après la concaténation ?

FINDRH

Bonjour,

Je ne suis pas sur de comprendre comment l'intégrer à la formule car j'ai des erreurs en essayant.

Pourriez-vous me l'indiquez à partir de cette ligne de code s'il vous plait ?

Cells(,).Formula2 = "=" & formule & ""

Merci d'avance,

Bonjour,
Et si tu joignais un xlsm à ta demande ?
J'ai du mal avec Cells(,).address !...
Cdlt.

c'est un mélange d'anglais (IF) et français (EQUIV), non ??

Bonsoir Azerty1234,

J'ai du mal avec Cells(,).address !...

Je reprendrais comme Jean-Eric, cette syntaxe est incorrecte.

A la rigueur on peut utiilser Cells(, C).Address ou C représente une valeur numérique pour la colonne. Excel corrigera l'absence de la valeur ligne par le chiffre 1.

Apparemment, ce serait une réaction de sécurité d'Excel pour je ne sais quelle raison.

Non, par le @, Excel indique tout simplement que la syntaxe de la formule a une partie incorrecte. Mais que la formule peut fonctionner dans certains cas et donner un résultat. Et dans l'autre cas (Erreur) elle fournira #NOM?

Essaie le simple code ci-dessous dans une feuille du classeur. Ou le non est inscris sans guillemet.

Sub Essai1()
'La cellule B2 a la formule =SI(D2=0;"oui";non)
Formule = Range("B2").Formula2
Range("B4") = Formule
End Sub

En cellule B4 tu obtiendras un @. Si D2 vaut 0 la formule donnera oui, par contre si D2 diffère de 0 tu auras #NOM?

Maintenant si la formule est écrite correctement. Donc avec un non entre guillemets, tu n'auras plus le @ lors de sa copie en B4.

Sub Essai2()
'La cellule B2 a la formule =SI(D2=0;"oui";"non")
Formule = Range("B2").Formula2
Range("B4") = Formule
End Sub

Donc reprends ton code en vérifiant que tu n'as pas un guillemet en trop ou en moins. Et même si Excel corrige ton IF inscris plutôt SI.

Bonjour,
Pas de fichier, version Excel non précisée, etc.
@ est l'opérateur d'intersection implicite (voir aide Microsoft à ce sujet).
azerty1234 doit fournir des informations complémentaires.
Cdlt.

Bonjour à tous,

Merci Bart, c'était à la fois utiliser formula2 et remplacer EQUIV par MATCH qui ont résolu mon problème.

Cdt,