Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

optimisation d'un programme

P

pedro59940

XLDnaute Nouveau
Bonjour tout le monde,

Je suis débutant et commence à programmer un peu sur excel via des macros. J'utilise la fonction "enregistrer une macro" et voici ci-dessous le programme que j'obtient qui est très long et met 5 min à s'executer. J'aimerai l'optimiser pour gagner du temps mais je ne sais pas comment étant donné que si je fais des boucles, je ne peux mettre aucune variable dans ActiveCell.FormulaR1C1 = "='tri data BM1'!R[1]C[-4]".

Pouvez-vous m'aidez ?

Merci d'avance

le programme :

Sub Macro2()
'
' Macro2 Macro
'

'

Sheets("calculation").Select

For A = 3 To 32

Cells(A, 7).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[1]C[-4]"
Cells(A, 8).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[1]C[-5]"
Cells(A, 9).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[1]C[-6]"
Cells(A, 10).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[1]C[-7]"
Cells(A, 11).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[1]C[-8]"
Cells(A, 12).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[1]C[-9]"
Cells(A, 13).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[1]C[-10]"
Cells(A, 14).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[1]C[-11]"

Cells(A, 21).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[31]C[-18]"
Cells(A, 22).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[31]C[-19]"
Cells(A, 23).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[31]C[-20]"
Cells(A, 24).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[31]C[-21]"
Cells(A, 25).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[31]C[-22]"
Cells(A, 26).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[31]C[-23]"
Cells(A, 27).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[31]C[-24]"
Cells(A, 28).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[31]C[-25]"

Cells(A, 35).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[61]C[-32]"
Cells(A, 36).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[61]C[-33]"
Cells(A, 37).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[61]C[-34]"
Cells(A, 38).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[61]C[-35]"
Cells(A, 39).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[61]C[-36]"
Cells(A, 40).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[61]C[-37]"
Cells(A, 41).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[61]C[-38]"
Cells(A, 42).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[61]C[-39]"

Cells(A, 49).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[91]C[-46]"
Cells(A, 50).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[91]C[-47]"
Cells(A, 51).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[91]C[-48]"
Cells(A, 52).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[91]C[-49]"
Cells(A, 53).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[91]C[-50]"
Cells(A, 54).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[91]C[-51]"
Cells(A, 55).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[91]C[-52]"
Cells(A, 56).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[91]C[-53]"

Cells(A, 63).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[121]C[-60]"
Cells(A, 64).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[121]C[-61]"
Cells(A, 65).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[121]C[-62]"
Cells(A, 66).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[121]C[-63]"
Cells(A, 67).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[121]C[-64]"
Cells(A, 68).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[121]C[-65]"
Cells(A, 69).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[121]C[-66]"
Cells(A, 70).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[121]C[-67]"

Cells(A, 77).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[151]C[-74]"
Cells(A, 78).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[151]C[-75]"
Cells(A, 79).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[151]C[-76]"
Cells(A, 80).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[151]C[-77]"
Cells(A, 81).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[151]C[-78]"
Cells(A, 82).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[151]C[-79]"
Cells(A, 83).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[151]C[-80]"
Cells(A, 84).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[151]C[-81]"

Cells(A, 91).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[181]C[-88]"
Cells(A, 92).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[181]C[-89]"
Cells(A, 93).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[181]C[-90]"
Cells(A, 94).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[181]C[-91]"
Cells(A, 95).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[181]C[-92]"
Cells(A, 96).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[181]C[-93]"
Cells(A, 97).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[181]C[-94]"
Cells(A, 98).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[181]C[-95]"

Next A

For A = 44 To 73

Cells(A, 7).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[-40]C[-3]"
Cells(A, 8).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[-40]C[-4]"
Cells(A, 9).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[-40]C[-5]"
Cells(A, 10).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[-40]C[-6]"
Cells(A, 11).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[-40]C[-7]"
Cells(A, 12).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[-40]C[-8]"
Cells(A, 13).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[-40]C[-9]"
Cells(A, 14).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[-40]C[-10]"

