Utilisation des tableaux

Bonjour à tous,

J'en appel aux experts de la programmation à me filer un coup de main. Voici le problème posé :

J'ai une feuille excel comportant un nombre de lignes variable et un nombre de colonnes fixe (20).
Sur cette plage je souhaite "retraiter les données" et donc conserver la ligne seulement si elle répond au critère suivant : la cellule de la 5 ème colonne doit contenir 4 chiffres et une lettre(en derniere position de la chaine) : "####[A-Z]"

Pour l'instant j'ai un code qui tourne en scannant ligne à ligne la plage et effectue un delete à chaque ligne qui ne répond pas au critère. Problème: cela met trop de temps.

Apparemment le traitement de cette base de données par tableau me permettrait de gagner un temps conséquent. Seulement je ne suis que débutant et ne sait pas trop comment faire. Help me.

Bonjour,
Le filtre élaboré d'excel peut t'aider à résoudre ce problème, en utilisant les critères calculés.
A lire éventuellement Les filtres avancés ou élaborés dans Excel
Deux solutions (Nous supposons que la liste des données commence en cellule A1)
Soit, nous travaillons à l'envers et nous exportons les données répondant aux critères (4 chiffre et une lettre à l'envers). La formules à placer comme critères calculés

Code:

---

=ET(NBCAR(E2)=5;ESTTEXTE(DROITE(E2;1));ESTNUM(CNUM(GAUCHE(E2;4))))

---

Soit on filtre les lignes ne répondant pas aux critères et ensuite on les supprime. Cette procédure est un peu plus complexe.
Il faut avoir copié la ligne des titres au préalable (La ligne 2 pouvant faire partie des lignes cachées)

Code:

---

=NON(ET(NBCAR(E2)=5;ESTTEXTE(DROITE(E2;1));ESTNUM(CNUM(GAUCHE(E2;4)))))

---

C'est la même formule imbriquée dans la formule NON
Une fois les lignes filtrées
1) Sélectionner la plage (Ctrl+A)
2) Appeler l'outil Atteindre (touche F5), cliquer sur le bouton Cellules... et sélectionner Cellules visibles seulement
3) Faire un clic droit et sélectionner Supprimer la ligne et ensuite répondre OK au message Voulez-vous supprimer la ligne entière ?

Merci pour cette réponse. En effet je pourrais de cette façon filtrer sur ce qui m'intérresse et exporter les valeurs.

Seulement je souhaiterais que le retraitement de cette feuille soit "automatisée" et soit déclenchée par l'action sur un bouton.

En fait je ne sais pas écrire le code permettant de :
enregistrer la plage dans un tableau (n lignes et 20 colonnes)
redimensionner le tableau en ne conservant que les lignes respectant le critère
Transférer les valeurs du tableau remanié vers la feuille de calcul en écrasant les anciennes valeurs.

Je vous remercie par avance.

Bonjour,

Citation:

---

En fait je ne sais pas écrire le code permettant de :
enregistrer la plage dans un tableau (n lignes et 20 colonnes)

---

Qu'entends-tu par là ?
Si c'est pour sélectionner la liste des données, tu as entre-autres la propriété CurrentRegion qui est l'équivalent d'un Ctrl+A
Exemple avec une variable objet

Code:

---

1 2 3 4

Sub t() Dim rng As Range Set rng = ThisWorkbook.Worksheets("db").Range("A1").CurrentRegion End Sub

---

Une fois que l'on a attribué la plage de données à la variable objet rng, on peut utiliser celle-ci pour par exemple mettre en jaune les cellules de la plage

Code:

---

rng.Interior.Color = vbYellow

---

ou copier la plage vers la cellule C5 de la feuille nommée Export

Code:

---

rng.Copy ThisWorkbook.Worksheets("Export").Range("C5")

---

Citation:

---

Seulement je souhaiterais que le retraitement de cette feuille soit "automatisée" et soit déclenchée par l'action sur un bouton.

---

Le meilleur ami du programmeur est l'enregistreur de macros.
Commence à l'enclencher et ensuite tu réalises toutes les opérations décrites plus haut, une fois terminé, tu arrêtes l'enregistreur et tu vas lire ce qu'il a écrit.
Ensuite si tu ne comprends pas tout ou que tu veux enlever les Select, Selection, Activate qu'il ne va pas manquer de produire, tu reviens poser tes questions.

Merci,

Mais seulement le fichier à retraité est renouvellé chaque jour, je ne peux pas à chaque mise à jour rajouter la formule que tu précises. Ta solution est très simple mais demande quelques actions manuelles.

