Discussion :

[tbuhagiar]

Bonsoir à tous,

(Encore) un problème de débutant (désolé, j'espère pouvoir vous solliciter pour des choses plus complexes bientôt!!)

Voici mon soucis :
j'ai crée une macro pour réaliser un tableau dynamique à partir d'une base de données évolutive (d'où le dynamique)

Le code fonctionne a priori puisque je n'ai aucun message d'erreur.
Mais quand je lance la macro, je ne trouve pas mon tableau.

Ça doit être une sombre histoire de détail oublié mais je vous laisse me guider.
Voici le code que j'ai crée :

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
Sub Test_création_tableau_dynamique()
derniere_ligne = Range("A:Q").End(xlDown).Row 'dernière ligne de la base de données
 
 
Dim tab_exemple()
ReDim tab_exemple(derniere_ligne - 2, 2)
 
'Enregistrement des valeurs dans le tableau
For i = 0 To dernière_ligne - 2
    tab_exemple(i, 0) = Range("A" & i + 2)
    tab_exemple(i, 1) = Range("B" & i + 2)
    tab_exemple(i, 2) = Range("C" & i + 2)
    tab_exemple(i, 3) = Range("P" & i + 2)
    tab_exemple(i, 4) = Range("Q" & i + 2)
Next
 
End Sub

D'avance, merci à tous pour votre aide. dans l'idéal, s'il pouvait s'afficher sur une nouvelle feuille, ce serait top

[mercatog]

Commence par faire en tête de ton module

Code :

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

Option Explicit

Tu sauras qu'entre dernière_ligne et derniere_ligne il y a une différence énorme.

Ensuite pour récupérer une plage de cellules dans une variable tableau, il est souhaitable de le faire en une fois (dans ton cas, tu n'as pas besoin de toutes les colonnes mais bon, on ne perd que peu si on prend toutes les colonnes)

Code :

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

1
2
3
4
5
6
7
8
9
Sub Test_Creation_tableau_dynamique()
Dim Derniere_Ligne As Long
Dim Tab_Exemple
 
With Worksheets("Feuil1")
    Derniere_Ligne = .Cells(.Rows.Count, "A").End(xlUp).Row    'dernière ligne de la colonne A
    Tab_Exemple = Range("A2:Q" & Derniere_Ligne)
End With
End Sub

Tab_Exemple(1, 1) donne A2
Tab_Exemple(5, 3) donne C6

[nibledispo]

Bonjour,

Je ne suis pas certain que la solution réponde au problème posé.

En effet, tbuhagiar si je le comprends bien, souhaite créer un tableau à partir d'un nombre défini de colonne ( A B C P Q) de la BD. je pense que les colonnes D E F ... ne sont pas toutes vides et même si elles l'étaient, elles seraient inutile dans le tableau.

Une simple copie ne peut donc convenir.

Par ailleurs, la solution du demandeur qui va refaire toute la boucle à chaque fois qu'une ligne est rajoutée dans la BD n'est pas des plus séduisantes

Je me demande si l'emploi des tableaux de la feuille (insertion, tableau) qui sont dynamiques par essence (pas besoin de redimensionner) n'est pas une solution satisfaisante. C'est ce que semble préconiser Pierre Fauconnier dans une autre discussion si je l'ai bien compris.
Cela suppose que chaque évolution dans la BD soit immédiatement répercutée dans le tableau

Cordialement,

[mercatog]

Paul, bonjour

Quel est la valeur ajoutée de ton intervention à ceci

(dans ton cas, tu n'as pas besoin de toutes les colonnes mais bon, on ne perd que peu si on prend toutes les colonnes)

L'important de mon post est de montrer l'utilité de remplissage des variables tableaux en évitant les boucles sur les cellules de la feuille Excel. (Qui prennent un temps démesuré).

Dans le cas particulier et je me répète on ne perd que peu si on prend toutes les colonnes, rien ne l'empêches d'utiliser 2 variables, l'une pour A:C et l'autre pour P:Q ou accessoirement de remplir en une seule fois un tableau temporaire de la plage A:Q et le remplissage du tableau final se fera en bouclant sur les éléments du tableau temporaire et non sur les cellules de la feuille.

Voilà, j'espère te relire.

[nibledispo]

Bonjour Mercatog,

Aucune prétention à une quelconque valeur ajoutée dans mes interventions mais plutôt une volonté de comprendre.

Dans ce cas particulier:

je me demandais quelle pouvait être l’intérêt de recopier une partie des données de la BD.
Le remplacement de la BD par un tableau peut avoir une justification (rapidité, ......) mais mettre en double certaines informations présente plutôt un risque ?

la suite de mon propos découlait de ce questionnement

Par ailleurs, la solution du demandeur qui va refaire toute la boucle à chaque fois qu'une ligne est rajoutée dans la BD n'est pas des plus séduisantes

.

Va-t-il recopier l'entièreté des lignes de la BD à chaque fois que celle-ci évoluera ?

D'ou ma suggestion bonne ou mauvais sur l'emploi des tableaux (Pierre Fauconnier) ...

Un jour peut-être je pourrai faire des propositions de code. peut-être !

Cordialement.

[mercatog]

Paul bonsoir

Je faisais allusion à l'utilisation des boucles sur des cellules d'une feuille pour remplir une variable tableau au lieu d'une affectation directe.

Pour la seconde partie de ta suggestion, en l'occurrence l'emploi des tableaux à partir de Xl2007, je ne la commente pas puisque tu as entièrement raison

[Invité]

Bonjour,

Si le tableau est rectangulaire, il n'y a pas de problème sur dernière ligne, mais éventuellement, si la base de donnée sur les colonnes AQ n'est pas rectangulaire et que la colonne D par exemple est plus longue que la colonne A, il faut boucler sur toutes les colonnes comprise entre A et Q.

Un exemple, en reprenant le code de Mercatog:

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
 
Sub Test_Creation_tableau_dynamique()
    Dim Derniere_Ligne As Long
    Dim TabDerLigne() As Long
    Dim Tab_Exemple
    Dim i As Long
    Dim Rng As Range
 
    Set Rng = Worksheets("Feuil1").Range("A:Q")
    With Rng
        ReDim TabDerLigne(1 To .Columns.Count)
        For i = 1 To .Columns.Count
            TabDerLigne(i) = .Cells(.Rows.Count, i).End(xlUp).Row
            Debug.Print .Cells(.Rows.Count, i).Address
        Next i
    End With
 
    Derniere_Ligne = 1 'pas utile cependant
    For i = LBound(TabDerLigne) To UBound(TabDerLigne)
        If TabDerLigne(i) > Derniere_Ligne Then Derniere_Ligne = TabDerLigne(i)
    Next i
 
    MsgBox Derniere_Ligne
 
    Tab_Exemple = Range(Rng.Cells(2, 1).Address, Rng.Cells(Derniere_Ligne, Rng.Columns.Count).Address)
    MsgBox Range(Rng.Cells(2, 1).Address, Rng.Cells(Derniere_Ligne, Rng.Columns.Count).Address).Address
 
    Set Rng = Nothing
End Sub

Après, la valeur ajouté n'est pas spécialement beaucoup plus grande que celle nibledispo, mais ça permet d'éviter une éventuelle erreur sur le dimensionnement du tableau en se basant sur le Maximum du n° de ligne de d'une plage plutôt que sur une unique colonne.

EDIT:

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_Creation_tableau_dynamique()
    Dim Derniere_Ligne As Long
    Dim TabDerLigne() As Long
    Dim Tab_Exemple
    Dim i As Long, RngNLig As Long, RngNCol As Long
    Dim Rng As Range, PlageFinal As Range
 
    Set Rng = Worksheets("Feuil1").Range("A:Q")
    With Rng
        RngNCol = .Columns.Count
        RngNLig = .Rows.Count
        ReDim TabDerLigne(1 To RngNCol)
        For i = 1 To RngNCol
            TabDerLigne(i) = .Cells(RngNLig, i).End(xlUp).Row
            Debug.Print .Cells(RngNLig, i).Address
        Next i
    End With
 
    Derniere_Ligne = 1 'pas utile cependant
    For i = LBound(TabDerLigne) To UBound(TabDerLigne)
        If TabDerLigne(i) > Derniere_Ligne Then Derniere_Ligne = TabDerLigne(i)
    Next i
 
    MsgBox Derniere_Ligne
 
    With Rng ' A2 à Qn
        Set PlageFinal = Range(.Cells(2, 1).Address, .Cells(Derniere_Ligne, RngNCol).Address)
    End With
 
    MsgBox PlageFinal.Address
    Tab_Exemple = PlageFinal.Value
 
    Set Rng = Nothing
End Sub

[patricktoulon]

bonjour

si le probleme est la difference en terme de ligne de chaque colonne pose probleme pour la construction d'un tableau en direct
on peut utiliser le split usedrange.address pour determiner le nombre de ligne

exemple

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
Sub test_tableau_en_direct()
Dim tableau
Dim fin As Variant
fin = Split(Split(ActiveSheet.UsedRange.Address, ":")(1), "$")(2)
tableau = Range("A2:G" & fin)
 
'test verification :::::::facultatif
For i = 1 To UBound(tableau)
Debug.Print tableau(i, 1) & "!" & tableau(i, 2) & "!" & tableau(i, 3) & "!" & tableau(i, 4) & "!" & tableau(i, 5) & "!" & tableau(i, 6) & "!" & tableau(i, 7)
Next
End Sub

en effet le nom d'une plage est composée de 2 données séparées par un doublepoint
chaque partie comporte un "$" pour la colonne et un pour la ligne donc
on coupe par les deux points pour la derniere cellule et par les dollards pour la ligne de la derniere cellule
ainsi si on ne veux pas perdre de ligne avec le xl(up).end sur une colonne par exemple on utilisera le split sur le usedrange.address

Au plaisir

[Invité]

Salut

Ici, je pense que le problème vient du UsedRange qui est en Rapport avec la Feuille entière. C'est la même chose pour SpecialCells. Pourtant, ça aurait été la meilleur solution, mais on ne peut pas définir par exemple:
- Range("A:Q").UsedRange...
ou alors
- Range("A:Q").SpecialCells...
Ça ne génère pas d'erreur pour .SpecialCells(xlCellTypeLastCell), mais le résultat n'est pas bon, il se fie à la feuille plutôt qu'à une plage restreinte et déterminé.
Donc, à voir, mais je pense qu'à part boucler ou trouver une fonction perso ou pré-faite (tel que find), il n'y a pas énormément de solution.

A moins qu'il y ai une technique pour utiliser UsedRange et SpecialCells sur une plage restreinte ?

A+


P.S.: Tu vois ce que je veux dire pour UsedRange ? Une cellule utilisé en A1 et en YY10000 générera une Plage A1:YY10000, même si les données utiles sont situé en C5:G50.