Plage variable et fonctionRésolu

Question

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.

ucfoutu · Accepted Answer

On dirait que 2003 est la cause d'un souci (avec la mise en oieuvre de FindNext) par rapport aux versions ultérieures.
Qu'à cela ne tienne ===>> on va s'y prendre sans lui ===>>

Attention : iml s'agit maintenant d'une fonction personnaliséez ==>> Code à mettre dans un module, donc, puis insertion d'une fonction personnalisée (et remplir jusqu'au champ Valeur, qui n'apparait pas sans utiliser l'ascenseur)

Public Function cherche2(f As String, nomcolmois As String, strRMois As String, nomcolnoms As String, strRNom As String, nomColValeurs As String) As String
  Application.Volatile
  cherche2 = "Pas de données"
  Dim feuille As Worksheet
  Set feuille = Worksheets(f)
  Dim intNColNom As Integer, intNColVal As Integer
  Dim c As Range
    With feuille.Rows(1)
      Set c = .Find(nomcolmois, LookIn:=xlValues)
      If Not c Is Nothing Then intNColmois = c.Column
      Set c = .Find(nomcolnoms, LookIn:=xlValues)
      If Not c Is Nothing Then intNColNom = c.Column
      Set c = .Find(nomColValeurs, LookIn:=xlValues)
      If Not c Is Nothing Then intNColVal = c.Column
    End With
    derligne = feuille.Cells(Rows.Count, intNColmois).End(xlUp).Row
    Dim plage As Range, R As Range
    Set plage = feuille.Range(feuille.Cells(1, intNColmois), feuille.Cells(derligne, intNColmois))
    For Each c In plage
      If c.Value strRMois And feuille.Cells(c.Row, intNColNom).Text strRNom Then
        cherche2 = feuille.Cells(c.Row, intNColVal).Value: Exit Function
      End If
    Next
    Set feuille = Nothing
    Set c = Nothing
End Function


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

boomer11 · Answer

salut,

essaie peut etre cette synthaxe:
Sheets(Nom).Range("A1", Range("A1").End(xlDown))
sinon donne un exemple plus précis...
voila

cs_mayga · Answer

Merci pour la réponse.

En fait je viens de voir que le problème viens de Sheets(Nom).
Je fais référence à une feuille de donnée qui n'est pas la feuille ou j'exécute ma fonction.

Je ne sais pas si je suis assez clair.
Dans mon classeur j'ai une feuille nommée "DONNEE" et j'utilise ma fonction dans une autre feuille.

Par exemple :
Function essai()
essai = range("A1", Range("A1").End(xlDown)).Address

Celle ci s'exécutera sans problème, mais je serai obligé de l'utiliser sur la même feuille.
Or, je voudrai faire référence à ma feuille "DONNEE"
Sheets("DONNEE").Range("A1", Range("A1").End(xldown)).Address
 ne marche pas! (ou worksheets idem)

boomer11 · Answer

re, 

Je pense que tu devrais activer ta feuille de données avant d'utiliser la fonction
par exemple:

sub test()
    Application.ScreenUpdating =false
Sheets("DONNEE").Activate
'Ta macro.....
Sheets("DONNEE").Range(Range("A1").End(xlDown))
'....
'à la fin tu réactives ton autre feuille
Sheets("feuil?").Activate
Application.ScreenUpdating =true 'Pour éviter les changements de feuilles intempestifs
Ce sera peut etre une solution !!

ucfoutu · Answer

Bonjour,

1) ta fonction doit retourner une adresse ?
Une adresse est de type String 
2) ta fonction doit traiter une feuille ?
Passe cette feuille en paramètre de ta fonction

Je reprends ton exemple ==>>
Private (ou Public) Function essai(feuille as worksheet) as string
  essai = feuille.range("A1", Range("A1").End(xlDown)).Address 
end function


cette fonction est appelable ainsi (par exemple) :

msgbox essai(worksheets("Feuil1"))
____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

cs_mayga · Answer

Bonjour, et encore merci pour vos réponses.