Je souhaite enregistrer la plage dans un tableau (stocké les valeurs dans un tableau) et réindéxé seulement les lignes qui m'intérressent pour les transférer vers la feuille de calcul. Le cas est très bien traité pour une plage à 2 colonnes, je souhaiterais pouvoir le faire sur 20 colonnes.



Bonne journée.

bonjour

si tu tiens absolument a utiliser une tableau alors on en utilisera 2
exemple

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

sub machin()   dim tablo1,tablo2,plage set plage=range("A1:f10") tablo1=plage redim tablo2(ubound(tablo1),10) for i= 0 to ubound(tablo1) 'teston la présence de "toto" dans la 1 ere colonne if tablo1(i,0)<>"toto" then for e=0 to 10 tablo2(i,e)=tablo1(i,e) next next 'maintenant on replace le tout sans les "toto" plage=tablo2 end sub

---

Bonjour,

Citation:

---

Mais seulement le fichier à retraité est renouvellé chaque jour, je ne peux pas à chaque mise à jour rajouter la formule que tu précises. Ta solution est très simple mais demande quelques actions manuelles.

---

Absolument pas, il suffit d'écrire la formule à l'aide d'un code VBA et le temps de transfert avec le filtre avancé est extrêmement rapide. J'avais fait un test en son temps moins d'une seconde pour transférer 3 732 lignes pour une liste de 272 769 lignes et 3 colonnes et ce avec 5 critères

Citation:

---

Je souhaite enregistrer la plage dans un tableau (stocké les valeurs dans un tableau) et réindéxé seulement les lignes qui m'intérressent pour les transférer vers la feuille de calcul.

---

Ce site et d'autres d'ailleurs regorgent d'exemples avec des tableaux et placer une liste de donénes dans un tableaux est d'une simplicité déconcertante
En reprenant l'exemple que je t'ai donné

Code:

---

1 2 3 4 5 6 7 8 9 10

Sub t() Dim rng As Range, Table() Set rng = ThisWorkbook.Worksheets("db").Range("A1").CurrentRegion Table = rng ' Charge la liste dans une variable tableau MsgBox Table(1, 1) ' Affiche la valeur de la celulle de la 1ère ligne, 1ère colonne With ThisWorkbook.Worksheets("Export") ' Ecrit à partir de la cellule C5 le contenu de la variable Table .Range(.Cells(5, 3), .Cells(5 + UBound(Table) - 1, UBound(Table, 2) - 1)) = Table End With End Sub

---

Merci pour votre aide, grâce à vous j'y suis presque.
Encore besoin de quelques petits conseils et sa va le faire.

1/Je ne comprends pas la ligne en rouge

2/La macro fonctionne sauf que l'onglet export contient la plage filtrée comme je le souhaite mais elle contient es lignes vides lorsque le critère n'était pas respecté. Comment réindexer correctement les valeurs qui respectent le critère dans l'export afin d'éviter les lignes vides (toujours en traitement dans le tableau car je souhaite éviter les boucles For qui vont directement cherche dans la feuille).

Merci d'avance.

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 traitement_cste_3() 'J'active la feuille que je veux retraiter Workbooks("CSTE.xlsx").Sheets("CSTE").Activate Dim i As Integer Dim e As Integer Dim derniere_ligne As Integer Dim tableau1, tableau2, plage derniere_ligne = Range("B" & Rows.Count).End(xlUp).Row Set plage = Sheets("CSTE").Range("A1:T" & derniere_ligne) tableau1 = plage ReDim tableau2(UBound(tableau1), 20) For i = 1 To UBound(tableau1) 'testons le respect du critère 4 chiffres 1 lettre If tableau1(i, 5) Like "####[A-Z]" Then 'Je remplit les colonnes A à T avec les valeurs qui respectent mon critère For e = 1 To 20 tableau2(i, e) = tableau1(i, e) Next End If Next 'Je transfert les valeurs vers l'onglet export With Workbooks("CSTE.xlsx").Worksheets("Export") .Range(.Cells(1, 1), .Cells(1 + UBound(tableau2) - 1, UBound(tableau2, 2) - 1)) = tableau2 End With End Sub

---

c'est pas une bonne ecriture
utilise le resize!

Code:

---

