Discussion :

[Coxtox]

Bonjour à tous,

J'ai un petit problème de syntaxe dans mon code.

Comme le nom du sujet l'indique, j'aimerais stocker la valeur que renvoie une formule dans une variable VBA.

La formule a pour but de compter dans la feuille "Sélection globale" le nombre de valeurs distinctes de la variable métier (colonne C).
A savoir que ce nombre de métiers changent suivant le périmètre sélectionné par l'utilisateur. (La plage peut aussi bien s'étende de C2 à C3 que de C2 à C200.)

J'ai procédé avec l'enregistreur de macro mais une erreur "incompatibilité de type" intervient. Probablement lié à la déclaration de ma variable.

Voici le code associé :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
    Dim Nbmetier As Long
 
 
 
    Menu = ActiveSheet.Name
    Nbmetier = 0
 
    Nbmetier = Evaluate("SUM(1/COUNTIF('Sélection globale'!R[-42]C[-1]:R[-40]C[-1],'Sélection globale'!R[-42]C[-1]:R[-40]C[-1]))")
 
         Worksheets("Synthèse Atteinte Norme").Activate
         h.Range("B3").Select
 
         Selection.AutoFill Destination:=Range("B3:B" & Nbmetier)

Etant débutant en VBA, il existe surement des imperfections dans la syntaxe de mon code.

Je vous remercie par avance.

Cordialement

Pour reformuler, je souhaiterais connaître le nombre de valeurs distinctes d'une variable.

J'aimerais par la suite stocker ce nombre de valeurs distinctes dans une variable (ici Nbmetiers)

Cette variable me permettra par la suite d'appliquer un traitement de la cellule B3 à B"NBmetiers" (B8 si le nombre de métiers est de 8)

Cordialement

[gnain]

Bonjour,
j'ai de la difficulté à comprendre.
Est-ce que si ta_variable= "ABC1234AC214"

le nombre de valeur distincte serait de 7 ?
Est-ce que c'est 7 que tu veux récupéré?

[Coxtox]

Oui c'est bien 7 que je souhaite stocker dans la variable Nbmetier.

Cordialement

[joe.levrai]

Bonjour,

je ne comprend pas l'histoire du périmètre qui défini la liste de métiers en colonne C

voici une idée qui va récupérer la liste sans doublon des métiers en colonne C
le nombre d'éléments défini en suite la plage de remplissage de la colonne B de ton autre feuille

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
20
21
22
23
24
25
26
27
28
29
30
31
Sub Extracton()
 
Dim MesMetiers As Object
Dim Plage As Range
Dim Tablo
Dim i As Integer
 
'la liste des métiers
Set MesMetiers = CreateObject("Scripting.Dictionary")
 
With ThisWorkbook.Worksheets("Sélection globale")
    'la plage en colonne C
    Set Plage = Range(.Cells(2, 3), .Cells(Rows.Count, 3).End(xlUp))
    Tablo = Plage
 
    ' pour chaque valeur de la colonne C
    For i = LBound(Tablo, 1) To UBound(Tablo, 1)
        On Error Resume Next
        ' on l'ajoute à la liste s'il n'existait pas
        MesMetiers.Add Tablo(i, 1), Tablo(i, 1)
        On Error GoTo 0
    Next i
End With
 
' MesMetiers.Count est donc le nombre de métiers qu'on a trouvé
ThisWorkbook.Worksheets("Synthèse Atteinte Norme").Range("B3").AutoFill Destination:=Range("B3:B" & MesMetiers.Count)
 
Set Plage = Nothing
Set MonDico = Nothing
 
End Sub

[gFZT82]

Bonjour,

Tu peux aussi essayer d'utiliser une notation classique (A1) à la place de la notation RC.

Exemple :

Code :

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

Nbmetier = Evaluate("SUM(1/COUNTIF('Sélection globale'!D39:G39,'Sélection globale'!D39:G39))")

Cordialement.

[Coxtox]

Bonjour,

Pour répondre à joe.levrai, en fait, l'utilisateur sélectionne son périmètre d'étude Réseau --> Secteur --> Agence ... qui va filtrer les données d'un autre onglet en fonction du périmètre qu'il a choisi. Cette sélection va se placer dans l'onglet 'Sélection globale'. Ainsi la liste des métiers change suivant le périmètre sélectionné. (Avec ta méthode j'ai une erreur Autofill de la classe Range a echoué)