Je ne suis pas assez clair et vous ai induit en erreur.
Ma feuille Donnee comporte une colonne Mois,une colonne Nom, et différentes colonne avec des valeur diverses.
Je cherche a obtenir la valeur correspondant a un mois et un nom précis.

Je vous transmet le code :

Function Recherche_Val(rgDonnee As Range, RMois As String, RCol As String, RNom As String)

Dim rgMois As Range, rgNom As Range, rgCol As Range
Dim intLrgMois As Integer, intLrgNom As Integer, intNcol As Integer
Dim i As Integer, a As Integer
Dim TabLmois() As Variant
Dim strNFeuil as string


strNFeuil = rgDonnee.Worksheet.name


With Worksheets(strNFeuil).Range(Range("A1").End(xlDown), Range("A1").End(xlToRight))
'Trouve le numero de colonne de la valeur a chercher
    Set rgCol = .Find(RCol, LookIn:=xlValues)
        If Not rgCol Is Nothing Then
            intNcol = rgCol.Column
        Else
            Recherche_Val = "Pas de donnees"
        End
        End If
'Trouve toutes les lignes du même mois et les enregistrer dans un tableau
    i = 0
    Set rgMois = .Find(RMois, LookIn:=xlValues)
        If Not rgMois Is Nothing Then
        intLrgMois = rgMois.Row
        Do
        ReDim Preserve TabLmois(i)
        TabLmois(i) = rgMois.Row
        i = i + 1
        Set rgMois = .FindNext(rgMois)
        Loop While Not rgMois Is Nothing And rgMois.Row <> intLrgMois
        End If
    a = i
    i = 0
End With
'Vérifier pour chaque ligne si un nom correspond
        Do
        With Worksheets(RFeuil).Cells(TabLmois(i), 1).EntireRow
            Set rgNom = .Find(RNom, LookIn:=xlValues)
            If rgNom Is Nothing Then i = i + 1
        End With
        Loop While rgNom Is Nothing And i < a
            If Not rgNom Is Nothing Then
            intLrgNom = rgNom.Row
            Recherche_Val = Sheets(RFeuil).Cells(intLrgNom, intNcol).Value
            Else
            Recherche_Val = "Pas de donnees"
            End If
End Function

Désolé pour la présentation mais je ne sais pas comment insérer le code dans le message!
J'ai juste fait un copier coller du texte.

J'ai créer et tester ce code dans une procédure Sub, sans problème.
J'ai ensuite créer la fonction et l'ai exécuté a partir d'une procédure sub (en rentrant les arguments de la fonction manuellement pour le tester), tout se passe bien.

Je voudrais maintenant l'utiliser comme fonction (en tapant =Recherche_val(.... dans une cellule) et la ça ne marche plus!

ucfoutu · Answer

Déjà et avant d'aller plus en avant :
1) tu n'as pas typé ta fonction. Si j'ai bien compris (vu ce que tu veux lui faire retourner), elle devrait être typée en String
2) quel est l'inrérêt de :
Dim strNFeuil as string


strNFeuil = rgDonnee.Worksheet.name


With Worksheets(strNFeuil).Range(Range("A1").End(xlDown), Range("A1").End(xlToRight)) 


 Puisque rgdonnee est déjà une feuille (passée comme telle en paramètre)!
Ainsi : rgdonnee.Range("A1") suffit, par exemple, pour te référer à la cellule A1 de la feuille passée en paramètre 

Recommence et reviens ensuite avec, cette fois, ton code mis entre balises  et 
____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

cs_mayga · Answer

Merci pour les infos et conseils,
 
Voici le code plus visible:
 
Function Recherche_Val(rgDonnee As Range, RMois As String,_
RCol As String, RNom As String)as long

Dim rgMois As Range, rgNom As Range, rgCol As Range
Dim intLrgMois As Integer, intLrgNom As Integer, intNcol As Integer
Dim i As Integer, a As Integer
Dim TabLmois() As Variant
Dim strNFeuil as string


strNFeuil = rgDonnee.Worksheet.name