Cells(A, 21).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[-10]C[-17]"
Cells(A, 22).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[-10]C[-18]"
Cells(A, 23).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[-10]C[-19]"
Cells(A, 24).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[-10]C[-20]"
Cells(A, 25).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[-10]C[-21]"
Cells(A, 26).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[-10]C[-22]"
Cells(A, 27).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[-10]C[-23]"
Cells(A, 28).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[-10]C[-24]"

Cells(A, 35).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[20]C[-31]"
Cells(A, 36).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[20]C[-32]"
Cells(A, 37).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[20]C[-33]"
Cells(A, 38).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[20]C[-34]"
Cells(A, 39).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[20]C[-35]"
Cells(A, 40).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[20]C[-36]"
Cells(A, 41).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[20]C[-37]"
Cells(A, 42).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[20]C[-38]"

Cells(A, 49).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[50]C[-45]"
Cells(A, 50).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[50]C[-46]"
Cells(A, 51).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[50]C[-47]"
Cells(A, 52).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[50]C[-48]"
Cells(A, 53).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[50]C[-49]"
Cells(A, 54).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[50]C[-50]"
Cells(A, 55).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[50]C[-51]"
Cells(A, 56).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[50]C[-52]"

Cells(A, 63).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[80]C[-59]"
Cells(A, 64).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[80]C[-60]"
Cells(A, 65).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[80]C[-61]"
Cells(A, 66).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[80]C[-62]"
Cells(A, 67).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[80]C[-63]"
Cells(A, 68).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[80]C[-64]"
Cells(A, 69).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[80]C[-65]"
Cells(A, 70).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[80]C[-66]"

Cells(A, 77).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[110]C[-73]"
Cells(A, 78).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[110]C[-74]"
Cells(A, 79).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[110]C[-75]"
Cells(A, 80).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[110]C[-76]"
Cells(A, 81).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[110]C[-77]"
Cells(A, 82).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[110]C[-78]"
Cells(A, 83).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[110]C[-79]"
Cells(A, 84).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[110]C[-80]"

Cells(A, 91).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[140]C[-87]"
Cells(A, 92).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[140]C[-88]"
Cells(A, 93).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[140]C[-89]"
Cells(A, 94).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[140]C[-90]"
Cells(A, 95).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[140]C[-91]"
Cells(A, 96).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[140]C[-92]"
Cells(A, 97).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[140]C[-93]"
Cells(A, 98).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[140]C[-94]"

Next A



[...]

For A = 1151 To 1180

Sheets("calculation").Select
Cells(A, 7).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[-1024]C[21]"
Cells(A, 8).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[-1024]C[20]"
Cells(A, 9).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[-1024]C[19]"
Cells(A, 10).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[-1024]C[18]"
Cells(A, 11).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[-1024]C[17]"
Cells(A, 12).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[-1024]C[16]"
Cells(A, 13).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[-1024]C[15]"
Cells(A, 14).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[-1024]C[14]"

Sheets("calculation").Select
Cells(A, 21).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[-994]C[7]"
Cells(A, 22).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[-994]C[6]"
Cells(A, 23).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[-994]C[5]"
Cells(A, 24).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[-994]C[4]"
Cells(A, 25).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[-994]C[3]"
Cells(A, 26).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[-994]C[2]"
Cells(A, 27).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[-994]C[1]"
Cells(A, 28).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[-994]C[0]"
Cells(A, 29).Select

Sheets("calculation").Select
Cells(A, 35).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[-964]C[-7]"
Cells(A, 36).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[-964]C[-8]"
Cells(A, 37).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[-964]C[-9]"
Cells(A, 38).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[-964]C[-10]"
Cells(A, 39).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[-964]C[-11]"
Cells(A, 40).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[-964]C[-12]"
Cells(A, 41).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[-964]C[-13]"
Cells(A, 42).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[-964]C[-14]"
Cells(A, 43).Select

