Plage variable et fonction

Résolu
cs_mayga
Messages postés
30
Date d'inscription
dimanche 12 décembre 2004
Statut
Membre
Dernière intervention
11 juillet 2011
- 1 juil. 2011 à 16:02
cs_mayga
Messages postés
30
Date d'inscription
dimanche 12 décembre 2004
Statut
Membre
Dernière intervention
11 juillet 2011
- 5 juil. 2011 à 17:32
Bonjour,
Je ne suis pas un expert!
J'essaie de créer une fonction en VBA pour excel afin de faire une recherche sur plusieurs critères dans une feuille de donnée.

Le problème vient du fait que lorsque je fais référence à une plage variable dans ma fonction, celle ci renvoie une erreur "VALEUR"

Par exemple:
Sheets(Nom).Range(Range("A1").End(xlDown))
L'exécution de la fonction bloque à ce niveau!

Alors que si je place le même code dans une procédure, tout se passe bien.

Si quelqu'un pouvait m'éclairer ou me donner un conseil, je l'en remercie d'avance.

42 réponses

ucfoutu
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
3 juil. 2011 à 11:32
Je ne comprends pas car je viens de le tester chez moi, sans faille.
Y compris depuis une autre feuille. !





____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP
0
ucfoutu
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
3 juil. 2011 à 11:38
Attends.
Je crois avoir compris ce qui se passe dans ton appli.
Mets la fonction (mon dernier code) en Public dans un module.
Essaye et dis/.

____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP
0
ucfoutu
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
3 juil. 2011 à 11:50
Si marche toujours pas :
fais-moi connaître ton adresse email par Messagerie privée (MP)
Et je t'enverrai un fichier exemple tout fait


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP
0
cs_mayga
Messages postés
30
Date d'inscription
dimanche 12 décembre 2004
Statut
Membre
Dernière intervention
11 juillet 2011