.Range(.Cells(1, 1).resize( UBound(tableau2),(nombre de colonne que tu a traité) ) = tableau2

---

ainsi ta grille excel commence au la cell( 1,1) et se termine la ou elle doit de terminer en fonction de la taille du tableau2 en terme de ligne et en terme de colonne renseignées juste apres

Merci, j'ai essayé d'appliquer ta modification mais cela me mets un message d'erreur " erreur définie par l'application ou par l'objet". Bon sa à l'air de fonctionner sans ...

Entre temps j'ai ajouté "j" pour avoir un index pour les valeurs que je souhaite garder dans le tableau cela m'évite les lignes vides. Il y a certainnement plus propre... mais sa fonctionne

Il me reste un problème que je n'arrive pas à résoudre : l'exportation ne commence pas en A1 mais en B1 alors que je lui demande pourtant de commencer en cells(1,1) ? D'ou cela peut il venir ?
Enfin mes deux derniers colonnes sont des dates et le passage par tableau me les transforme au format standard. Comment conserver le bon format ?

Merci pour votre aide j'y suis presque !!

PS : code utilisé :

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

Sub traitement_cste_3()   'J'active la feuille que je veux retraiter Workbooks("CSTE.xlsx").Sheets("CSTE").Activate   Dim i As Integer Dim e As Integer Dim j As Integer Dim derniere_ligne As Integer Dim tableau1, tableau2, plage j = 0   derniere_ligne = Range("B" & Rows.Count).End(xlUp).Row Set plage = Sheets("CSTE").Range("A1:T" & derniere_ligne) tableau1 = plage ReDim tableau2(UBound(tableau1), 20)   For i = 1 To UBound(tableau1) 'testons le respect du critère 4 chiffres 1 lettre If tableau1(i, 5) Like "####[A-Z]" Then 'Je remplit les colonnes A à T avec les valeurs qui respectent mon critère j = j + 1 For e = 1 To 20 tableau2(j, e) = tableau1(i, e) Next   End If Next   'Je transfert les valeurs vers l'onglet export With Workbooks("CSTE.xlsx").Worksheets("Export") .Range(.Cells(1, 1), .Cells(UBound(tableau2), UBound(tableau2, 2))) = tableau2   End With   End Sub

---

Bonjour Patrick,

Citation:

---

c'est pas une bonne ecriture

---

Je te remercie pour ton appréciation de l'écriture de la ligne de code. Que l'écriture soit longue, je te l'accorde mais écrire que ce n'est pas bon n'est pas exact ;)
Le but de l'utilisation de la fonction Ubound est que justement on est pas certain du nombre de colonnes même s’il est vrai qu'en début de discussion on a parlé de 20 colonnes fixes.
Le problème avec les constantes, c'est que dans six mois, l'utilisateur pourrait avoir plus de 20 colonnes et il aura alors un résultat erroné et le temps qu'il se rappelle que c'est à cause de cette constante, quelques heures peuvent s'écouler.

Patrick, Philippe,

Il me reste un problème que je n'arrive pas à résoudre : l'exportation ne commence pas en A1 mais en B1 alors que je lui demande pourtant de commencer en cells(1,1) ? D'ou cela peut il venir ?
Enfin mes deux derniers colonnes sont des dates et le passage par tableau me les transforme au format standard. Comment conserver le bon format ?

Merci pour votre aide j'y suis presque !!

Bonjour,
Dans la syntaxe plage.Cells(1,1), si plage est la plage de cellules B2:E7, nous avons la cellule de la ligne 1, 1ère colonne de la plage soit B2.
Petit exemple ci-dessous

Code:

---

1 2 3 4 5

Sub T() Dim rng As Range Set rng = ThisWorkbook.Worksheets("Feuil1").Range("B2:E7") MsgBox "Adresse de la première cellule " & rng.Cells(1, 1).Address End Sub

---

bonsoir Philippe

excuses je me suis mal exprimé
de toute façon le résultat est le même
cependant pour le nombre de colonnes lors du resize il a cas se servir de sa

Code:

---

plage.columns.count

---

tout simplement dans six mois si ca change se sera toujours bon
et cells(1,1)="A1"
sil il veux transposer son tableau en A2 se sera cells(2,1) .resize...... ou range("A2").resize......
excuse moi encore pour ma réponse mal formulée

Bonjour à tous.

Citation:

---

Envoyé par damsaga

Il me reste un problème que je n'arrive pas à résoudre : l'exportation ne commence pas en A1 mais en B1 alors que je lui demande pourtant de commencer en cells(1,1) ? D'ou cela peut il venir ?

---

Il faut mettre Option Base = 1
ou dimensionner Tableau2 à partir de 1 :

Code:

---

ReDim tableau2(1 To UBound(tableau1, 1), 1 To UBound(tableau1, 2))

---

Citation:

---

Enfin mes deux derniers colonnes sont des dates et le passage par tableau me les transforme au format standard. Comment conserver le bon format ?

---

Je ne vois pas ton problème de format.

[EDIT] Si je le vois. Il faut changer le format des cellules pour le format STANDARD avant d'y transférer la table.

Code:

---

1 2	.Resize(UBound(tableau2, 1), UBound(tableau2, 2)).NumberFormat = "General" .Resize(UBound(tableau2, 1), UBound(tableau2, 2)) = tableau2

---

[/EDIT]

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

Option Base 0 Sub traitement_cste_3()   'J'active la feuille que je veux retraiter 'Workbooks("CSTE.xlsx").Sheets("CSTE").Activate   Dim i As Integer Dim e As Integer Dim j As Integer Dim derniere_ligne As Integer Dim tableau1, tableau2, plage j = 0   derniere_ligne = Range("B" & Rows.Count).End(xlUp).Row Set plage = Sheets("CSTE").Range("A1:T" & derniere_ligne) tableau1 = plage ReDim tableau2(1 To UBound(tableau1, 1), 1 To UBound(tableau1, 2)) 'OK si Option Base = 0 ou Option Base = 1 'ReDim tableau2(UBound(tableau1, 1), UBound(tableau1, 2)) 'OK seulement si Option Base = 1   For i = 1 To UBound(tableau1, 1) 'testons le respect du critère 4 chiffres 1 lettre If tableau1(i, 5) Like "####[A-Z]" Then 'Je remplit les colonnes A à T avec les valeurs qui respectent mon critère j = j + 1 For e = 1 To UBound(tableau1, 2) 'For e = 1 To 20 tableau2(j, e) = tableau1(i, e) Next   End If Next   'Je transfert les valeurs vers l'onglet export en A1 With Workbooks("CSTE.xlsx").Worksheets("Export").Range("A1") .Resize(UBound(tableau2, 1), UBound(tableau2, 2)).NumberFormat = "General" .Resize(UBound(tableau2, 1), UBound(tableau2, 2)) = tableau2 End With   End Sub

---

Merci à tous pour votre aide.
Sa fonctionne très bien Sauf sur le format date.
Dans ma feuille origine j'ai deux colonnes qui contiennent des dates et lors du retraitement le format de toutes les colonnes devient standard. Comment spécifier dans le programme que je souhaite conserver le format date pour les deux dernières colonnes.

Merci beaucoup !

Le tableau transfère les valeurs et le type des valeurs, mais pas la propriété NumberFormat des cellules.
Il faut trouver un autre moyen de le faire :

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

Option Base 0   Sub traitement_cste_4()   'J'active la feuille que je veux retraiter 'Workbooks("CSTE.xlsx").Sheets("CSTE").Activate   Dim i As Integer Dim e As Integer Dim j As Integer Dim derniere_ligne As Integer Dim tableau1, tableau2, plage j = 0   derniere_ligne = Sheets("CSTE").Range("B" & Rows.Count).End(xlUp).Row Set plage = Sheets("CSTE").Range("A1:T" & derniere_ligne) tableau1 = plage ReDim tableau2(1 To UBound(tableau1, 1), 1 To UBound(tableau1, 2)) 'OK si Option Base = 0 ou Option Base = 1 'ReDim tableau2(UBound(tableau1, 1), UBound(tableau1, 2)) 'OK seulement si Option Base = 1   For i = 1 To UBound(tableau1, 1) 'testons le respect du critère 4 chiffres 1 lettre If tableau1(i, 5) Like "####[A-Z]" Then 'Je remplit les colonnes A à T avec les valeurs qui respectent mon critère j = j + 1 For e = 1 To UBound(tableau1, 2) 'For e = 1 To 20 tableau2(j, e) = tableau1(i, e) Next   End If Next   'Je transfert les valeurs vers l'onglet export en A1 With Workbooks("CSTE.xlsx").Worksheets("Export").Range("A1")   .Resize(UBound(tableau2, 1), UBound(tableau2, 2)).NumberFormat = "General" .Resize(UBound(tableau2, 1), UBound(tableau2, 2)).Value = tableau2     Set r = .Resize(UBound(tableau2, 1), UBound(tableau2, 2)) 'Je transfere la propriété NumberFormat de la première ligne de la plage For i = 1 To r.Columns.Count   If r.Columns(i).Cells(1, 1).NumberFormat <> plage.Columns(i).Cells(1, 1).NumberFormat Then   r.Columns(i).Cells.NumberFormat = plage.Columns(i).Cells(1, 1).NumberFormat   End If   Next End With   End Sub

---