Discussion :

[Valerie9999]

Bonsoir,

Je souhaite créer une fonction en VBA qui fait appel à un autre onglet que celui qui est actif.
J'ai crée ma fonction dans un module VBA, mais quand je l'appelle dans l'onglet, il me fait les calculs dans l'onglet où je me situe, et non pas dans celui que j'appelle.
Voici ma fonction :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Public Function effectif_1_critere(nom_critere As String, valeur_critere) As Integer
 
Sheets("Effectif").Select
Range("A1").Select
 
nbre_lig = Application.WorksheetFunction.CountA(Range("A1", [A1].End(xlDown)))
nbre_col = Application.WorksheetFunction.CountA(Range("A1", [A1].End(xlToRight)))
 
num_col_critere1 = num_col(nom_critere, nbre_col)
num_col_comptage = num_col("COMPTAGE", nbre_col)
 
effectif_1_critere = Application.WorksheetFunction.SumIf(Range(Cells(2, num_col_critere1), Cells(nbre_lig, num_col_critere1)), valeur_critere, Range(Cells(2, num_col_comptage), Cells(nbre_lig, num_col_comptage)))
 
End Function

Je lance ma fonction dans l'onglet "Stats" et malheureusement il ne va pas chercher mes données dans l'onglet Effectif.
Comment faire ?

Par avance merci pour votre aide.

Valérie

[Nicolas JACQUIN]

Bonjour,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
with WorskSheets("Effectif")
'ton code
end With

[Valerie9999]

Bonjour,

Je viens de tester et cela ne fonctionne pas. Lorsque je tape ma formule dans un onglet différent de Effectif, il me met #VALEUR!

Avez vous une autre idée ?

Par avance merci

Valérie

[pijaku]

Bonjour
Dans un bloc With, il faut ajouter un point devant les objets s'y référant.
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
With Sheets("Feuil1")
    .Range("A1") = 3 'ici il s'agit de A1 Feuil1
    Range("A1") = 4 'ici il s'agit de A1 feuille active
End With

[Valerie9999]

Merci pour cette info.

Mais comment puis je faire pour récupérer le nombre de lignes et de colonnes de mon onglet effectif, et programmer ensuite l'équivalent d'une somme si en fonction de certains critères ?
Le but de ma fonction est de faire quelque chose de simple et "transparent" pour un utilisateur.

Par avance merci

Valérie

[RyuAutodidacte]

Bonjour,

- Comme l'on expliqué pijaku et Nicolas utiliser With … End With
En n'oubliant pas de mettre le . devant les Range (.Range) … voir dans l'aide vba le fonctionnement de With
- Peux-tu modifier ton 1er message pour mettre le code entre balises pour que cela soit plus lisible, un exemple se trouve dans ma signature.
- petits conseils, il est important d'expliquer son problème de manière claire et exhaustive avec le tenants et aboutissants, afin que l'on puisse mieux cerner le problème et que l'on puisse donner des réponses adéquates.

[Valerie9999]

Bonjour,

J'ai modifié mon programme et fais un test sur un exemple plus simple, pour juste faire apparaître le nombre de lignes de mon onglet Effectif (en étant dans l'onglet Stats) et ça ne fonctionne toujours pas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Function effectif_1_critere(nom_critere As String, valeur_critere) As Integer
 
 
With Sheets("Effectif")
 
    .Range("A1").Select
    nbre_lig = Application.WorksheetFunction.CountA(Range(Selection, Selection.End(xlDown)))
 
    effectif_1_critere = nbre_lig
 
End With
 
 
End Function

Je pense pourtant avoir fait comme expliqué ci-dessus ...

Par avance merci pour votre aide

[riaolle]

Bonjour,

ça ne fonctionne toujours pas

Peux-tu être un peu plus précise? Message d'erreur? si oui, où? etc...
Evite d'utiliser Select et Activate dans ton code, cela entraîne souvent des problèmes difficilement gérables. En fait, ton code peut se résumer à :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Function effectif_1_critere(nom_critere As String, valeur_critere) As Integer 
With Sheets("Effectif")
    effectif_1_critere = Application.WorksheetFunction.CountA(Range(.Range("A1"), .Range("A1").End(xlDown)))
End With
End Function

Et comme tu commences à la cellule A1, il y même encore plus simple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Function effectif_1_critere(nom_critere As String, valeur_critere) As Integer 
    effectif_1_critere = Sheets("Effectif").Range("A1").End(xlDown).Row
End Function

Dans le code de ton premier message, à quoi correspond num_col?

[Valerie9999]

Bonjour,

Merci pour votre aide, ça avance bien !

Ma fonction num_col me permet de chercher le numéro d'une colonne, selon le nom de l'intitulé de la colonne.
Je l'ai défini ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Function num_col(onglet As String, nom_col As String, max_col) As Integer
 
j = 1
Dim a As String
a = Sheets(onglet).Cells(1, 1).Value
 
While a <> nom_col
    j = j + 1
    a = Sheets(onglet).Cells(1, j).Value
Wend
 
If j = max_col + 1 Then
    num_col = 0
Else
    num_col = j
End If
 
End Function

Ainsi ça me permet de pouvoir gérer les modifications éventuelles des colonnes dans un onglet.

