Boucle while en erreur

Boucle while en erreur - VB/VBA/VBS - Programmation

Marsh Posté le 15-05-2013 à 21:07:20    

Je travaille actuellement dans une boite ou la majeure partie de mon travail est la conception de requête SQL couplée avec Excel.
 
Aujourd'hui, je bloque sur le code VBA, les deux fichiers dont je me sers sont des extractions d'une base Oracle en Sql, je recherche sur les deux feuilles suivantes issues du même classeur :  Feuille 1
 
Résidence     ESI             Type            nom résidence
0100      0100.03.01.0402      T5              RESIDENCE DU PARC
0100      0100.03.03.0001      T2              RESIDENCE DU PARC
0100      0100.03.03.0002      T2              RESIDENCE DU PARC
0100      0100.03.03.0003      T5              RESIDENCE DU PARC
0100      0100.03.03.0101      T5              RESIDENCE DU PARC
0100      0100.03.03.0102      T6              RESIDENCE DU PARC
 
Feuille 2
 
TYPE
T2
T4
T1
T6
T3
T4
T1
T1BIS
 
 
Le résultat devrait donner a peut près ça :
 
 
Résidence nom résidence T1 T2 T3 T4
0300                           1    
0400 BAT B                      7    
0100 RESIDENCE DU PARC  10  
0400 BAT B                           1  
0100 RESIDENCE DU PARC   2  
0400 BAT B                           1  
200                             4
0100 RESIDENCE DU PARC    2
0300                             1
 
Voici mon code (qui beugge a toute les lignes), la boucle doit incrémenter de 1 mes cellules TYPE de la feuille 3.
 

Code :
  1. Sub test()
  2. Dim i As Variant             
  3. Dim j As Variant               
  4. Dim tabl As Worksheets       
  5. Dim tablsor As Worksheets     
  6.              
  7. Set tabl = Worksheets("Feuil1" )               
  8. Set tablsor = Worksheets("Feuil3" )           
  9. i = 1
  10. j = 0
  11. i = i + 1                                   
  12.     Si tablsor = (225,25)
  13.    
  14.    
  15.             For i = 0 To 199                                         
  16.             tablsor(i, 1) = 0
  17.            
  18.             Next i
  19.            
  20.         End If
  21.        
  22.    
  23.    
  24.    While tabl(i, 1) <> "" And i < 2000       
  25.        
  26.         tablsor = Cells(i, j).Value               
  27.      
  28.         If tabl(i, 1) = Worksheets("Feuil2" ).Cells(1, 20) Then                 
  29.    
  30.             tabl(i, 1) = tablsor(i, j) + 1 
  31.      
  32.         End If
  33.  
  34.     Wend
  35.    
  36.  
  37.     Range(C).Value = tablsor
  38.     Sheets("Feuil3" ).Select
  39. End Sub


 
Merci par avance.


Message édité par labeo64 le 15-05-2013 à 21:27:54
Reply

Marsh Posté le 15-05-2013 à 21:07:20   

Reply

Marsh Posté le 15-05-2013 à 21:30:37    

J'ai oublié de préciser que je travaille sur Windows Seven et Excel 2010.

Reply

Marsh Posté le 16-05-2013 à 07:47:48    

Je n'ai pas trop le temps de répondre précisément, mais effectivement, ton code doit sacrément boguer...
D'abord i et j devraient être déclaré en Integer (Variant c'est bon aussi, mais Integer c'est mieux).
Ensuite tu déclares tes 2 tableaux comme étant des Worksheet mais ensuite tu les utilises comme si c'était des variables tableaux. Ca ne peut pas fonctionner !
Ensuite, tu as ta ligne

Si tablsor = (225,25)

Pourquoi Si et pas If et il faut rajouter un Then? De plus même un

If tablsor = (225,25) Then

ne peut fonctionner, ça ne veut rien dire...
 
Bon courage :)


---------------
OtObOxBlOg - - - Etre seul à avoir tort  c'est plus difficile, mais c'est bien plus beau que d'avoir raison avec une bande de cons
Reply

Marsh Posté le 16-05-2013 à 10:31:19    

otobox a écrit :

Je n'ai pas trop le temps de répondre précisément, mais effectivement, ton code doit sacrément boguer...
D'abord i et j devraient être déclaré en Integer (Variant c'est bon aussi, mais Integer c'est mieux).


 
Pour un i qui va jusqu'à 2000, un Integer n'est pas trop "court" ? Ne faut-il pas un Long ?

Reply

Marsh Posté le 16-05-2013 à 11:05:38    

 
           Jusqu'à 2000 un Integer convient tout à fait vu qu'il va jusqu'à 32767 !
 
           Mais vu le nombre de lignes contenues dans une feuille, mieux vaut effectivement déclarer la variable en Long
           ou ne rien déclarer du tout car pas de souci avec des entiers …
 

Reply

Marsh Posté le 16-05-2013 à 11:06:40    

Marc L a écrit :

 
           Jusqu'à 2000 un Integer convient tout à fait vu qu'il va jusqu'à 32767 !
 
           Mais vu le nombre de lignes contenues dans une feuille, mieux vaut effectivement déclarer la variable en Long
           ou ne rien déclarer du tout car pas de souci avec des entiers …
 


 
Ah oui, il reste un peu de marge [:tinostar]
Merci de l'info :jap:

Reply

Marsh Posté le 16-05-2013 à 16:16:32    

Merci pour toutes vos réponse j'ai rectifié.
 
Cela ne fonctionne toujours pas

Reply

Marsh Posté le 16-05-2013 à 17:07:06    

Fais voir ce que tu as désormais.

Reply

Marsh Posté le 16-05-2013 à 18:21:21    

