optimisation d'une macro très lente

[jozerebel]

Bonjour à tous!!!!

Avec l'aide de tous, en particulier papou et Pierre jean, j'ai construit un fichier me servant de suivi de prises en charge (secteur sanitaire et social).

Cependant, une macro (nommée chab sous VBA, faisant référence aux fonctions cha et chb du module 1 [merci Pierre Jean!]) met un temps fou, mais fou.... à s'exécuter.

Pour info, l'onglet A a quelques 2500 lignes, quelques 28000 lignes dans l'onglet "BDD pec".

Je joins le fichier, vide d'info, mais avec les macros... et le lien direct au cas où:



Si vous pouvez voir comment accélerer le traitement de la macro chab ...

D'avance merci!

PS : Utilisation de windows XP, office 2003 pro

 

[jozerebel]

Re : optimisation d'une macro très lente

Bonsoir Pierre Jean,

Après essai, ça ne fonctionne pas.

En effet, toutes les cellules doivent être renseignées.

Ainsi, si pour un client A et un mois de facturation 01/05/2010 je dois chercher dans la liste des prises en charge (bdd pec) quelle période de prise en charge comprend ce mois de facturation (ex: 01/06/2009 au 31/05/2010), et en prenant soin de prendre la période avec la date de début la plus récente si plusieurs périodes correspondent intègrent ce mois de facturation.

La macro ne me récupère pas toutes les périodes de prise en charge pour des clients pourtant existants dans la feuille bdd pec.

Je n'ai toujours rien en j8:k12 alors que la personne existe en bdd pec...

Je devrais avoir "01/12/2009 30/11/2010" sur j8:k12.

De plus en J et K47, j'obtiens période du 01/01/2010 au 31/12/2011
alors que le mois de facturation est le 01/12/2009 (non compris dans la période trouvée par la macro).Je devrais plutôt avoir 01/01/2009 et 31/12/2009.

Je vous remercie par avance pour votre aide.

Bonne soirée Pierre Jean!

 

[pierrejean]

Re : optimisation d'une macro très lente

Re

Avec toutes mes excuses (une erreur de signe > au lieu de< ) que je m'obstinais a ne pas voir !!!

Sub Macro3()
Application.ScreenUpdating = False
    x = ActiveWorkbook.Worksheets("bdd pec").Range("A65536").End(xlUp).Row
    ActiveWorkbook.Worksheets("bdd pec").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("bdd pec").Sort.SortFields.Add Key:=Range("A2:A" & x) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("bdd pec").Sort.SortFields.Add Key:=Range("D2:D" & x) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("bdd pec").Sort
        .SetRange Range("A1:J" & x)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
For n = 2 To Sheets("A").Range("A65536").End(xlUp).Row
 client = Sheets("A").Range("A" & n)
 ladate = CDate(Sheets("A").Range("D" & n))
 Set c = Sheets("bdd pec").Columns(1).Find(client, LookIn:=xlValues, lookat:=xlWhole)
 If Not c Is Nothing Then
      firstAddress = c.Address
        Do
         If (CDate(c.Offset(0, 3)) <= ladate) And (ladate <= CDate(c.Offset(0, 4))) Then
          Sheets("A").Range("J" & n) = c.Offset(0, 3)
          Sheets("A").Range("K" & n) = c.Offset(0, 4)
         End If
           Set c = Sheets("bdd pec").Columns(1).FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
  End If
Next n
Application.ScreenUpdating = True
End Sub

 

[jozerebel]

Re : optimisation d'une macro très lente

Bonsoir Pierre Jean!

Merci pour cette aide très précieuse!

Après plusieurs contrôles sur certaines lignes, la macro semble fonctionner correctement. je vérifierai sur d'autres lignes dès demain.

PAr contre, la ligne de code .Sort.SortFields.Clear ne fonctionne que sur 2007 . Comment faire pour la faire marcher sur MSOffice 2003?

C'est un super travail qui m'aide vraiment!

Merci Pierre Jean pour votre dévouement à aider les xldnautes!

A bientôt!

 

[pierrejean]

Re : optimisation d'une macro très lente

Re

Adaptée a Excel 2000

Sub Macro3()
Application.ScreenUpdating = False
    x = ActiveWorkbook.Worksheets("bdd pec").Range("A65536").End(xlUp).Row
    Range("A1:J" & x).Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("D2") _
        , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
For n = 2 To Sheets("A").Range("A65536").End(xlUp).Row
 client = Sheets("A").Range("A" & n)
 ladate = CDate(Sheets("A").Range("D" & n))
 Set c = Sheets("bdd pec").Columns(1).Find(client, LookIn:=xlValues, lookat:=xlWhole)
 If Not c Is Nothing Then
      firstAddress = c.Address
        Do
         If (CDate(c.Offset(0, 3)) <= ladate) And (ladate <= CDate(c.Offset(0, 4))) Then
          Sheets("A").Range("J" & n) = c.Offset(0, 3)
          Sheets("A").Range("K" & n) = c.Offset(0, 4)
         End If
           Set c = Sheets("bdd pec").Columns(1).FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
  End If
Next n
Application.ScreenUpdating = True
End Sub

 

[jozerebel]

Re : optimisation d'une macro très lente

Avec encore tous mes remerciements!

A bientôt!

 

[jozerebel]

Re : optimisation d'une macro très lente

Bonjour à tous

La macro de Pierre Jean fonctionne parfaitement quand les clients sont stockés sous forme de texte.

Le problème c est qu aujourd'hui je souhaiterais remplacer les noms des clients par des clés uniques représentées par des numéros de sécurité sociale.

Mais la macro ne marche pas avec les num de sécu..

Comment faire?


D avance merci pour vos réponses.

Bonne soirée

 

[pierrejean]

Re : optimisation d'une macro très lente

Bonjour jorezebel

Que veux dire "ne fonctionne pas " ?
Les noms des clients sont bien remplacés dans les 2 feuilles ? (A et bdd pec)

 

[jozerebel]

Re : optimisation d'une macro très lente

Bonjour Pierre Jean

Dans les deux onglets les noms ont été remplaces par des num de sécu et là, la macro ne fonctionne plus, c est à dire que rien ne s affiche. Si je change un num de sécu par un mot alors ça refonctionne...

 

[pierrejean]

Re : optimisation d'une macro très lente

Re

Si les colonnes concernées sont en format Texte il n'y a aucune raison que cela ne fonctionne pas
Et j'attends toujours que tu me dises ce que sigifie "ne fonctionne pas" : provoque un bug ; ne reporte pas les dates ; reporte des dates fausses ; etc.....
creee un fichier exemple avec une 20 aine de ligne par feuille et poste le

 

[jozerebel]

Re : optimisation d'une macro très lente

Salut Pierre Jean,

CI-joint le fichier retravaillé avec la macro quelque peu modifiée (juste quelques noms de feuilles et quelques colonnes).

La macro s'exécute mais rien ne s'affiche dans les cellules...

J'espère que tu pourras m'aider.

Bon dimanche et merci encore!

 

[pierrejean]

Re : optimisation d'une macro très lente

Re

Les colonnes doivent etre mises au format texte avant de renseigner les valeurs

 

[jozerebel]

Re : optimisation d'une macro très lente

Encore une fois merci Pierre Jean!