gFZT82, J'ai testé en notation classique, j'ai une erreur incompatibilité de type (que j'avais également avant).


Mon but est en fait d'étendre un traitement de la première ligne (B3 qui est le premier métier de la sélection globale) jusqu’au dernier métier distinct trouvé.

Par exemple, si il y a 4 métiers distincts dans sélection globale, le premier métier se trouvera à la ligne 3 et je veux étendre mon traitement de B3 à B7 (dernière ligne des métiers)

Au départ j'avais fait avec un compteur qui marchait bien seulement quand le nombre de métiers était inférieur à deux j'avais l'erreur Autofill)
C'est possible que je me complique un peu la tache !!

Cordialement

[joe.levrai]

quand tu parles d'autofill, en fait tu veux qu'en partant de B3, on écrive chaque métier trouvé ?

B3 = metier1
B4 = metier2
etc.. ?

[gnain]

Oui c'est bien 7 que je souhaite stocker dans la variable Nbmetier.

Bonjour

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
Sub Nombre_Diff()
 
    Dim Nbmetier As Integer
    ta_variable = "ABC1234AC214"
 
    For i = 1 To Len(ta_variable)
 
        tata = Mid(ta_variable, i, 1)
 
        If InStr(ta_variable, tata) > i - 1 Then
            Nbmetier = Nbmetier + 1
        End If
 
    Next
 
    MsgBox Nbmetier
 
End Sub

[gFZT82]

gFZT82, J'ai testé en notation classique, j'ai une erreur incompatibilité de type (que j'avais également avant).

Indique-moi ta ligne de code afin que je puisse voir la formule utilisée.

Si tu places la formule dans ta feuille, obtiens-tu un résultat correct ? (je suppose que oui puisque tu dis avoir utilisé l’enregistreur de macro).

Cordialement.

[Coxtox]

Je me suis un peu éparpillé entre les méthodes ...

J'ai une méthode avec le compteur qui marche à moitié !!!

Je vous explique :

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
20
21
22
23
24
25
26
27
28
 
  'Rechercher les différents métiers qui composent la zone de sélection ( feuille Sélection globale)
    Set Dicometier = CreateObject("scripting.dictionary")
    For Each cellulle In c.Range("C2:C1000")
        If Not Dicometier.Exists(cellulle.Value) Then Dicometier.Add cellulle.Value, cellulle.Value
    Next cellulle
 
'Importation dans la 1ère colonne de la feuille Synthèse Atteinte Norme des noms de métiers présents dans l'onglet sélection globale
    'Comptabilisation du nombre de métiers différents dans la zone de sélection
Dim ligne3 as integer
ligne3 = 2
    For Each Value In Dicometier.items
        h.Range("A" & ligne3) = Value 'h est la feuille Synthèse Atteinte Norme)
        ligne3 = ligne3 + 1
        cpt = cpt + 1
    Next Value
 
'Placement dans la cellule B3 de la feuille Syntèse Atteinte Norme
Worksheets("Synthèse Atteinte Norme").Activate
h.Range("B3").Select
 
 
'Calcul du nombre de personnes par métier de la sélection
If Not IsEmpty(Range("A3:A18")) Then
'Comptage du nombre de personne effectuant le métier    
Selection.FormulaArray = "=COUNTIF('Sélection globale'!R2C3:R1001C3,""=""&RC1)"
'Etends le comptage jusqu'àu dernier métier trouvé                
Selection.AutoFill Destination:=Range("B3:B" & cpt), Type:=xlFillDefault

Ce code marche niquel lorsque dans la valeur du compteur est supérieure ou égale à 3. Si il n'y a que deux métiers différents dans la sélection globale, la dernière ligne de code :

Code :

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

Selection.AutoFill Destination:=Range("B3:B" & cpt), Type:=xlFillDefault

m'indique l'erreur : la méthode Autofill de la classe Range a échoué.

En espérant que mon message n'est pas trop imbuvable ... En tous cas Je vous remercie de m'avoir aidé.

N’hésitez pas à me faire savoir si je ne suis pas clair...

Cordialement,

[joe.levrai]

Oula, j'ai oublié un truc dans cette ligne de code

si on prend uniquement cpt c'est pas bon, il faut prendre "cpt + 3", et également vérifier que cpt > 0

remplace cette ligne par

Code :

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

1
2
3
4
5
If cpt > 0 Then
    Selection.AutoFill Destination:=Range("B3:B" & 3 + cpt), Type:=xlFillDefault
Else
    MsgBox ("Aucun métier trouvé")
End If

et regarde si c'est ok

[Coxtox]

Je joins en PJ au message un exemple de mon problème ... ça sera beaucoup plus clair !!

Explication problème.xlsm

Dans ce fichier il y a 4 onglets, les deux premiers concernent le cas où ça marche et les deux derniers le cas où une erreur survient.

Les onglets "Sélection globale" regroupe les différents métiers, les onglets "Synthèse Atteinte Norme" sont les onglets ou je liste les différents métiers et compte le nombre de personnes dans chacun des métiers.

Le fichier contient deux macros :

- Une qui marche sur les deux premiers onglets car le nombre de métiers est suffisant
- L'autre qui ne marche pas car seuls deux métiers sont présents dans l'onglet "Sélection globale2"

La seule différence entre ces deux onglets ("Sélection globale" et "Sélection globale2") est le nombre de métiers distincts.

Lorsqu'on lance la macro "Cas_Marche_Pas_Executer_Sur_Onglet_Synthèse_AttNorme2" une erreur Autofill intervient alors que l'autre macro "Cas_Marche_Executer_Sur_Onglet_Synthèse_AttNorme"pas d'erreur.

En espérant être clair,

Cordialement,

[gnain]

Bonjour,
c'est tout à fait normal que ça "bug" car cpt n'est pas bien compter.
on ne peut pas avoir un Selection.AutoFill Destination:=Range("B3:B3") ... cela est impossible!

même dans ton sub qui fonctionne, il manque la dernière donnée car cpt démarre à 0.

premièrement il faut initialisé cpt = 2
parce que tu commence à la ligne 3.

ensuite il ne faut pas compter value = "", car ta plage contient des vides.
alors il faut mettre une condition pour compter cpt (voir première condition en rouge)

puis à la fin on remet une condition (voir 2e condition en rouge)
car si il y a trouvé seulement un métier alors cpt sera égal à 3 et cela est impossible ( Range("B3:B3") ).
( voir 2e condition en rouge, vous comprendrez )

et le tour est joué.
voilà! voir les différences en rouge

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Sub test()
    'Rechercher les différents métiers qui composent la zone de sélection ( feuille Sélection globale)
        Set Dicometier = CreateObject("scripting.dictionary")
        For Each cellulle In Sheets("Sélection globale2").Range("C2:C1000")
            If Not Dicometier.Exists(cellulle.Value) Then Dicometier.Add cellulle.Value, cellulle.Value
        Next cellulle
     
    'Importation dans la 1ère colonne de la feuille Synthèse Atteinte Norme des noms de métiers présents dans l'onglet sélection globale
        'Comptabilisation du nombre de métiers différents dans la zone de sélection
    Dim ligne3 As Integer
    cpt = 2
    ligne3 = 3
        For Each Value In Dicometier.items
            If Value <> "" Then
                Sheets("Synthèse Atteinte Norme2").Range("A" & ligne3) = Value
                ligne3 = ligne3 + 1
                cpt = cpt + 1
            End If
        Next Value
     
    'Placement dans la cellule B3 de la feuille Syntèse Atteinte Norme
    Worksheets("Synthèse Atteinte Norme2").Activate
    Sheets("Synthèse Atteinte Norme2").Range("B3").Select
 
    'Calcul du nombre de personnes par métier de la sélection
    If Not IsEmpty(Range("A3:A18")) Then
        'Comptage du nombre de personne effectuant le métier
        Selection.FormulaArray = "=COUNTIF('Sélection globale2'!R2C3:R1001C3,""=""&RC1)"
        'Etends le comptage jusqu'àu dernier métier trouvé
        If cpt > 3 Then
            Selection.AutoFill Destination:=Range("B3:B" & cpt), Type:=xlFillDefault
        End If
    End If
End Sub

[gFZT82]

Bonjour,

Une autre approche avec une comptabilisation directe du nombre d’occurrence des métiers avec le dictionnaire

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
20
21
22
23
24
25
26
27
28
29
30
31
Sub Cas_Marche_Executer_Sur_Onglet_Synthèse_AttNorme()
Dim Dicometier, k, i, n
Dim Cel As Range
Dim Ligne3 As Integer
Dim DerLigS As Long
    'Rechercher les différents métiers qui composent la zone de sélection ( feuille Sélection globale)
    'et comptabilisation du nombre d'occurence de ces métiers
    Set Dicometier = CreateObject("scripting.dictionary")
    With Sheets("Sélection globale")
        DerLigS = .Range("C" & Rows.Count).End(xlUp).Row
        If DerLigS = 1 Then Exit Sub
        For Each Cel In .Range("C2:C" & DerLigS)
            If Not Dicometier.Exists(Cel.Value) Then
                Dicometier.Add Cel.Value, 1
            Else
                Dicometier.Item(Cel.Value) = Dicometier.Item(Cel.Value) + 1
            End If
        Next Cel
    End With
    k = Dicometier.keys
    i = Dicometier.items
    Ligne3 = 3
    'Inscription dans la feuille Synthèse Atteinte Norme des noms de métiers et du nombre d'occurence
    With Sheets("Synthèse Atteinte Norme")
        For n = 0 To Dicometier.Count - 1
            .Range("A" & Ligne3) = k(n)
            .Range("B" & Ligne3) = i(n)
            Ligne3 = Ligne3 + 1
        Next n
    End With
End Sub

Cordialement.

[Coxtox]

Merci à vous deux ! Et à ceux qui m'ont répondu précédemment !

Je vous remercie pour vos explications Gnain ! J'y vois plus clair maintenant ! Ça fonctionne ! Ça m'as permis de remarquer que même mon code précédent oubliait de compter une ligne !

gFZT82, ce code est fonctionnel et voir plus simple que le précédent (Merci !) !

Deux approches différentes qui fonctionne parfaitement !

Toujours un plaisir d'avoir des réponses claires et variées !

Cordialement,

Bonne journée.