Marc L a écrit :

 
           Jusqu'à 2000 un Integer convient tout à fait vu qu'il va jusqu'à 32767 !
 
           Mais vu le nombre de lignes contenues dans une feuille, mieux vaut effectivement déclarer la variable en Long
           ou ne rien déclarer du tout car pas de souci avec des entiers …
 


Oui, un long peut aussi faire l'affaire, mais j'ai l'habitude d'utiliser des integer pour parcourir les tables Excel, ceux ci atteignant rarement 32767 lignes...
Toujours déclarer ses variables comme elles doivent l'être :

  • ça utilise moins de mémoire
  • ça évite des bogues à la con ;)

C'est pour ça qu'en tête de module je place systématiquement :

Option Explicit


Tzol a écrit :

Fais voir ce que tu as désormais.


Oui, ça donne quoi maintenant ?


Message édité par otobox le 16-05-2013 à 18:23:22

---------------
OtObOxBlOg - - - Etre seul à avoir tort  c'est plus difficile, mais c'est bien plus beau que d'avoir raison avec une bande de cons
Reply

Marsh Posté le 16-05-2013 à 19:41:59    

Code :
  1. Public Sub test()
  2. Dim i As Integer
  3. Dim j As Integer
  4. Dim tabl As Integer
  5. Dim tblsor(225, 25) As Integer
  6. Sheets("Feuil1" ).Select
  7.     tabl = Range("a1:d1" )
  8.    
  9.    
  10.     ' initialisation table de sortie (resultat) = feuille 3
  11.    
  12.     For i = 0 To 199
  13.             tblsor(i, j) = 0
  14.    
  15.     Next i
  16.    
  17.     ' Initialisation des variable
  18.     i = 1
  19.     j = 0
  20.    
  21.     ' je déclare mon compteur
  22.    
  23.     i = i + 1
  24.    
  25.    ' boucle sur la premiere colonne
  26.  
  27.  
  28.     While tabl(i, 1) <> "" And i < 2000
  29.    
  30.         Sheets("Feuil2" ).Select
  31.      
  32.         i = i + 1
  33.          
  34.           tablsor = Cells(i, j).Value
  35.    
  36.         If tabl(i, 1) = Worksheets("Feuil2" ).Cells(1, 20) Then
  37.  
  38.             tabl(i, 1) = tablsor(i, j) + 1
  39.    
  40.          End If
  41.            
  42.                  
  43.     Wend
  44.    
  45.    
  46.     Range(A).Value = tblsor
  47.     Sheets("Feuil3" ).Select
  48.    
  49. End Sub


 
Il bloque sur la ligne 34 : While tabl(i, 1) <> "" And i < 2000
 
avec "erreur de compilation tableau attendu"
 

Reply

Marsh Posté le 16-05-2013 à 19:41:59   

Reply

Marsh Posté le 16-05-2013 à 20:41:39    

 

labeo64 a écrit :

Il bloque sur la ligne 34 : While tabl(i, 1) <> "" And i < 2000
 
avec "erreur de compilation tableau attendu"


           Normal car en comparant avec sa déclaration en ligne n°12, effectivement cela ne peut pas marcher !
 
           Un effort de réflexion, remise à plat, est nécessaire …
 

Reply

Marsh Posté le 16-05-2013 à 20:45:07    

J'ai un peu avancé de mon coté avec ce code
 

Code :
  1. Public Sub test()
  2. ' declare mes variable
  3. Dim i As Integer
  4. Dim j As Integer
  5. Dim compteur
  6. Dim tabl
  7. Dim tblsor(225, 25)
  8.    
  9. '
  10.     Sheets("Feuil1" ).Select
  11.     tabl = Range("a3:z15000" )
  12. '   initialisation table tblsor
  13. '
  14.     For i = 0 To 199
  15.         For j = 0 To 1
  16.               tblsor(i, j) = 0
  17.         Next j
  18.     Next i
  19. '
  20.     i = 1
  21.     j = 0
  22.  
  23.     While tabl(i, 1) <> "" And i < 2000
  24.          critere = tabl(i, 1)
  25.          While tabl(i, 1) = critere
  26.             compteur = compteur + 1
  27.             i = i + 1
  28.         Wend
  29.        
  30.          tblsor(j, 0) = critere
  31.          tblsor(j, 1) = compteur
  32.          j = j + 1
  33.          compteur = 0
  34.     Wend
  35.     Sheets("feuil3" ).Select
  36.     Range("a2:b2000" ).Value = tblsor
  37. End Sub


 
ça ne beuggue plus du tout  mais il ne me calcule que la première colonne de la feuille 1 soit les résidences
 
comme ça :
100 507
140 31
150 108
180 14
190 16
200 312
300 166
400 133
500 123
 
incremente bien dans la feuille 3 mais il ne calcule pas sur la bonne colonne qui est la C (la troisième soit le type)
 
pour avoir un résultat optimum, il faudrait même qu'il compare la colonne C de la feuille 1 avec la colonne A de la feuille 2 et incrémente de 1 la feuille 3 si cellule de la colonne C de la feuille 1 et cellule de la colonne A de la feuille 2 sont égales.
 
Je continue donc
 
Merci pour vos conseils qui me permette de progresser

Reply

Marsh Posté le 16-05-2013 à 21:00:59    

Marc L a écrit :


           Normal car en comparant avec sa déclaration en ligne n°12, effectivement cela ne peut pas marcher !
 
           Un effort de réflexion, remise à plat, est nécessaire …
 


 
Merci Marc.
 
Tu as raison des fois j'y suis tellement concentré des heures que je ne vois même plus mon code.
 
Pour avoir mis un IF en français, fallait le faire quand même, peut être ma blondeur enfantine qui a laissé quelques séquelles  :D  

Reply

Marsh Posté le 17-05-2013 à 07:55:15    

