Problème avec calcul d'une fonction créée

[Gringo]

Bonjour le forum,

J'ai un petit problème avec un de mes fichiers. Je me suis créé une fonction de recherche en code pour pouvoir aller chercher de l'information. Mon problème est le suivant: quand je change les données qui interviennent dans le calcul, la cellule contenant ma formule ne se met pas à jour.
J'ai essayé avec du code en utilisant Calculate mais ça ne marche pas non plus. La seule façon d'activer le calcul est en allant se placer sur la cellule, en faisant F2 et en validant (Enter). Or, j'ai plusieurs cellules avec ces formules et c'est plutôt irritant d'avoir à faire ça toutes les fois.

Voici la fonction que j'ai créée:

Public Function Searchvalue(Val As Date, Start As Range, Nbcol As Integer)
Dim a As Range, c As Double, x As Integer, y As Integer
Application.ScreenUpdating = False
y = Start.Column
x = Start.Row
Do Until Cells(x, y).Value >= Val
    x = x + 1
Loop
c = Cells(x, y + Nbcol)
Searchvalue = c
Application.ScreenUpdating = True
End Function

Elle fait un peu comme RECHERCHEV mais elle peut aussi aller vers le haut ou la gauche.

Ci-joint un fichier pour vous montrer un exemple. Il y a des explications en commentaire dans la cellule A1.

Merci de votre aide!

Gringo

 

[wilfried_42]

Re : Problème avec calcul d'une fonction créée

salut à toi

si je comprends bien ton code, tu ne testes qu'une seule celulle

lig=0 : col = 0
for each cel in start
if cel.value = val then lig=cel.row : col = cel.column
next

salutations

 

[Gringo]

Re : Problème avec calcul d'une fonction créée

Bonjour Wilfred,

Effectivement, dans mon code je ne testais qu'une seule cellule pour montrer l'exemple, mais j'aimerais faire en sorte que toutes mes cellules avec ma fonction se calculent, soit par macro, soit de toute autre façon.

La macro Searchvalue fonctionne bien, le problème est simplement qu'elle ne se calcule ni automatiquement, ni en utilisant la méthode Calculate (que je teste avec la macro Test).

Merci!

Gringo

 

[wilfried_42]

Re : Problème avec calcul d'une fonction créée

dans la partie de code workbook tu prends la declaration sheet_calculate
tu fais = call de ta macro

salutations

 

[Hervé]

Re : Problème avec calcul d'une fonction créée

bonsoir

sinon essaye d'ajouter un application.volatile en debut de fonction, afin de forcer le recalcul :

Public Function Searchvalue(Val As Date, Start As Range, Nbcol As Integer)
Dim a As Range, c As Double, x As Integer, y As Integer

Application.Volatile

y = Start.Column
x = Start.Row
Do Until Cells(x, y).Value >= Val
    x = x + 1
Loop
c = Cells(x, y + Nbcol)
Searchvalue = c

End Function

salut

edition : le val comme nom de variable n'est pas terrible, val etant un mot clé de vba il vaut mieux ne pas l'employer comme nom de variable.

 

[Gringo]

Re : Problème avec calcul d'une fonction créée

Bonjour Wilfred, Hervé, le forum,

J'ai essayé la suggestion de Hervé avec le Application.volatile et ça marche!!!

Par contre, mon problème s'est déplacé... J'ai plusieurs feuilles de calcul semblables à celle que j'ai mises dans le fichier joint original. La fonction que j'utilise (Searchvalue) est utilisée de la même façon dans toutes ces feuilles de calcul. Or, quand je change une valeur du calcul dans une feuille, les valeurs des autres feuilles changent elles aussi. Il faut dire que dans toutes les feuilles, la fonction a les mêmes paramètres de calcul:
dans la première feuille, par exemple, la formule est la suivante:

=Searchvalue($A$7+1;$C$13;7)

La formule est exactement la même pour les feuilles subséquentes parce que mes feuilles sont toutes montées sur le même frame... Comment puis-je m'en sortir?

Merci de votre patience et bonne fin de journée,

Gringo

 

[pierrejean]

Re : Problème avec calcul d'une fonction créée

bonsoir Gringo

salut Hervé

salut Wilfried

voila ce que je testerais

[LEFT]Public Function Searchvalue(Val As Date, Start As Range, Nbcol As Integer)
Dim a As Range, c As Double, x As Integer, y As Integer

Application.Volatile

[COLOR=blue]with Activesheet[/COLOR]

y = Start.Column
x = Start.Row
Do Until .Cells(x, y).Value >= Val
    x = x + 1
Loop
c = .Cells(x, y + Nbcol)
Searchvalue = c

[COLOR=blue]end with[/COLOR]
[COLOR=#0000ff][/COLOR]
End Function[/LEFT]

 

[Gringo]

Re : Problème avec calcul d'une fonction créée

Bonjour à tous,

pierrejean, j'ai testé mais ça ne fonctionne toujours pas. La fonction se calcule bien mais renvoie les valeurs de la feuille active sur toutes les autres feuilles. Pour mieux me faire comprendre, j'ai mis un fichier joint. Si vous avez le goût de tester des solutions...

Merci

Gringo

 

[pierrejean]

Re : Problème avec calcul d'une fonction créée

bonsoir Gringo

Salut à tous

1) Les . (points) devant cells etaient omis

2) j'espere que cela ne va pas faire trop de peine à mon ami Hervé

j'ais annulé le Application.Volatile et ma foi ça a l'air de fonctionner

teste et tiens nous au courant

 

[Gringo]

Re : Problème avec calcul d'une fonction créée

Bonjour à tous,

J'ai testé la version de pierrejean. Les valeurs sont maintenant indépendantes de page en page, mais lorsque je change la valeur de la cellule G15 (disons que je la mets à 0) dans l'onglet 1, je m'attendrais à ce que ma formule dans la cellule J27 change pour me donner 0 elle aussi. Or, ce n'est pas le cas, elle ne se calcule pas automatiquement.

Donc, si on règle un problème, ça en créé un autre...

Je continue à tester et à chercher une solution. Merci pour votre aide.

Gringo

 

[pierrejean]

Re : Problème avec calcul d'une fonction créée

re gringo

j'ai un probleme de test avec ton fichier
il est lié avec un autre que bien entendu je ne possede pas
il est probable que c'est pour cela que la modification de G15 (chez moi) n'a pas de repercussion sur J27

j'ai donc modifié la formule en J27 en faisant reference a C4
vois le fichier que je t'ais transmis et constate que la mise a jour de C4 provoque bien le changement de J27

sinon explique moi comment la modif de G15 doit se repercuter sur J27

 

[pierrejean]

Re : Problème avec calcul d'une fonction créée

bonjour Gringo
salut Hervé
bonjour à tous

resultat de mes cogitations nocturnes:

la fonction n'est pas recalculée parceque la modification de G15 n'est pas une modification des parametres de la fonction

j'ai donc concu une solution hypertarabiscotée que voici

ajout d'un parametre qui devra varier a chaque changement dans la feuille
(A1 par exemple)
a chaque changement de la feuille (macro worksheet change) on modifie A1 et on le retablit ce qui provoque le recalcul
accessoirement une variable publique evite que le changement se transforme en 'boucle infernale'

mais j'espere sincerement qu'il y aura mieux

 

[Gringo]

Re : Problème avec calcul d'une fonction créée

Bonjour à tous,

Je vous remercie grandement pour la patience que vous avez à travailler sur mon fichier. Je l'apprécie beaucoup, particulièrement toi pierrejean qui a travaillé fort pour m'aider.

Malheureusement, la solution de pierrejean n'est pas tout à fait ce que je recherche. Quand je change la donnée en G15 sur la feuille 1, les valeurs en gras qui sont calculées par ma fonction changent sur cette feuille. Ça marche.
Par contre, les autres feuilles de calcul sont aussi liées sur la cellule G15 de la feuille 1. Donc, idéalement, je voudrais que toutes mes fonctions se recalculent en fonction des données mises à jour (ici le taux d'intérêt qui est sur la feuille 1 en G15 et qui est dans la colonne G des autres feuilles) qui sont particuliers à chaque feuille. Les résultats de ma fonction devraient être différents d'une feuille à l'autre.

J'ai enlevé les liens de mon fichier et vous le remet disponible. J'ai tenté de mettre des explications supplémentaires dans mon fichier.

Merci de votre aide,

Gringo