3 juil. 2011 à 12:02
Je ne comprends, chez moi ça ne marche pas!
La seule chose qui change dans mon code c'est que j'ai déclaré derligne (intNLigne chez moi) comme variant. (Car j'ai option explicit en début de module)

Et peut être le fait que j'exécute à partir de la feuille de module et non en cliquant sur un bouton.

Mais là mystère!
Je re-poste mon code :
Private Function cherche1(feuille As Worksheet, strRMois As String, strRNom As String, _
strNomColVal As String, ColMois As String, ColNom As String) As String

cherche1 = "Pas de données"

Dim strNomColMois, strNomColNom
If ColMois "" Then strNomColMois "Mois"
If ColNom "" Then strNomColNom "Nom"

'Trouver les numéros des colonne Utilisés
Dim intNColNom As Integer, intNColVal As Integer, intNColMois As Integer
Dim c As Range
With feuille
With .Rows(1)
    Set c = .Find(strNomColNom, LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then intNColNom = c.Column
    Set c = .Find(strNomColVal, LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then intNColVal = c.Column
    Set c = .Find(strNomColMois, LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then intNColMois = c.Column
End With

Dim intNLigne As Variant
intNLigne = feuille.Cells(Rows.Count, intNColMois).End(xlUp).Row

With feuille.Range(Cells(1, intNColMois), Cells(intNLigne, intNColMois))
    Dim firstaddress As String
    Set c = .Find(strRMois, LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then
    If feuille.Cells(c.Row, intNColNom).Text = strRNom Then
    cherche1 = feuille.Cells(c.Row, intNColVal).Value: Exit Function
    End If
    firstaddress = c.Address
    Do
        Set c = .FindNext(c)
        If Not c Is Nothing And .Cells(c.Row, intNColNom).Text = strRNom Then
        cherche1 = feuille.Cells(c.Row, intNColVal).Value: Exit Function
        End If
    Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
End With
End With
End Function
0

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
cs_mayga
Messages postés
30
Date d'inscription
dimanche 12 décembre 2004
Statut
Membre
Dernière intervention
11 juillet 2011

3 juil. 2011 à 12:15
Non ça ne marche pas,
autre problème comment fait on pour transmettre son mail en (MP)?
0
ucfoutu
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
3 juil. 2011 à 16:57
Voilà !
Le fichier demo, "dégradé" pour ta version (2003), est maintenant chez toi.
Comme il est rigoureusement identique à ce que j'ai écrit plus haut, tu peux sans problème continuer maintenant ici (puisque chacun en connaît le code exact)


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP
0
cs_mayga
Messages postés
30
Date d'inscription
dimanche 12 décembre 2004
Statut
Membre
Dernière intervention
11 juillet 2011

3 juil. 2011 à 23:04
Erreur détectée :

Mauvais :
With .Range(Cells(1, intNColMois), Cells(intNLigne, intNColMois))


Bon :
With .Range(.Cells(1, intNColMois), .Cells(intNLigne, intNColMois))


Ceci dit je ne comprends pas vraiment pourquoi le point est nécessaire dans ce cas!

With feuille.Range(Cells(1, 1), Cells(1, 2))

celui ci ne me provoquera pas d'erreur si je me trouve dans la même feuille.
0
ucfoutu
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
4 juil. 2011 à 07:02
With feuille.Range(.Cells(1, intNColmois), .Cells(derligne, intNColmois))

est bien ce que j'avais écrit et se trouve ainsi écrit dans le fichier demo. Il vaut toujours mieux se servir du copier/coller intégral
Le point est nécessaire, en effet, dès lors que l'on se sert de Cells, pour ne pas confondre les cells de la feuille concernée avec celles de la feuille en cours (que rien n'interdirait d'utiliser, pardi !)


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP
0
bigfish_le vrai
Messages postés
1835
Date d'inscription
vendredi 13 mai 2005
Statut
Membre
Dernière intervention
20 novembre 2013
12
4 juil. 2011 à 14:51
Bonjour,

sans remettre en question tout ce qui à déjà été dit:

je vous conseil 3 choses sur la ligne :

Private Function cherche1(feuille As Worksheet, strRMois As String, strRNom As String, _
strNomColVal As String, ColMois As String, ColNom As String) As String




1) dans une fonction VBA EXCEl "feuille As Worksheet" est inutile ! en effet il existe une propriété de l'application
qui renvoi l'objet appelant la fonction:

Dans votre cas cela donnerait:

Private Function cherche1(strRMois As String, strRNom As String, _
strNomColVal As String, ColMois As String, ColNom As String) As String

Dim feuille As Worksheet
Set feuille = Application.Caller.Parent 'évidemment si la recherche s'effectue toujours dans la feuille appelante


2) ne jamais oublier qu'en VBA l'option Byref est l'option par défaut et que dans une fonction il peut être dangereux de ne pas spécifier l'option byval ou byref. Dans ton cas ByVal me parait être le plus approprié.

ce qui donne :
Private Function cherche1(ByVal strRMois As String, ByVal strRNom As String, _
ByVal strNomColVal As String, ByVal ColMois As String, ByVal ColNom As String) As String


3) pour éviter les lignes:
Dim strNomColMois, strNomColNom
If ColMois "" Then strNomColMois "Mois"
If ColNom "" Then strNomColNom "Nom"

le mot clé optional peut être ajouté à l'argument et ainsi définir la valeur par défaut de chaque argument:
Dans ce cas les arguments "ColMois" et "ColNom", associés à "ByVal", peuvent être utiliser directement dans le déroulement de la fonction.

ce qui donne:
Private Function cherche1(ByVal strRMois As String, ByVal strRNom As String, _
ByVal strNomColVal As String,Optional ByVal ColMois As String "Mois", Optional ByVal ColNom As String "Nom") As String


A+
0
ucfoutu
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
4 juil. 2011 à 15:02
Bonjour, bigfish_le vrai
Ton 1) ===>>
tu oublies une chose importante : le demandeur a précisé que la fonction devait pouvoir être appelée depuis une autre feuille
Ton 2) ===>>
la fonction ne modifie aucun de ses paramètres d'entrée (alors : que ce soit byref ou byval ===>> sans effet
Ton 3) ===>>
Non, car le demandeur a exprimé le voeu d'en faire in fine une fonction personnalisée et les noms donnés en titre des colonnes de référence doivent donc rester flexibles

____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP
0
ucfoutu
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
4 juil. 2011 à 15:04
J'attends maintenant que le demandeur soit prêt en ce qui concerne le passage à une fonction personnalisée. La modif à faire sera alors mineure.

____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP
0
cs_mayga
Messages postés
30
Date d'inscription
dimanche 12 décembre 2004
Statut
Membre
Dernière intervention
11 juillet 2011

5 juil. 2011 à 00:00
Bonsoir,
je tarde à répondre car je n'ai pas eu de connexion durant toute la journée!

J'ai essayé pendant ce temps de passer ma fonction en macro complémentaire mais toujours le même problème de #VALEUR quand je l'exécute.
J'ai essayé d'enlever le premier argument (feuille as worksheets) mais toujours le même résultat.
???
0
cs_mayga
Messages postés
30
Date d'inscription
dimanche 12 décembre 2004
Statut
Membre
Dernière intervention
11 juillet 2011