Ben disons que l'éditeur a certainement dû hurler avec les erreurs de syntaxe. C'est étonnant que tu n'aies pas vu ces erreurs...

 

Ensuite, il y a le débogueur (touches F5 et surtout F8 pour faire du pas à pas) couplé à la fenêtre des variables qui sont bien utiles pour détecter les erreurs de logique.

 

Tu es sur la bonne voie me semble t-il, ton dernier code est beaucoup mieux que l'avant dernier.
Je n'ai pas saisi toutes les subtilités de ce que tu veux faire, donc je ne peux pas trop te dire pourquoi tu n'as pas le résultat attendu.

 

Bon courage :)


Message édité par otobox le 17-05-2013 à 07:55:32

---------------
OtObOxBlOg - - - Etre seul à avoir tort  c'est plus difficile, mais c'est bien plus beau que d'avoir raison avec une bande de cons
Reply

Marsh Posté le 17-05-2013 à 10:14:06    

 
           Cela me rassure car moi aussi j'ai du mal à visualiser, et ce, depuis le premier message,
           car entre le résultat attendu et les feuilles 1 & 2, quid de la logique …
 
           Sans explication précise, je ne cherche même pas …
 
           Exemples :
           - incrémente de 1 la feuille 3 :   impossible ‼   Une cellule oui, mais laquelle ?
 
           - si cellule de la colonne C de la feuille 1 et cellule de la colonne A de la feuille 2 sont égales :
 
             là quoique plus clair, cela manque de précision sur le n° de ligne ...
             Feuil1.C9 doit correspondre uniquement à Feuil2.A9 ?  Ou à n'importe quelle cellule de la colonne A de Feuil2 ?
 
           En dehors de l'effort de réflexion, il en faut un aussi quant à la présentation de la problématique devant être claire et exhaustive.
 
           Car sinon, comme je l'ai récemment écrit, ayant prêté ma boule de cristal, je ne fais plus d'effort divinatoire ‼
 

Reply

Marsh Posté le 17-05-2013 à 11:07:34    

Bonjour,
 
J'essaie d'être plus claire avec moi-même. J'ai conscience que le problème bien exposé c'est une partie du programme déjà faite.
 
Tableau 1 de la feuille 1
 
Quartier ESI                    Type         Nom Résidence
0100      0100.15.18.1004     T4  
0100      0100.12.02.0403    T2/3         RESIDENCE ALIZE 1
0100      0100.12.04.0203    T2/3         RESIDENCE ALIZE 1
0140      0140.01.01.0202     T3  
0140      0140.01.01.0203     T4  
0140      0140.01.01.0204     T4  
0140      0140.01.01.0301     T2  
0140      0140.01.01.0302     T3  
0150      0150.01.01.0201     T6  
0150      0150.01.01.0202     ST  
 
 
 
Tableau 2 feuille2
 
type
I3
D1
D5
I6
T7
T2
D6
CHAM
I4
T6
I5
GG
D3
T2/3
I2
T4
T1
T1BIS
D4
1B
T3
T5
ST
 
Résultat attendu  en feuille 3
 
Num Res     Nom Res   F1            F2         F2/3        F3 F4 etc
0100           Res X   nombre   nombre  nombre etc  etc  
       
       
       
En fait, le programme doit balayer le premier tableau (entier) de la feuille1 (ce qu’il fait)
S’il trouve une valeur dans la 3ème colonne qui correspond au une valeur du  tableau de la feuille 2 colonne 1 qui correspond par exemple a F1 etc
Alors il doit incrémenter de 1 la valeur F1  par rapport au nom de la résidence du tableau en feuille 1
 
Le but final étant de savoir combien nous avons de F1, F2, etc.. dans une résidence.
 
Je persevère pour obtenir dans un premier temps le nombre de F1, F2 etc en vertical.


Message édité par labeo64 le 17-05-2013 à 11:11:06
Reply

Marsh Posté le 17-05-2013 à 12:17:13    

 
           Et là dans l'exemple de la feuille 2 il n'y a aucun F1 ...
 
           S'il s'agit juste de totaliser un titre d'une colonne de la feuille 3 présent dans la première colonne de la feuille 2,
           pas besoin de macro pour cela car une simple formule directement dans une cellule suffit !
 
           Pour le choix de la formule, tout dépend de ta version d'Excel …   ► 2010 :  NB.SI.ENS
 
           Par contre cela reste flou car je ne vois pas de lien entre la feuille 2 et le n° de résidence de la feuille 1 car,
           tel que présenté, le total dans la feuille 3 à partir de la colonne 3 (F1, F2) sera le même pour chacune des lignes ‼
 
           Tu peux utiliser l'icône Fixed pour bien présenter tes colonnes.

Message cité 1 fois
Message édité par Marc L le 17-05-2013 à 13:14:08
Reply

Marsh Posté le 17-05-2013 à 12:36:00    

Merci pour ta réponse.
 
point 1 : autant pour moi c'est T1, T2 etc qui représente les types de logement...
 
point 2 oui c'est cela mais comme il faut le faire par rapport au n° et a la résidence alors on doit filtrer sur le tableau 1
 
point 3 : la feuille 1 représente une extraction sql de notre base oracle des logements, le numéro représente un lot (ça peut s’apparenter a un quartier), la feuille 2 est une autre extraction mais avec restriction sur ma requete, j'ai ramené que les logements qui correspondent a des habitations en excluant garage, cave etc... (qui sont présent dans le tableau 1 dans la colonne type.
 
A la fin, je veut le nombre de type de logement de type 1.2.3.2/3.4.5 etc par résidence.
 
Le numéro et le nom de la résidence doivent aussi s'afficher dans le tableau.
 
J'espère que cette fois je me suis faite comprendre.