Sheets("calculation").Select
Cells(A, 49).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[-934]C[-21]"
Cells(A, 50).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[-934]C[-22]"
Cells(A, 51).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[-934]C[-23]"
Cells(A, 52).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[-934]C[-24]"
Cells(A, 53).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[-934]C[-25]"
Cells(A, 54).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[-934]C[-26]"
Cells(A, 55).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[-934]C[-27]"
Cells(A, 56).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[-934]C[-28]"
Cells(A, 57).Select

Sheets("calculation").Select
Cells(A, 63).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[-904]C[-35]"
Cells(A, 64).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[-904]C[-36]"
Cells(A, 65).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[-904]C[-37]"
Cells(A, 66).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[-904]C[-38]"
Cells(A, 67).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[-904]C[-39]"
Cells(A, 68).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[-904]C[-40]"
Cells(A, 69).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[-904]C[-41]"
Cells(A, 70).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[-904]C[-42]"
Cells(A, 71).Select

Sheets("calculation").Select
Cells(A, 77).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[-874]C[-49]"
Cells(A, 78).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[-874]C[-50]"
Cells(A, 79).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[-874]C[-51]"
Cells(A, 80).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[-874]C[-52]"
Cells(A, 81).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[-874]C[-53]"
Cells(A, 82).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[-874]C[-54]"
Cells(A, 83).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[-874]C[-55]"
Cells(A, 84).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[-874]C[-56]"
Cells(A, 85).Select

Sheets("calculation").Select
Cells(A, 91).Select
ActiveCell.FormulaR1C1 = "='tri data BM1'!R[-844]C[-63]"
Cells(A, 92).Select
ActiveCell.FormulaR1C1 = "='tri data BM2'!R[-844]C[-64]"
Cells(A, 93).Select
ActiveCell.FormulaR1C1 = "='tri data BM3'!R[-844]C[-65]"
Cells(A, 94).Select
ActiveCell.FormulaR1C1 = "='tri data BM4'!R[-844]C[-66]"
Cells(A, 95).Select
ActiveCell.FormulaR1C1 = "='tri data BM5'!R[-844]C[-67]"
Cells(A, 96).Select
ActiveCell.FormulaR1C1 = "='tri data BM8'!R[-844]C[-68]"
Cells(A, 97).Select
ActiveCell.FormulaR1C1 = "='tri data BM9'!R[-844]C[-69]"
Cells(A, 98).Select
ActiveCell.FormulaR1C1 = "='tri data BM10'!R[-844]C[-70]"
Cells(A, 99).Select


Next A

End Sub
 
Trier par date Trier par votes

Grand Chaman Excel

XLDnaute Impliqué
Re : optimisation d'un programme

Bonjour pedro,

Il y a sûrement une façon de simplifier ton code mais sans voir ton fichier c'est plus difficile à visualiser
Peux-tu joindre un exemple de ce que tu souhaites comme résultat avec des données non confidentielles ?

Si j'ai bien compris, voici un exemple que tu pourrais adapter.

VB: 
Sub toto()
    Dim i As Integer
    Dim j As Integer
    
    Application.ScreenUpdating = False
    
    For i = 3 To 32
        For j = 7 To 14
            Sheets("Calculation").Cells(i, j) = Sheets("tri data MB1").Cells(i + 1, 2)
        Next j
        
        For j = 21 To 28
            Sheets("Calculation").Cells(i, j) = Sheets("tri data MB1").Cells(i + 31, 2)
        Next j
        
        For j = 35 To 42
            Sheets("Calculation").Cells(i, j) = Sheets("tri data MB1").Cells(i + 61, 2)
        Next j

  'etc...
    Next i
    
    Application.ScreenUpdating = True
End Sub

Notes :
1. Evite les .Select. C'est inutile et ça ne fait que ralentir le code. L'enregistreur de macro les ajoute pour rien
2. Application.ScreenUpdating = False : sert à éviter le raffraichissement de l'écran pendant l'exécution de la macro. C'est aussi plus rapide aussi. Il ne faut pas oublier de remettre la valeur à TRUE à la fin de la macro.