With Worksheets(strNFeuil).Range(Range("A1").End(xlDown), Range("A1").End(xlToRight))
'Trouve le numero de colonne de la valeur a chercher
    Set rgCol = .Find(RCol, LookIn:=xlValues)
        If Not rgCol Is Nothing Then
            intNcol = rgCol.Column
        Else
            Recherche_Val = "Pas de donnees"
        End
        End If
'Trouve toutes les lignes du même mois et les enregistrer dans un tableau
    i = 0
    Set rgMois = .Find(RMois, LookIn:=xlValues)
        If Not rgMois Is Nothing Then
        intLrgMois = rgMois.Row
        Do
        ReDim Preserve TabLmois(i)
        TabLmois(i) = rgMois.Row
        i = i + 1
        Set rgMois = .FindNext(rgMois)
        Loop While Not rgMois Is Nothing And rgMois.Row <> intLrgMois
        End If
    a = i
    i = 0
End With
'Vérifier pour chaque ligne si le nom existe
        Do
        With Worksheets(strNFeuil).Cells(TabLmois(i), 1).EntireRow
            Set rgNom = .Find(RNom, LookIn:=xlValues)
            If rgNom Is Nothing Then i = i + 1
        End With
        Loop While rgNom Is Nothing And i < a
            If Not rgNom Is Nothing Then
            intLrgNom = rgNom.Row
            Recherche_Val = Sheets(strNFeuil).Cells(intLrgNom, intNcol).Value
            Else
            Recherche_Val = "Pas de donnees"
            End If
End Function


J'ai déclaré rgDonne comme Range car lorsque je tape "=" et sélectionne ma fonction une fenêtre s'ouvre pour entrer les arguments de la fonction mais je ne veux pas les rentrer manuellement à chaque fois mais plutôt faire référence a des cellules (pour pouvoir par la suite copier le formule dans les cellules adjacentes).
Au départ j'avais déclaré rgDonne comme worksheet mais lorsque je sélectionnais l'onglet de la feuille : #VALEUR# en rouge dans cette fenêtre.
Je pourrai changer par la suite, mais je pense que le problème ne vient pas de là.

J'ai typé ma fonction en Long car j'ai des calculs à faire par la suite avec ces résultats.

Est-il possible de trouver le code correspondant à la fonction RECHERCHEH pour s'en inspirer?

ucfoutu · Answer

on en reparlera ce soir tard (je serai alors moins occupé), mais déjà :
1) J'ai typé ma fonction en Long car j'ai des calculs à faire par la suite avec ces résultats.


et
2)
Recherche_Val = "Pas de donnees"