Reply

Marsh Posté le 17-05-2013 à 12:45:58    

 

labeo64 a écrit :

je veux le nombre de type de logement de type 1.2.3.2/3.4.5 etc par résidence.

Marc L a écrit :

je ne vois pas de lien entre la feuille 2 et le n° de résidence de la feuille 1


            S'il n'y a pas de n° de résidence dans la feuille 2, comment différencier les types pour le total en feuille 3 alors ?‼
 


Message édité par Marc L le 17-05-2013 à 12:47:30
Reply

Marsh Posté le 17-05-2013 à 14:50:58    

Note logiciel est un peu spécial.
Chaque lot est representé par le numéro ESI  
Les 4 premiers caractères représentent  la résidence suivie d’un point (100. = ousse des bois) c'est la premiere colonne de ma feuille 1.
Les deux caractères suivant représentent le N° de bâtiment suivi d’un point. (100.03 = résidence du parc)
Les deux caractères suivant représentent le N° de rue suivi d’un point.  
Les quatre caractères suivant représentent le N° de logement qui se décomposent en deux parties, étage + N° dans l’étage.
 
Comme je le disais avant je peux le faire apparaitre puisque les tableaux sont issus de mes requetes SQL
 
Pour le point, je comptais faire la comparaison sur les types qui eux sont communs a la feuille 1 et 2.
 
Ce code VBA est pour mon boulot.
 
C'est un peu nouveau alors je me mélange un peu les pinceaux.
 
Mon supérieur ne veut pas une formule (trop facile) mais bien du code VBA.
 
Ce n'est pas une mauvaise chose car cela me fait progresser.


Message édité par labeo64 le 17-05-2013 à 14:55:22
Reply

Marsh Posté le 17-05-2013 à 15:04:22    

 
           Oui mais peu importe le langage de programmation, c'est avant tout un souci de pure logique …
 
           N'obtenant toujours pas de réponse quant au problème soulevé concernant la deuxième feuille,
           passant peut-être à côté de quelque chose (parfois on peut être enfermé dans ses propres arcanes internes …),
           j'attends de voir les déductions d'autres intervenants compétents comme par exemple otobox
 

Reply

Marsh Posté le 17-05-2013 à 15:09:23    

 
           Précision :   même une fois l'horizon dégagé, ma solution ne consistera pas à faire bêtement une usine à gaz en VBA,
                             tout du moins à vouloir réinventer la roue au risque qu'elle fusse carrée,
                             j'utiliserai quand même en VBA les fonctions de calcul internes aux feuilles d'Excel
                             car elles sont beaucoup plus puissantes qu'une quelconque procédure VBA ‼


Message édité par Marc L le 17-05-2013 à 15:10:31
Reply

Marsh Posté le 17-05-2013 à 17:21:52    

Bonsoir,  
je suis le fil depuis son début, je crois comprendre que la feuille 2 contient la liste des différents types rencontrés dans l'ensemble des résidences.  
Labeo64 voudrait, semble-t-il, voir cette liste recopiée sur la feuille 3, en ligne 1, à partir de la colonne 3 et classée (?) par ordre alphabétique croissant. Puis, elle voudrait, par résidence, la somme de chacun des types qu'on y trouve (0,n).
Reste aussi à lever l'ambigüité "quartier" "résidence"  
Cordialement


Message édité par seniorpapou le 17-05-2013 à 17:23:50
Reply

Marsh Posté le 17-05-2013 à 19:33:51    

Si le Senior arrive, on va pouvoir s'en sortir :)

 

J'ai toujours un peu de mal à comprendre, mais si c'est ce que dit seniorpapou est correct, labeo64 devrait pouvoir passer par un tableau dynamique croisé, sans avoir à coder du vba.

 

Edit : OK, je viens de voir que le vba est imposé :/


Message édité par otobox le 17-05-2013 à 19:36:31

---------------
OtObOxBlOg - - - Etre seul à avoir tort  c'est plus difficile, mais c'est bien plus beau que d'avoir raison avec une bande de cons
Reply

Marsh Posté le 17-05-2013 à 19:54:18    

Merci Séniorpapou pour l'expression vraisemblablement plus distincte de ma problématique.
 
Comme je l'expliquais avant, otobox, ceci est un test de mon collègue pour tester mes connaissances en programmation (que j'eu mais j'ai bien peur de n'avoir pratiquer depuis très très longtemps).
 
Je ne sais donc encore pas si cela va être bloquant pour mon futur emploi, d'ou l'importance et l'implication que j'y mets.
 
Je ne désespère pas de trouver la solution et votre aide m'est très précieuse.


Message édité par labeo64 le 17-05-2013 à 19:55:22
Reply

Marsh Posté le 17-05-2013 à 21:39:25    

Bonsoir otobox, je faisais juste un court passage, parce que labeo64 m'a remis en mémoire les demandes d'un utilisateur qui savait très bien ce qu'il voulait, mais qu'il fallait presser comme un citron pour obtenir l'expression exacte de sa pensée. Bonne chance à labeo64.
Cordialement

Reply

Marsh Posté le 18-05-2013 à 08:13:23    

Ce qui se conçoit bien s'énonce clairement - Et les mots pour le dire arrivent aisément (Boileau) Ça fonctionne aussi pour la programmation, comme quoi ce poète du XVIIe siècle était en avance sur son temps :p

 

Alors je reprends: l'idée est de récapituler le nombre de type de logements par résidence, chaque ligne du tableau étant une résidence. Avec en plus, l'idée de ne pas répertorier tous les types logements, mais seulement ceux qui sont identifiés dans la feuille 2. C'est ça ?

 

Si c'est ça, comment ça se passe quand il n'y a pas de nom de résidence ? Car dans le tableau que tu donnes :


