boucles sur plage de cellule

[diombo]

Bonjour,
Je suis nouveau en VBA pour excel.
J'ai un code qui marche en vba access, sur une table.
Ce code permet de parcourir toute la table afin de faire des comptage.
J'aimerais faire la même chose avec excel.

voici le code en vba access:

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
Do Until rsAn.EOF 
    annee = rsAn.Fields(0)
    Debug.Print "Début Année : " & rsAn.Fields(0)
    sqlA = "SELECT centre, annee FROM Ventes GROUP BY centre, annee HAVING annee =" & annee
    Set rsAc = db.OpenRecordset(sqlA, dbOpenDynaset) ' filtre au niveau année
 
    If rsAc.RecordCount > 0 Then
        Do Until rsAc.EOF
		centre = rsAc.Fields(0)
			sqlB="SELECT vente,centre, annee FROM Ventes GROUP BY vente, centre, annee HAVING annee =" & annee
			sqlB= sqlB & " AND centre='" & centre & "'"
 
			Set rsBc = db.OpenRecordset(sqlB, dbOpenDynaset) ' filtre au niveau année  et centre
			If rsBc.RecordCount > 0 then
				Do Until rsBc.EOF 
					Traitement ' les traitements sur les données filtrées
				rsBc.MoveNext
				DoEvents
				Loop
			End If
		rsAc.MoveNext
		DoEvents
		Loop
 
	End If
rsAn.MoveNext
DoEvents
Loop

Quel est l'équivalent pour excel sans passer par un recordset?
Merci d'avance

[Sclarckone]

Le modèle objet d'Excel est naturellement différent de celui d'Access et il n'y a donc pas de traduction automatique...

Tout va dépendre de la structure de ton fichier Excel. Prenons 2 cas extrêmes:

- Tu as un classeur Excel par année qui contient un onglet par centre:

Dans ce cas, tu pourrais utiliser un "super-classeur" qui va ouvrir successivement tous les classeurs par année et au sein de ceux-ci faire le traitement que tu veux centre par centre et écrire les résultats dans un onglet du "super-classeur".

- Tu as toute ta table brute dans un seul onglet d'un seul classeur:

Dans ce cas, il faut parcourir toutes les lignes de ton onglet et faire des tests sur les valeurs des colonnes 'annee' et 'centre' et appliquer les traitements en fonction du résultat des tests.


Une autre alternative peut être d'écrire dans ton fichier Excel depuis ta base de données Acess.

[diombo]

Bonjour,
Je suis dans le deuxième cas, l'ensemble des données brutes dans une feuille.
Pour le traitement, comme l'illustre le code qui marche en vba access, l'objectif est de faire des filtres par année,puis par centre et de faire des traitements (somme, moyenne) sur les lignes filtrées.
Je doit répéter ce travail pour chaque année, centre de la feuille.
Le résultat obtenu devrait ressembler à un tableau croisé avec en colonne les traitement (moyenne, somme) et en lignes les années et les centres.
Encore une fois merci.

[Sclarckone]