Ca ne te laisse imperturbable, à toi ?
Mais il y a (avant même d'en faire une fonction personnalisée), pleins de petites choses à revoir, en commençant par le mécanisme lui-même.
A ce soir.
____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

cs_mayga · Answer

Oups!
Désolé je l'ai typé juste avant de le reposter suite au message précédent, au départ je pensais qu'en ne mettant rien elle serait typé comme variant automatiquement.
Je mettrai ="0".
Mais visiblement ce n'est pas le seul problème!!!

J'étais content de ma petite fonction!

Encore merci pour le temps perdu!

A plus

ucfoutu · Answer

Me revoilà donc.
Prêt ?

- La plage à "fouiller" principalement est celle des mois.
- La dernière ligne de cette plage des mois à fouiller est la dernière remplie de cette colonne des mois.
Toutefois, il est inutile d'aller plus loin que la dernière ligne remplie de la colonne des noms (c'est évident car alors, on ne risquerait pas d'avoir les 2 conditions remplies, non ?)

- j'ignore à ce stade le format de ta colonne des mois. Dans l'exemple qui va suivre, je considère que ses cellules sont formatées en nombre et que tes mois y sont donc des 1,2, ... 12.
(La modif à faire si string est minime)
Regarde maintenant (cela devrait t'ouvrir des perspectives) ce que fait le code suivant ;
Private Sub CommandButton1_Click()
  Dim nom As String, mois As String, feuille As Worksheet, colmois As String, colnom As String
  MsgBox cherche(Worksheets("Feuil1"), "A", "D", 3, "jacques")
End Sub
Private Function cherche(feuille As Worksheet, colmois As String, colnom As String, mois As Integer, nom As String) As String
   Dim dercolmois As Long, dercolnom As Long
   cherche = "pas trouvé"
   dercolmois = feuille.Range(colmois & Rows.Count).End(xlUp).Row
   dercolnom = feuille.Range(colnom & Rows.Count).End(xlUp).Row
   If dercolmois < dercolnom Then dercolmois = dercolnom
   With feuille.Range(col & "1:" & col & dercolmois)
     Dim c As Range
     Set c = .Find(mois, LookIn:=xlValues)
     If Not c Is Nothing Then
       firstAddress = c.Address
       Do
         Set c = .FindNext(c)
         If Not c Is Nothing And .Range(colnom & c.Row) = nom Then
           cherche = c.Row: Exit Function
         End If
       Loop While Not c Is Nothing And c.Address <> firstAddress
     End If
  End With
End Function
en admettant que la feuille concernée soit nommé Feruil1, que les mois se trouvent en nombres entiers dans la colonne A et les noms en String dans la colonne D, que le mois cherché soit le mois 3 et que le nom cherché soit "jacques"
Nous faisons retourner ici le N° de la ligne correspondante (on pourrait bien évidemment retourner n'importe quoi d'autre).
Reviens après analyse et compréhension et nous passerons à la seconde partie : en faire une fonction personnalisée.
____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

cs_mayga · Answer

J'ai essayé d'analyser le code,
D'après ce que je comprend, il faut que connaisse au départ l'emplacement exact de mes colonnes (Mois = col A...).
Dans mon cas je voulais que ce soit plus flexible pour pouvoir réutiliser la fonction dans un autre classeur qui ne serai pas identique.

 Ici j'obtiens le numéro de la ligne correspondant à l'intersection de la colonne nom et de la colonne mois.
Il faut maintenant que je cherche mon résultat dans la cellule correspondant à l'intersection de la ligne trouvée et de la colonne ou se trouve ma valeur (qui elle varie obligatoirement).

Je ne sais pas si j'ai bien compris!

cs_mayga · Answer

Re Bonsoir,
J'ai modifier mon code

Private Function cherche1(feuille As Worksheet, strRMois As String, strRNom As String, ColValeur As String) As String
cherche1 = "Pas de données"
'Trouver les numéros des colonne Utilisés
Dim intNColNom As Integer, intNColVal As Integer
Dim c As Range
With feuille.Rows(1)
    Set c = .Find("Nom", LookIn:=xlValues)
    If Not c Is Nothing Then intNColNom = c.Column
    Set c = .Find(ColValeur, LookIn:=xlValues)
    If Not c Is Nothing Then intNColVal = c.Column
End With

With feuille.Range("A1", Range("A1").End(xlDown))
    Dim firstaddress As String
    Set c = .Find(strRMois, LookIn:=xlValues)
    If Not c Is Nothing Then
    firstaddress = c.Address
    Do
        Set c = .FindNext(c)
        If Not c Is Nothing And .Cells(c.Row, intNColNom) = 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 Function

Il fonctionne bien pour le moment!
Il n'y aura pas de cellule vide dans la colonne mois.
J'ai utilisé Cells au lieu de Range pour le résultat car je ne sais pas transformer le numéro de colonne "1" en "A".
C'est vrai que c'est plus clair
encore merci pour tous ces conseils.

ucfoutu · Answer

Je crois que tu as au contraire parfaitement compris, puisque tu as su adapter.
On va alors plus loin ===>>
si tu veux que ta fonction (à personnaliser ensuite) soit utilisable partout, il te suffit de la rendre plus flexible ainsi :
- lui dire quels sont les titres des colonnes concernées
- lui faire (comme tu l'as fait pour l'une d'entre elles) chercher ces colonnes
- lui dire le critère des deux premières colonnes (mois et nom dxans ton cas)

Exemple

Private Sub CommandButton1_Click()
  Dim titre1 As String, val_cherchee_titre1 As String
  Dim titre2 As String, val_cherchee_titre2 As String
  Dim titre3 As String
  Dim lafeuille As Worksheet
  Set lafeuille = Worksheets("Feuil1")
  titre1 = "Mois"
  titre2 = "Nom"
  titre3 = "Valeur"
  val_cherchee_titre1 "3" '>> si tes mois sont des nombres
  val_cherchee_titre2 = "b"
  MsgBox cherche1(lafeuille, titre1, val_cherchee_titre1, titre2, val_cherchee_titre2, titre3)
  '
  ' mais rien ne t'empêcherait d'appeler ainsi, directement, dans tout ce qui précède
  'MsgBox cherche1(Worksheets("Feuil1"), "Mois", "3", "Nom", "b", "Valeur")
End Sub
Private Function cherche1(feuille As Worksheet, nomcolmois As String, strRMois As String, nomcolnoms As String, strRNom As String, nomColValeurs As String) As String
  cherche1 = "Pas de données"
  Dim intNColNom As Integer, intNColVal As Integer
  Dim c As Range
  With feuille.Rows(1)
     Set c = .Find(nomcolmois, LookIn:=xlValues)
     If Not c Is Nothing Then intNColmois = c.Column
     Set c = .Find(nomcolnoms, LookIn:=xlValues)
     If Not c Is Nothing Then intNColNom = c.Column
     Set c = .Find(nomColValeurs, LookIn:=xlValues)
     If Not c Is Nothing Then intNColVal = c.Column
 End With
 derligne = feuille.Cells(Rows.Count, intNColmois).End(xlUp).Row
 With Range(Cells(1, intNColmois), Cells(derligne, intNColmois))
    Dim firstaddress As String
    Set c = .Find(strRMois, LookIn:=xlValues)
    If Not c Is Nothing Then
    firstaddress = c.Address
    Do
        Set c = .FindNext(c)
        If Not c Is Nothing And .Cells(c.Row, intNColNom) = 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 Function

Attention :
Note mon insistance ici :
derligne = feuille.Cells(Rows.Count, intNColmois).End(xlUp).Row
 With Range(Cells(1, intNColmois), Cells(derligne, intNColmois))
au lieu de 
With feuille.Range("A1", Range("A1").End(xlDown))
En effet : bien que tu aies précisé que ta première colonne n'avait pas de trous, rien ne te permet (je te rappelle que l'on va in fine en faire une fonction personnalisée) d'affirmer que tel sera toujours le cas.
____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

ucfoutu · Answer

Ah oui :
utilise aussi LookAt:= xlWhole dans  toutes  tes recherches de cette fonction
Exemple :
Set c  = .Find(nomcolnoms, LookIn:=xlValues, LookAt:=xlWhole)

cette précaution évitera qu'il ne considère, par exemple, avoir trouvé "nom" alors que la cellule contient par exemple "prénoms".
Valable ici partout où tu utilises .find


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

cs_mayga · Answer

Bonjour,
Je viens de voir les messages, il y a quelque chose que je ne comprend pas.
derligne me renvoie le numéro de la dernière ligne utilisée.
 Mais rien ne me précise l'emplacement de la feuille ou chercher quand je vais utiliser With Range(...
Il ne faudrait mettre : with feuille.Range(...?

cs_mayga · Answer

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.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 long
intNLigne = feuille.Cells(Rows.Count, intNColMois).End(xlUp).Row
With 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
    firstaddress = c.Address
    Do
        Set c = .FindNext(c)
        If Not c Is Nothing And .Cells(c.Row, intNColNom) = 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 Function

Bon j'ai testé et comme je le pensais ça ne fonctionne que lorsque je me trouve dans la feuille de donnée lorsque j'exécute la procédure.
J'ai essayé de rajouté feuille. devant range mais ça me provoque une erreur!

ucfoutu · Answer

Tu as diablement raison !
Il y avait aussi un autre couic si double critère rencontré dès la première occurence du premier 
Correction, donc :
Private Function cherche1(feuille As Worksheet, nomcolmois As String, strRMois As String, nomcolnoms As String, strRNom As String, nomColValeurs As String) As String
  cherche1 = "Pas de données"
  Dim intNColNom As Integer, intNColVal As Integer
  Dim c As Range
  With feuille
    With .Rows(1)
      Set c = .Find(nomcolmois, LookIn:=xlValues)
      If Not c Is Nothing Then intNColmois = c.Column
      Set c = .Find(nomcolnoms, LookIn:=xlValues)
      If Not c Is Nothing Then intNColNom = c.Column
      Set c = .Find(nomColValeurs, LookIn:=xlValues)
      If Not c Is Nothing Then intNColVal = c.Column
    End With
    derligne = .Cells(Rows.Count, intNColmois).End(xlUp).Row
    With feuille.Range(.Cells(1, intNColmois), .Cells(derligne, intNColmois))
      Dim firstaddress As String
      Set c = .Find(strRMois, LookIn:=xlValues)
      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 feuille.Cells(c.Row, intNColNom).Text = strRNom Then ' 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
Dis-moi si tout va maintenant bien pour toi.
____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

cs_mayga · Answer

Non ça ne marche toujours pas,
En fait j'avais déjà tenté cette solution en rajoutant un with.feuille au départ

With feuille.Range(.Cells(1, intNColmois), .Cells(derligne, intNColmois))


ici me renvoie une erreur!

et comme ceci:
With Range(Cells(1, intNColMois), Cells(intNLigne, intNColMois))

me renvoie "Pas de données" (lorsque au départ je suis sur une autre feuille)

ucfoutu · Answer

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

ucfoutu · Answer

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

ucfoutu · Answer

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

cs_mayga · Answer

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

cs_mayga · Answer

Non ça ne marche pas,
autre problème comment fait on pour transmettre son mail en (MP)?

ucfoutu · Answer

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

cs_mayga · Answer

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.

ucfoutu · Answer

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

bigfish_le vrai · Answer

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+

ucfoutu · Answer

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

ucfoutu · Answer

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

cs_mayga · Answer

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.
???

cs_mayga · Answer

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?

ucfoutu · Answer

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

ucfoutu · Answer

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

cs_mayga · Answer

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.

cs_mayga · Answer

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.

cs_mayga · Answer

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.

ucfoutu · Answer

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

cs_mayga · Answer

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.

cs_mayga · Answer

Oui, vous avez raison je crois que pour le flexibilité c'est pas trop ça.

cs_mayga · Answer

Pourquoi pas un fichier d'aide.
Je vais chercher un tuto la dessus.

Je crois que j'ai suffisamment usé de votre temps, encore merci pour tout. Vous avez résumé en trois ligne ce qui au départ en prenait 30.

cs_mayga · Answer

Je poste le code légèrement modifié, surtout au niveau de la déclaration, adapté à mes besoins.

Public Function cherche2(f As String, ByVal strRmois As Range, ByVal strRNom As Range, ByVal nomColValeurs As Range, Optional nomcolmois As String "Mois", Optional nomcolnoms As String "Nom") As String
  Application.Volatile
  cherche2 = "Pas de données"
  
  Dim feuille As Worksheet
  Set feuille = Worksheets(f)
  Dim intNColNom As Integer, intNColVal As Integer, c As Range
    With feuille.Rows(1)
      Set c = .Find(nomcolmois, LookIn:=xlValues)
      If Not c Is Nothing Then intNColmois = c.Column
      Set c = .Find(nomcolnoms, LookIn:=xlValues)
      If Not c Is Nothing Then intNColNom = c.Column
      Set c = .Find(nomColValeurs, LookIn:=xlValues)
      If Not c Is Nothing Then intNColVal = c.Column
    End With
    
    derligne = feuille.Cells(Rows.Count, intNColmois).End(xlUp).Row
    Dim plage As Range, R As Range
    Set plage = feuille.Range(feuille.Cells(1, intNColmois), feuille.Cells(derligne, intNColmois))
    For Each c In plage
      If c.Value strRmois And feuille.Cells(c.Row, intNColNom).Text strRNom Then
        cherche2 = feuille.Cells(c.Row, intNColVal).Value: Exit Function
      End If
    Next
    Set feuille = Nothing
    Set c = Nothing
End Function

Plage variable et fonction

42 réponses

Votre réponse

Discussions similaires