Quartier  ESI                 Type         Nom Résidence
0100      0100.15.18.1004     T4  
0100      0100.12.02.0403     T2/3         RESIDENCE ALIZE 1
0100      0100.12.04.0203     T2/3         RESIDENCE ALIZE 1
0140      0140.01.01.0202     T3  
0140      0140.01.01.0203     T4  
0140      0140.01.01.0204     T4  
0140      0140.01.01.0301     T2  
0140      0140.01.01.0302     T3  
0150      0150.01.01.0201     T6  
0150      0150.01.01.0202     ST


Il n'y a pas de nom de résidence à chaque ligne... Alors, faut-il récapituler par n° de quartier ? Ou alors extraire le code de la résidence dans le n° ESI ? En gros, quelle est la clé unique (dans le sens BDD) définissant la résidence ?

 

Edit : après relecture de tes précédents messages, je vois que ce qui défini une résidence sont les 2 premières séries de chiffres du n°ESI, soit :


0100.15.
0100.12.
0100.12.
0140.01.
0140.01.
etc.


Il faudrait donc compter le nombre de type de logement en recherchant sur cette partie du numéro... OK ?

 

C'est important pour la suite, car pour des raisons pratiques de programmation, ainsi que pour la rapidité d'exécution de la procédure, il faut commencer -à mon avis- par trier le tableau d'entrée par numéros uniques de résidences, histoire d'éviter de parcourir plusieurs fois la liste.

 

Edit2 : je vois dans ce que tu as écrit avant que en fait, tu regroupes par n° de quartier...

labeo64 a écrit :


100 507
140 31
150 108
180 14
190 16
200 312
300 166
400 133
500 123




Message édité par otobox le 18-05-2013 à 08:37:02

---------------
OtObOxBlOg - - - Etre seul à avoir tort  c'est plus difficile, mais c'est bien plus beau que d'avoir raison avec une bande de cons
Reply

Marsh Posté le 18-05-2013 à 21:05:46    

Merci seniorpapou et merci a otobox
 
le numéro important est celui de la feuille 1 dans la première colonne et peut importe s'il est associé a un nom ou pas.
 
Je travaille a faire une comparaison de ma colonne c en feuille 1 et ma colonne A en feuille 2
 
Mais cela n'est peut être la marche a suivre  :non:  
 
Peut importe en tout les cas ce sera instructif.
 
 
 

Reply

Marsh Posté le 18-05-2013 à 22:10:03    

OK, c'est donc un regroupement sur la première colonne.
 
J'ai une solution "clé en main" qui fonctionne. Je te la donne ou je te laisse chercher ?


---------------
OtObOxBlOg - - - Etre seul à avoir tort  c'est plus difficile, mais c'est bien plus beau que d'avoir raison avec une bande de cons
Reply

Marsh Posté le 18-05-2013 à 22:45:32    

otobox a écrit :

OK, c'est donc un regroupement sur la première colonne.
 
J'ai une solution "clé en main" qui fonctionne. Je te la donne ou je te laisse chercher ?


 
Merci pour ta proposition,
 
Même si tu me la donnes, je chercherais quand même, je suis du style acharnée.
 
 
 
 
 
 

Reply

Marsh Posté le 19-05-2013 à 08:54:56    

Dans la solution que je te propose, je ne passe pas par des tableaux vba (ça bouffe de la mémoire), mais je travaille directement sur les cellules.
 
Je commence par préparer les données pour les traiter ensuite :
 
Déclaration des différentes feuilles Excel en tant qu'objet.
Création d'une nouvelle feuille où seront écrits les résulats. Si la feuille de résultat existe, elle est supprimée avant de la refaire.
Création d'un objet type "Dictionnary" pour faciliter l'incrémentation du nombre d'appartement dans la bonne colonne :
Dans ta solution, d'après ce que j'ai compris tu voulais faire ceci :


'Lire le type d'appartement dans la liste principale
TypeAppartementRecherche = "T3"
'Rechercher dans quelle colonne est le type T3 :
i=1
Do
  if cells(1,i) = TypeAppartementRecherche then 'Le type a été trouvé, sortir de la boucle, le n° colonne est égal à i
     exit do
  else
     i = i + 1
  end if
loop


Dans la solution que je te propose, j'utilise un Dictionnary pour associer un type d'appartement à un n° de colonne. Ainsi, pas besoin de faire une boucle à chaque fois pour trouver dans quelle colonne on doit incrémenter le nombre d'appartement.
Exemple :

'J'apprends au dictionnaire le n° de la colonne pour différente type d'appartement :
MonDictionnaireAppartement.add "T2", 3 'Le type T2 est dans dans la colonne n°3
MonDictionnaireAppartement.add "T3", 4 'Le type T3 est dans dans la colonne n°4
MonDictionnaireAppartement.add "ST", 5 'Le type ST est dans dans la colonne n°5
'Je retrouve ainsi rapidement le n° de la colonne pour chaque appartement :
NumeroColonne = MonDictionnaireAppartement("T3" ) 'Résultat renvoyé = 4


Plus simple, non ?
 
Je fais aussi un tri dans le tableau principal pour être sûr que les résidences soient regroupées. Ainsi, quand je parcours le tableau principal ça fait ceci :

- lecture du nom de la résidence dans le tableau principal
- lecture du nom de la résidence dans le tableau résultat
- si les 2 noms sont égaux, je lance directement la procédure pour incrémenter le nombre du type d'appartement lu dans le 1er tableau
- si les 2 noms ne sont pas égaux, je crée une nouvelle ligne dans le tableau résultat, j'entre le nouveau nom de la résidence puis je fais la procédure pour incrémenter le nombre du type d'appartement lu dans le 1er tableau.