A+
 
Vote positif 0 Vote négatif
P

pedro59940

XLDnaute Nouveau
Re : optimisation d'un programme

Merci pour ton aide Grand Chaman Excel.

Ce que tu proposes ne peut pas être réalisable sur ma macro.

Je ne peux pas t'envoyer le fichier excel sur le forum car sa taille dépasse la limite autorisé, peux-tu me donner ton adresse mail pour que je te l'envoie dessus ?

Merci d'avance
 
Vote positif 0 Vote négatif
P

pedro59940

XLDnaute Nouveau
Re : optimisation d'un programme

Merci pour pour l'aide précieuse de Grand Chaman Excel qui m'a aidé sur ce programme.

voici le code fonctionnel :

Code: 
    Dim ws As Worksheet, wsCalc As Worksheet
    Dim rg As Range, c As Range, FirstAddress As String, rTemp As Range
    Dim sChannel As String, sRPM As String
    Dim lCol As Long, lLig As Long
    Dim rNom As Range
    Dim sNom As String
    Dim dStart As Double
    
    Application.ScreenUpdating = False
    dStart = Timer
    Set wsCalc = ThisWorkbook.Sheets("calculation")
    Set rg = wsCalc.UsedRange
    
    On Error Resume Next
    
    With rg
        Set c = .Find("Channel", , xlValues)
        FirstAddress = c.Address
        Do
            sChannel = c.Offset(1, 0)       '1 ligne sous le mot Channel
            sRPM = c.Offset(1, 1)           '1 colonne à droite du mot Channel
            Set rNom = c.Offset(0, 5)
            Do Until IsEmpty(rNom)
                sNom = rNom.Value             'nom du bench
                
                Application.StatusBar = "Exécution en cours... Channel: " & sChannel & ",RPM: " & sRPM & ",Bench: " & sNom
                
                
                Set ws = Sheets(sNom)       'on trouve la feuille du bench
                lCol = ws.Range("A2:ZZ2").Find(sChannel, , xlValues).Column 'column du channel
                lLig = ws.Range("C:C").Find(sRPM, , xlValues).Row            'ligne du rpm
                Set rTemp = ws.Cells(lLig, lCol).Resize(30, 1)               'plage à copier
                rTemp.Copy
                rNom.Offset(1, 0).PasteSpecial xlPasteValues
                Set rNom = rNom.Offset(0, 1)                        'prochain bench
            Loop
            Set c = .Find("Channel", c, xlValues)
        Loop While Not c Is Nothing And c.Address <> FirstAddress
    End With

    Application.ScreenUpdating = True
    MsgBox "Exécuté en : " & Timer - dStart & " secondes."
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

I
  • Question
XL 2019 Autofill - Une seule ligne
Réponses
1
Affichages
2 K
Staple1600
  • Question
XL 2010 Une fonction qui sature
Réponses
8
Affichages
2 K
Magic_Doctor
  • Question
XL 2010 Simplification code VBA(Résolu)
Réponses
12
Affichages
2 K
Kael_88
C
  • Question
XL 2013 Absence de prompt enregistrement après macro
Réponses
3
Affichages
929
Lone-wolf
M
  • Question
XL 2013 Optimisation de code pour une présentation
Réponses
5
Affichages
1 K
Merlin258413
M
M
  • Question
macro supprimer un certain nbr de lignes en fonction d'un comptage
Réponses
2
Affichages
813
merguez59
M
N
  • Question
Booster une macro
Réponses
5
Affichages
3 K
Staple1600
M
  • Question
Macro trop longue
Réponses
6
Affichages
2 K
Tirou
T
  • Question
Macro trop longue, et message d'erreur, à l'aide! SVP
Réponses
5
Affichages
13 K
Grand Chaman Excel
P
  • Question
Définir le nom d'un champ
Réponses
6
Affichages
3 K
phi.rousseau
P
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…