J'ai donc modifié ma fonction ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Function effectif_1_critere(nom_critere As String, valeur_critere) As Integer
 
    nbre_lig = Sheets("Effectif").Range("A1").End(xlDown).Row
    nbre_col = Sheets("Effectif").Range("A1").End(xlDown).Columns
 
    num_col_critere1 = num_col("Effectif", nom_critere, nbre_col)
    num_col_comptage = num_col("Effectif", "COMPTAGE", nbre_col)
 
 
    effectif_1_critere = Application.WorksheetFunction.SumIf(Sheets("Effectif").Range(Cells(2, num_col_critere1), Cells(nbre_lig, num_col_critere1)), valeur_critere, Sheets("Effectif").Range(Cells(2, num_col_comptage), Cells(nbre_lig, num_col_comptage)))
 
 
End Function

Lorsque je suis dans mon onglet Stats, j'appelle ma fonction de la façon suivante, dans la cellule F74 : =effectif_1_critere("GROUPE.DIRECTION-SERVICE";B74) et j'ai le message #VALEUR!

Par avance merci pour votre aide.

Valérie

[riaolle]

As-tu vérifié que ta fonction num_col marchait correctement? Je suis sceptique, car selon moi si elle ne trouve pas le nom, elle effectue une boucle infinie. Si a n'est jamais égal à nom_col, alors il continue à chercher indéfiniment. Il n'y a pas de condition d'arrêt. Il faudrait que tu utilises max_col plus tôt en ajoutant une condition au While.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Function num_col(onglet As String, nom_col As String, max_col) As Integer
j = 1
Dim a As String
a = Sheets(onglet).Cells(1, 1).Value
While a <> nom_col And j <= max_col + 1
    j = j + 1
    a = Sheets(onglet).Cells(1, j).Value
Wend
If j = max_col + 1 Then
    num_col = 0
Else
    num_col = j
End If
End Function

Mais, en fait, il y a plus simple, car il y a une fonction VBA qui te permet de faire cela : Application.Match. Cela t'évite de faire une boucle (moins efficace).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Function num_col(onglet As String, nom_col As String) As Integer
    If isError(Application.Match(nom_col, Sheets(onglet).Range("1:1"), 0)) Then
        num_col = 0
    Else
        num_col = Application.Match(nom_col, Sheets(onglet).Range("1:1"), 0)
    End If
End Function

Attention :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

nbre_col = Sheets("Effectif").Range("A1").End(xlDown).Column

Column sans "s".

Essaie de déclarer tes variables pour voir si tu n'as pas de problème avec le type de variables. Notamment valeur_critere qui est un Range. Pour obtenir la valeur de la cellule, utilise la propriété Value :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

valeur_critere.Value

Enfin es-tu certaine que la somme obtenue est un entier?

[Philippe Tulliez]

Bonjour,

Ma fonction num_col me permet de chercher le numéro d'une colonne, selon le nom de l'intitulé de la colonne.

Voici une fonction qui renvoie le numéro de la colonne d'une plage

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Function ColumnNumber(ByVal wRng As Range, Label As String) As Integer
 ' Renvoie le n° de colonne d'une étiquette de colonne
 ' Author : http://philippe.tulliez.be
 ' Version 1.1
 ' Arguments
 ' wRng  (Range)  Plage de cellules
 ' Label (String) Nom de l'étiquette
 Dim fn As WorksheetFunction
 Set fn = Application.WorksheetFunction
 On Error Resume Next
 ColumnNumber = fn.Match(Trim(Label), wRng.Resize(1), 0)
 If ColumnNumber = 0 Then
  MsgBox "Plage de cellules : " & wRng.Resize(1).Address(external:=True) _
          & vbCrLf & "Pas trouvé l'étiquette de colonne : " & Label, _
          vbCritical, "Fonction ColumnNumber"
 End If
 On Error GoTo 0
End Function

Appelable depuis une procédure VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Sub TestColumnNumber()
 MsgBox "N° de colonne " & ColumnNumber(Range("Feuil1!A1:D6"), "Nom")
End Sub

Directement depuis excel (même feuille)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ColumnNumber(A1:D6;"Nom")

ou (autre feuille du classeur)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ColumnNumber(Feuil1!A1:D6;"Nom")

[Valerie9999]

Merci Riaolle, je n'avais pas vu que ma fonction num_col pouvait être infinie.

J'ai modifié mon code de cette façon :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Function effectif_1_critere(nom_critere As String, valeur_critere As String) As Integer
 
    nbre_lig = Sheets("Effectif").Range("A1").End(xlDown).Row
    nbre_col = Sheets("Effectif").Range("A1").End(xlDown).Column
 
    num_col_critere1 = num_col("Effectif", nom_critere)
    num_col_comptage = num_col("Effectif", "COMPTAGE")
 
    effectif_1_critere = 0
 
    For i = 2 To nbre_lig
 
        If Sheets("Effectif").Cells(i, num_col_critere1).Value = valeur_critere Then
            effectif_1_critere = effectif_1_critere + Sheets("Effectif").Cells(i, num_col_comptage).Value
        End If
 
    Next i
 
End Function

et ça fonctionne ... Par contre je ne sais pas si c'est optimum au niveau programmation.

Merci à tous pour votre aide !

[Marc-L]

Bonjour,

l'optimum même par code est d'utiliser une simple formule de calculs …

___________________________________________________________________________________________________________

Je suis Paris, Egypte, Stockholm, London, Istanbul, Berlin, Nice, Bruxelles, Charlie, …