Si les résidences n'étaient pas triées au préalable, je risquerais d'avoir des doublons dans les résultats en utilisant cette méthode.
 
 
J'ai organisé le programme entre une procédure principale "labeo64" qui appelle différentes fonctions et sous-routines. Ainsi, tu pourras voir plus facilement les différentes étapes, entre la préparation des données, leur traitement et leur mise en forme finale.
 
/!\TIP : Pour copier le code sans les n° de lignes, fais d'abord un double clic dans le cadre blanc pour les effacer

Code :
  1. Option Explicit
  2. 'Le nom de la feuille résultat est donné ici :
  3. Const FeuilRes As String = "Résultat"
  4. Sub labeo64()
  5. 'Déclaration des variables
  6.     Dim BdLst As Worksheet 'Feuille contenant les données d'entrée
  7.     Dim BdTyp As Worksheet 'Feuille contenant les types d'appartements à compter
  8.     Dim BdRes As Worksheet 'Feuille résultat
  9.     Dim DicTyp As Object 'Dictionnaire définissant les types d'appartement à compter
  10. 'Initialisation des variables :
  11.     Set BdLst = ActiveWorkbook.Sheets("Feuil1" )
  12.     Set BdTyp = ActiveWorkbook.Sheets("Feuil2" )
  13.     Set DicTyp = CreateObject("Scripting.Dictionary" )
  14.     'Création de la feuille résultat
  15.     Set BdRes = CreerNouvelleFeuilleResultat
  16.     'Création de l'entête de la feuille résultat
  17.     'et écrit le dictionnaire des types d'appartements:
  18.     Set DicTyp = CompleterEntete(BdRes, BdTyp)
  19.    
  20.     'Tri de la liste par n° de résidence (colonne 2)
  21.     TrierResidence BdLst
  22. 'Toutes les variables ayant été initialisées et préparées,
  23. 'regrouper et compter les données
  24.     RegrouperAppartements BdLst, BdRes, DicTyp
  25. 'Afficher la feuille des résultats et la mettre en forme
  26.     MiseEnForme BdRes
  27. 'Libération des variables objet :
  28.     Set BdLst = Nothing
  29.     Set BdTyp = Nothing
  30.     Set BdRes = Nothing
  31.     Set DicTyp = Nothing
  32. End Sub
  33. 'Cette procédure met en forme les résultats
  34. Private Sub MiseEnForme(WS As Worksheet)
  35.     WS.Select
  36.     Cells.Select
  37.     Cells.EntireColumn.AutoFit
  38.     Range("A1" ).Select
  39. End Sub
  40. 'Cette procédure regroupe et compte les types d'appartement par résidence
  41. Private Sub RegrouperAppartements(BdLst As Worksheet, BdRes As Worksheet, DicTyp As Object)
  42. 'Déclaration des variables
  43.     Dim ligBdLst As Long 'Numéro de ligne parcourue dans la feuille contenant les données d'entrée
  44.     Dim ligBdRes As Long 'Numéro de ligne parcourue dans la feuille contenant les résultats
  45.     Dim strNoResD As String 'Numéro de la résidence lu dans la liste des données
  46.     Dim strNoResR As String 'Numéro de la résidence lu dans la liste des résultats
  47.     Dim LigneLst As Long 'N° de ligne courante de la liste des données d'entrée
  48.     Dim LigneRes As Long 'N° de ligne courante de la liste des résultats
  49.     Dim strTypeApp As String 'Type d'appartement
  50.     Dim ColType As Integer 'N° de la colonne renvoyée par le type d'appartement
  51. 'Initialisation des variables
  52.     LigneLst = 2 'Les données commencent à la ligne 2
  53.     LigneRes = 1 'Numéro de la ligne courante des résultats.
  54. 'On parcourt la liste des données, maintenant triées par résidence, pour compléter les résultats
  55. 'La feuille active est la feuille des données (activée lors du tri)
  56.     Do
  57.         'On extrait le n° de la résidence à partir du n°ESI (colonne 2)
  58.         'le n° de la résidence est défini par les 4 premiers caractères
  59.         strNoResD = Left(Cells(LigneLst, 2), 4)
  60.             'NOTE/
  61.             'Si on veut regrouper par bâtiment, il suffit de changer le nombre de caractères 4 par 7:
  62.             'strNoResD = Left(Cells(LigneLst, 2), 7)
  63.             '/NOTE
  64.         If strNoResD = "" Then Exit Do 'On sort de la boucle quand le n° de la résidence est vide = fin de liste des données
  65.         'On lit le type d'appartement (colonne 3 de la feuille des données)
  66.         strTypeApp = Cells(LigneLst, 3)
  67.         'et on l'associe au n° de la colonne donnée dans la feuille de résultats
  68.         ColType = DicTyp(strTypeApp) 'Note : si le type d'appart n'est pas défini, le n° de la colonne sera = 0
  69.         'Si le type d'appartement est reconnu (donc différent de zéro) compléter la liste résultats
  70.         If ColType <> 0 Then
  71.             'On lit le n° de la résidence dans la colonne 1 de la feuille résultat
  72.             strNoResR = BdRes.Cells(LigneRes, 1)
  73.             'Si le n° de la résidence de la liste résultat est différent du n° résidence de la liste des données, créer une nouvelle ligne
  74.             If strNoResR <> strNoResD Then
  75.                 LigneRes = LigneRes + 1
  76.                 'Noter le n° de la résidence dans la 1ère colonne
  77.                 BdRes.Cells(LigneRes, 1) = "'" & strNoResD 'Le "'" est pour conserver un caractère String dans Excel
  78.                 'Noter le nom de la résidence (colonne 4 de la liste des données) s'il existe (colonne 2 de la liste résultat)
  79.                 BdRes.Cells(LigneRes, 2) = BdLst.Cells(LigneLst, 4)
  80.             End If
  81.             'Rajouter 1 à la valeur existante dans la colonne correspondant au type d'appartement
  82.             BdRes.Cells(LigneRes, ColType) = BdRes.Cells(LigneRes, ColType) + 1
  83.         End If
  84.         'On passe à la ligne suivante dans le tableau des données
  85.         LigneLst = LigneLst + 1
  86.     Loop
  87. End Sub
  88. 'Cette procédure trie les données suivant la colonne B
  89. Private Sub TrierResidence(WS As Worksheet)
  90.     WS.Select 'Sélection de la feuille de données
  91.     Columns("A:D" ).Select 'Sélection des colonnes de A à D
  92.     Selection.Sort Key1:=Range("B2" ), Order1:=xlAscending, Header:=xlYes, _
  93.         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
  94.         DataOption1:=xlSortNormal
  95.     Range("A2" ).Select
  96. End Sub
  97. 'Cette fonction complète dynamiquement l'entête de la feuille résultat
  98. 'et renvoie un dictionnaire récapitulant les types d'appartement sélectionnés dans la feuil2
  99. Private Function CompleterEntete(BdRes As Worksheet, BdTyp As Worksheet) As Object
  100. 'Déclaration des variables
  101.     Dim DicTmp As Object
  102.     Dim i As Long, j As Long, strType As String
  103. 'Initialisation des variables
  104.     Set DicTmp = CreateObject("Scripting.Dictionary" )
  105. 'Pour chaque colonne de la feuille résultat, 1ère ligne
  106.     With BdRes
  107.         .Cells(1, 1).Value = "Num Res"
  108.         .Cells(1, 2).Value = "Nom Res"
  109.         'On complète dynamiquement les types d'appartements qui sont dans la Feuil2
  110.         'On en profite pour faire un dictionnaire qui facilitera plus tard l'insertion des données
  111.         i = 3 'On commence à la colonne 3
  112.         'Puis on parcourt la liste donnée sur la feuille 2
  113.         j = 1
  114.         Do
  115.             strType = BdTyp.Cells(j, 1) 'On lit la valeur dans la cellule de la colonne 1
  116.             If strType = "" Then Exit Do 'Si la cellule est vide, fin de liste on quitte la boucle
  117.             'on écrit le type de l'appartement dans l'entete de la feuille résultat
  118.             .Cells(1, i) = strType
  119.             'On écrit le dictionnaire
  120.             DicTmp.Add strType, i
  121.             i = i + 1 'On incrémente la colonne en prévision de la valeur suivante
  122.             j = j + 1 'Et on passe à la ligne suivante dans la feuille 2
  123.         Loop
  124.     End With
  125.     Set CompleterEntete = DicTmp
  126.     Set DicTmp = Nothing
  127. End Function
  128. 'Cette fonction crée une nouvelle feuille résultat nommée par la constante FeuilRes
  129. 'Elle renvoie un objet de type Worsheet
  130. 'Si la feuille de résultat existe déjà, elle est supprimée
  131. Private Function CreerNouvelleFeuilleResultat() As Worksheet
  132. 'Si la feuille de résultat existe, la supprimer :
  133.     If FeuilleExiste(FeuilRes) Then
  134.         Application.DisplayAlerts = False 'Suppression du message d'alerte
  135.         Worksheets(FeuilRes).Delete 'Supprimer la feuille
  136.         Application.DisplayAlerts = True 'On réaffiche les futurs messages d'alerte
  137.     End If
  138. 'On crée la feuille résultat
  139.     Set CreerNouvelleFeuilleResultat = ActiveWorkbook.Worksheets.Add 'Rajout d'une nouvelle feuille
  140.     CreerNouvelleFeuilleResultat.Name = FeuilRes 'On renomme la feuille créée
  141. End Function
  142. 'Cette fonction renvoie True si la feuille portant le nom donné en paramètre existe
  143. 'Renvoie FALSE si elle n'existe pas
  144. Private Function FeuilleExiste(Nom As String) As Boolean
  145. 'Déclaration des variables
  146.     Dim WS As Worksheet
  147. 'On parcourt les feuilles dans le classeur actif à la recherche d'une feuille <Nom>
  148.     For Each WS In ActiveWorkbook.Worksheets
  149.         If WS.Name = Nom Then 'Si le nom de la feuille est le nom de la feuille résultat
  150.             FeuilleExiste = True
  151.             Exit Function 'Sortir de la fonction
  152.         End If
  153.     Next
  154. End Function


