Stockage de la valeur d'une formule dans une variable

Version imprimable

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:

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

29/04/2015, 00h12
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é?
29/04/2015, 08h51
Coxtox

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

Cordialement

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:

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

29/04/2015, 10h09
gFZT82

Bonjour,

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

Exemple :

Code:

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

Cordialement.
29/04/2015, 12h11
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 !! :marteau:

Cordialement
29/04/2015, 12h25
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.. ?

Citation:

Envoyé par Coxtox

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

Bonjour

Code:

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

29/04/2015, 13h11
gFZT82

Citation:

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.

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:

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:

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,

29/04/2015, 15h28
joe.levrai
Oula, j'ai oublié un truc dans cette ligne de code :mrgreen:

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:

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
29/04/2015, 16h32
Coxtox

1 pièce(s) jointe(s)

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

Pièce jointe 176426

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,

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:

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

Bonjour,

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

Code:

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.

30/04/2015, 09h59
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.