Je n'ai pas l'habitude d'en utiliser mais justement, n'est-il pas possible de faire ce que tu veux simplement avec un TCD (auquel ça irait plus vite que d'écrire du code)?

S'il faut que tu en passes par l'écriture d'une macro VBA, commence par essayer de faire ce que tu peux par toi-même et on t'aidera lorsque tu bloques.

[diombo]

J'ai bien avancé et voici le premier code.
J'arrive à faire des boucles de filtres.
Il manque la dernière étape, faire des calculs (somme, moyenne, min, max, ...) sur les cellules filtrées.

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
Dim clD As Workbook
Dim indiCateur As Worksheet     ' déclaration classeur
Dim synThese As Worksheet       ' déclaration feuille
Dim dData As Worksheet          ' identique
Dim i As Integer
Dim jData As Range              ' déclaration plage de données
Dim Annee As Range              ' identique
Dim Mois As Range               ' identique
 
Sub traitement()
    ' affectation des objets excel
    Set clD = Application.ActiveWorkbook
    Set indiCateur = clD.Sheets("Indicateur")
    Set synThese = clD.Sheets("synthese")
    Set dData = clD.Sheets("dico")
 
    ' selection des ranges à copier
    ' affectation des colonnes sélectionner à l'objet jData
    Set jData = indiCateur.Range("a1,b1,g1").EntireColumn
 
    ' (1) Effacer les cellules de la feuille synthèse avant de (2) copier les colonne selectionnées
    synThese.Cells.Clear
    synThese.AutoFilterMode = False
    jData.Copy Destination:=synThese.Range("a1")
 
    ' copie des listes uniques pour les éléments du tableau de synthése
    ' ces éléments vont servir d'input pour la gestion des filtres
    dData.Cells.Clear
    With synThese
        .Range("A1:A23").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=dData.Range("a1"), Unique:=True
        .Range("B1:B23").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=dData.Range("b1"), Unique:=True
    End With
 
    ' affectation des listes uniques à des plages de données
    Set Annee = dData.Range("a1").EntireColumn      ' pour les années
    Set Mois = dData.Range("b1").EntireColumn       ' pour les mois
 
    Dim derA As Integer, derM As Integer            ' derA pour la dernière année et derM pour le dernier Mois
    Dim i As Integer, j As Integer
    derA = dData.Range("a1").End(xlDown).Row        ' le numéro de la dernière année donc le nombre d'années distincte
    derM = dData.Range("b1").End(xlDown).Row        ' le numéro du dernier mois donc le nombre de mois
 
    Debug.Print " nombre de mois : " & derM & " et nombre d'années : " & derA
 
    ' définition des boucles pour faire les filtres
    ' les boucles seront imbriquées pour rechercher toutes les combinaisons d'année et de mois
 
    synThese.Activate
    For i = 2 To derA
        If dData.Range("a" & i).Value = "" Then
            Debug.Print " donnee vide"
        Else
 
            Debug.Print "Annee = " & dData.Range("a" & i).Value
            synThese.AutoFilterMode = False
            With Range("a1").CurrentRegion
                .AutoFilter field:=1, Criteria1:=dData.Range("a" & i).Value
                .Interior.Color = vbRed
'                .Select
            End With
            For j = 2 To derM
            Debug.Print " **** Mois = " & dData.Range("b" & j).Value
 
                With Range("a1").CurrentRegion
                    .AutoFilter field:=1, Criteria1:=dData.Range("a" & i).Value
                    .AutoFilter field:=2, Criteria1:=dData.Range("b" & j).Value
                    .Interior.Color = vbYellow
                    .Select                             ' selection du filtre obtenu
                End With
                If synThese.Range("a1").End(xlDown) > 1 Then
 
                    For Each ligne In synThese.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Rows
                        MsgBox Range("B" & ligne.Row + 1).Value
                    Next ligne
                End If
 
                ' vérification si le filtre final est vide
                ' si vide alors on ne fait rien
                ' si non vide alors on fait les opérations sur le filtre sélectionne
 
                ' Recherche à faire: Tester si selection filtre vide
                ' Opération sur filtre
 
            Next j
        End If
 
    Next i
 
End Sub

Le principe est le suivant:
1. je copie les colonnes sur lesquelles je veux travailler pour plus de précaution
2. je crée une liste unique sur les colonnes devant servir de filtre
3. à partir de ces listes, je défini des filtres avec les différentes valeurs contenue dans ces listes uniques
4. je recupère les lignes filtrées pour faire des calculs dessus.

Blocage; j'aimerais faire range("D1").value= somme de la collonne C pour les lignes filtrées (visibles).
Merci

[corona]

Envoyé par diombo

Blocage; j'aimerais faire range("D1").value= somme de la collonne C pour les lignes filtrées (visibles).Merci

Petit exemple :

Code :

range("D1") = Application.WorksheetFunction.Subtotal(9, Range("plage"))

[jfontaine]

Bonjour,

Le principe est le suivant:
1. je copie les colonnes sur lesquelles je veux travailler pour plus de précaution
2. je crée une liste unique sur les colonnes devant servir de filtre
3. à partir de ces listes, je défini des filtres avec les différentes valeurs contenue dans ces listes uniques
4. je recupère les lignes filtrées pour faire des calculs dessus.

Personnellement, je trouve qu'a partir du point 3 tu te compliques.
Excel est capable de faire ces calculs avec des formules (Voir Sommeprod)

Bien sur ce n'est qu'un point de vue