Bon courage :)

Message cité 1 fois
Message édité par otobox le 19-05-2013 à 09:02:23

---------------
OtObOxBlOg - - - Etre seul à avoir tort  c'est plus difficile, mais c'est bien plus beau que d'avoir raison avec une bande de cons
Reply

Marsh Posté le 21-05-2013 à 12:08:31    

 

otobox a écrit :

je ne passe pas par des tableaux vba (ça bouffe de la mémoire), mais je travaille directement sur les cellules.


           Et pourtant travailler sur une variable tableau est tellement plus rapide que de parcourir les cellules …
 
           Sinon tu t'es bien amusé pour ton code !
           
           De mon côté, dans l'hypothèse d'une structure logique de la feuille n°2 vu la demande,
           je n'envisageais pas plus d'une douzaine de ligne de code afin d'alimenter la feuille n°3 …
 

Reply

Marsh Posté le 21-05-2013 à 19:19:42    

La procédure servant à trier les données fait moins de 50 lignes, avec la déclaration des variables et les commentaires.
Quelle est ta solution ?


---------------
OtObOxBlOg - - - Etre seul à avoir tort  c'est plus difficile, mais c'est bien plus beau que d'avoir raison avec une bande de cons
Reply

Marsh Posté le 22-05-2013 à 00:33:01    

 
           En fait je n'ai rien écrit vu que j'attendais une réponse, mais dans une certaine logique de clef commune entre les feuilles 1 & 2,
           j'envisageais (c'est un peu comme j'imaginais, non ?) une procédure assez simple pour alimenter la feuille 3,
           en somme une ébauche virtuelle cérébrale, donc rien de concret vu le manque de données objectives …
 
           En fait c'était juste pour te dire que tu t'étais bien défoncé !
 
           Pour un sujet récent sur un autre forum (et posté ici aussi) concernant un exercice cette fois bien présenté par une étudiante,
           suite aux palabres d'intervenants quant à ce qu'ils devaient mettre en œuvre,
           j'ai annoncé qu'il ne fallait pas plus d'une cinquantaine de lignes de code pour réaliser le programme.
           Puis leurs solutions de 100 lignes et plus ont été postées dont une à plus de 200 et une autre à quasiment 300 lignes !
           J'ai aussi réalisé puis publié mon code résolvant l'énoncé et tenant bien en une cinquantaine de lignes …
           Comme quoi à un problème peuvent correspondre diverses solutions, selon des critères propres à chacun !
 