5 juil. 2011 à 10:23
Bonjour,
Merci pour le code.
J'ai essayé de tester rapidement :
La fonction ne marche que si je me trouve dans la feuille de données.
J'ai ajouté :
If nomcolnoms "" then nomcolnoms "Nom"
If nomcolmois "" then nomcolmois "Mois"

Ca ne fonctionne plus => #VALEUR

Pour l'argument f je suis obligé de rentrer le nom de la feuille manuellement, n'est il pas possible de faire référence à la feuille en cliquant sur l'onglet de celle-ci.

Autre question: j'ai ajouté un stop en début de code (après déclarations).
Est il normal (sans que j'ai de cellule qui utilise la fonction) qu'a l'ouverture du fichier la fonction s'exécute seule et bloque sur le point d'arrêt. (Ce doit être l'actualisation, mais la fonction n'est utilisée nulle part)
J'ai remarqué également que les valeurs de nomcolnoms nomcolMois ne se déchargeaient pas de la mémoire après l'exécution du code.
Ce doit Public, mais cela ne pourrai t'il pas avoir une influence?
0
ucfoutu
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
5 juil. 2011 à 10:46
Je t'envoie donc (par email) un fichier demo (avec mon code) qui montre que tout marche bien, depuis n'importe quelle feuille

If nomcolnoms "" then nomcolnoms "Nom"
If nomcolmois "" then nomcolmois "Mois"


n'a pas sa place ici (je l'ai expliqué plus haut pourquoi !)

Renseigner la feuille utilisée par un clic sur l'onglet n'est pas une bonne idée ===>> insère genre Feuil1!, sauf à corriger alors manuellement encadrements de guillemets et suppression du !
Pour le reste, je ne sais pas pourquoi et ne vois pas comment ta fonction continuerait à être exécutée lorsqu'elle n'est pas appelée ! (ne le fait pas sur ma machine !)


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP
0
ucfoutu
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
5 juil. 2011 à 11:09
Tu es donc maintenant en possession de la demo.
J'ai fait des essais dans tous les sens sans faille.


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP
0
cs_mayga
Messages postés
30
Date d'inscription
dimanche 12 décembre 2004
Statut
Membre
Dernière intervention
11 juillet 2011

5 juil. 2011 à 11:37
Merci, j'ai bien reçu le fichier,il fonctionne si je rentre tous les arguments à la main.
Mais moi je fait référence à des cellules.
Le but de la fonction est de remplir un tableau qui synthétisent les données dans une année pour une personne.
Dans la colonne de gauche de mon tableau je vais avoir les mois (janvier ligne 1, février ligne 2....), dans les colonne à droite je vais avoir les différentes valeurs trouvées par la fonction (Valeur colonne 2, Valeur1 colonne 3 ...) et une cellule pour le nom.
Je voudrais éviter de rentrer le formule manuellement dans chaque cellule, il serait plus simple de mettre = et pointer sur la cellule correspondante.
Je voudrais rentrer le formule dans le première cellule du tableau et ensuite la copier sur les autres cellules et les références seraient incrémentées automatiquement.
Alors qu'ici si je fait référence à des cellules ça me donne #VALEUR, pourtant dans la fenêtre des arguments lorsque je clique sur la cellule, la valeur à droite est bonne.
0
cs_mayga
Messages postés
30
Date d'inscription
dimanche 12 décembre 2004
Statut
Membre
Dernière intervention
11 juillet 2011

5 juil. 2011 à 11:40
Ne faudrait il pas tout déclarer en range au départ (du moins la référence à valeur et mois) et prendre range.name pour la suite.
0
cs_mayga
Messages postés
30
Date d'inscription
dimanche 12 décembre 2004
Statut
Membre
Dernière intervention
11 juillet 2011

5 juil. 2011 à 12:13
Je viens d'essayer ça fonctionne.
J'ai déclaré mois et valeur en range, puis ai affecté les valeurs au variables strRMois et nomColValeurs.
Maintenant je vais vérifier si en copiant la formule tout se passe bien.
0
ucfoutu
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
5 juil. 2011 à 12:45
Je crains fort que ton souci de simplification (par pointage à la souris) ne se traduise in fine pour l'utilisateur par une gymnastique ésotérique (sauf à accompagner le tout d'un fichier d'aide)


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP
0
cs_mayga
Messages postés
30
Date d'inscription
dimanche 12 décembre 2004
Statut
Membre
Dernière intervention
11 juillet 2011

5 juil. 2011 à 12:47
Bon petit problème,
je suis obligé de procéder en plusieurs étapes, copier les lignes et ensuite les colonnes en changeant la référence absolue (mois puis valeur).
Je suppose que c'est un problème récurent d'Excel.
0