Reply

Marsh Posté le 22-05-2013 à 16:58:13    

Bonjour,
 
Veuillez m'excuser pour le silence mais j'ai beaucoup travaillé et pas qu'en VBA.
 
Mais voici le résultat attendu par mon collègue (qu'on a composé tout les deux du coup!)  
 

Code :
  1. Sub ventil()
  2. '
  3. ' ventil Macro
  4. '
  5.     Dim i As Integer
  6.     Dim j As Integer
  7.     Dim k As Integer
  8.     Dim cpt As Integer
  9.        
  10.     Dim tabl
  11.     Dim tbtyp
  12.     Dim tblsor(300, 32)
  13.     Sheets("feuil1" ).Select
  14.     tabl = Range("a2:d5000" )
  15. '
  16.     Sheets("feuil2" ).Select
  17.     tbtyp = Range("a2:b32" )
  18. '
  19. '   initialisation table tblsor
  20. '
  21.     For i = 0 To 299
  22.         For j = 0 To 31
  23.             tblsor(i, j) = 0
  24.         Next j
  25.     Next i
  26. '
  27.     i = 1
  28.     k = 1
  29.     j = 0
  30.     While tabl(i, 1) <> ""
  31.         critère = tabl(i, 1)
  32.         res = tabl(i, 4)
  33.         cpt = 0
  34.         typ = tabl(i, 3)
  35.         While tabl(i, 1) = critère
  36.             k = 1
  37.             While tbtyp(k, 1) <> tabl(i, 3) And k < 32
  38.          
  39.             k = k + 1
  40.             Wend
  41.             If tabl(i, 3) = tbtyp(k, 1) Then tblsor(j, k + 2) = tblsor(j, k + 2) + 1
  42.        
  43.             i = i + 1
  44.         Wend
  45.         k = 1
  46.        
  47.         tblsor(j, 0) = critère
  48.         tblsor(j, 1) = res
  49.         j = j + 1
  50.    
  51.        
  52.     Wend
  53.         Sheets("résultat" ).Select
  54.         pos = "C3"
  55.         Range(pos).Select
  56.         ActiveCell.Value = tbtyp(1, 1)
  57.    
  58.  
  59.     Sheets("résultat" ).Select
  60.     Range("a4:ar300" ).Value = tblsor
  61. End Sub


 
et Voici a quoi ressemble le résultat :
 
Res Nom  1B CHAM D1 D3 D4 D5 D6 GG I2 I3 I4 I5 I6 ST T1 T1BIS T2 T2/3
100  CHAM                 29
140                   5
150                   10
180                    
190 RES. LE PARC DES SAULES                  
200                   24
300                148 17  
400 BAT B                92 26 6
500                   34
 
 
ça c'est un peu décalé mais cela vous donne une idée.
 
Je suis en train de répondre a une autre demande du même genre...
 
Merci a tous, vos remarques sont très constructives et nous permette de progresser.

Reply

Marsh Posté le 22-05-2013 à 19:04:16    

 
         Autres remarques constructives quant au dernier code :
 

  • Eviter les affreux ralentisseurs  Activate  &  Select  !

         Exemple des lignes n°16 & 17 pouvant s'écrire en une seule :    tabl =  Sheets("Feuil1" ).Range("A2:D5000" )
 

  • Les lignes n°24 à 28 ne servent pas à grand chose !

         La variable tableau déclarée en ligne n°14 est donc de type Variant (par défaut sans précision du type)
         et donc chaque indice ne contient rien par défaut et en VBA rien veut aussi dire zéro ...
 
         Dans le cas où cette variable ne doit contenir que des nombres, mieux vaut alors la déclarer avec le type adéquat (Long, Double, …)
         et dès lors suite à cette déclaration la valeur par défaut est déjà à zéro …
 

  • La variable initialisée en ligne n°58 n'est utilisée qu'une seule fois en ligne suivante et qui plus est avec un Select !

         Autant réécrire directement les lignes n°58 à 60 en une seule :   Range("C3" ).Value = tbtyp(1, 1)
 

  • La ligne n°64 ne sert à rien, voir la ligne n°57 !   (et pour les puristes ce serait plutôt un Activate qu'un Select …)


         En résumé, travailler directement sur les objets est bien plus efficace !
 
         Sinon je n'ai pas décrypté la logique de la procédure pour voir si elle peut être améliorée
         (à l'origine j'envisageais une seule boucle combinée à la véloce méthode Find) …


Message édité par Marc L le 22-05-2013 à 19:11:55
Reply

Marsh Posté le    

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed