Tweet
chipeur
merci mais de ce coté là c'est bon ...
je suis plutôt à la recherche d'infos sur comment optimiser le temps de traitement des données via les macros VBA
Pour exemple voilà le genre de code sur lesquels je planche en ce moment :
BorneHi = 79 + q
BorneLo = BorneHi + q
Cells(BorneHi, c - 2).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.ScreenUpdating = False
If c = 46 Then Name = "DowYM"
If c = 24 Then Name = "DaxFut"
If c = 30 Then Name = "Airbus"
If c = 39 Then Name = "SoGen"
Windows("Research").Activate
Sheets(Name).Select
Nr = Range("A65536").End(xlUp).Row
For i = 1 To 28
For j = 1 To 44
Transfert(i, j) = Cells(Nr + 9 + i, 62 + j).Value
Next j
Next i
Windows("DAILY").Activate
Sheets("Controle").Select
' remise neutre valeurs C
Range(Cells(80, c - 5), Cells(BorneHi, c - 3)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
Range(Cells(BorneHi + 1, c - 5), Cells(94, c - 3)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
' MsgBox ("on attaque le dur")
For i = 80 To 93
N = i - 79
Cells(j, c - 5).Value = "C" & N
Next i
' Hi
For i = 1 To q + 1
' For j = 79 + i To 79 + q
Cells(79 + i, v).Value = Transfert(p, 15 - i)
Cells(79 + i, v - 1).Value = Transfert(p + 1, 15 - i)
m = m + 1
Next i
' traitement règle 80%
'For i = q + 1 To 1 Step -1
' Stats = Stats + Cells(79 + i, v - 1).Value
' If Stats > 0.8 Then
' Range(Cells(79 + q + 1, v - 1), Cells(79 + i)).Select
' With Selection.Interior
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
' .Color = 49407
' .TintAndShade = 0
' .PatternTintAndShade = 0
' End With
' End If
' Next i
g = 28 - q
For j = 80 + q To 94
z = z + 1
' b = b - 1
Cells(j, v).Value = Transfert(p, g - z + 2)
Cells(j, v - 1).Value = Transfert(p + 1, g - z + 2)
Cells(j, v - 2).Value = "C" & m - 2 + z
Next j
' traitement règle 80%
Line200:
' Cloture
d = -13
m = 13
For i = 95 To 108
Cells(i, v).Value = Transfert(p, 29 + m)
Cells(i, v - 1).Value = Transfert(p + 1, 29 + m)
m = m - 1
Next i
' nettoyage cellule
Erase Transfert
Application.EnableEvents = False
Cells(95, c).Select
' traitement Case Open
cp = ActiveCell.Column
OpenVal = Cells(95, c).Value
For j = 81 To 93
ValC = Cells(j, cp).Value
If OpenVal < ValC Then GoTo Line55
Cells(j - 1, cp - 2).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
' .Color = 5296274
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
GoTo Line100
Line55:
Next j
Line100:
Application.EnableEvents = True
merci mais de ce coté là c'est bon ...
je suis plutôt à la recherche d'infos sur comment optimiser le temps de traitement des données via les macros VBA
Pour exemple voilà le genre de code sur lesquels je planche en ce moment :
BorneHi = 79 + q
BorneLo = BorneHi + q
Cells(BorneHi, c - 2).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.ScreenUpdating = False
If c = 46 Then Name = "DowYM"
If c = 24 Then Name = "DaxFut"
If c = 30 Then Name = "Airbus"
If c = 39 Then Name = "SoGen"
Windows("Research").Activate
Sheets(Name).Select
Nr = Range("A65536").End(xlUp).Row
For i = 1 To 28
For j = 1 To 44
Transfert(i, j) = Cells(Nr + 9 + i, 62 + j).Value
Next j
Next i
Windows("DAILY").Activate
Sheets("Controle").Select
' remise neutre valeurs C
Range(Cells(80, c - 5), Cells(BorneHi, c - 3)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
Range(Cells(BorneHi + 1, c - 5), Cells(94, c - 3)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
' MsgBox ("on attaque le dur")
For i = 80 To 93
N = i - 79
Cells(j, c - 5).Value = "C" & N
Next i
' Hi
For i = 1 To q + 1
' For j = 79 + i To 79 + q
Cells(79 + i, v).Value = Transfert(p, 15 - i)
Cells(79 + i, v - 1).Value = Transfert(p + 1, 15 - i)
m = m + 1
Next i
' traitement règle 80%
'For i = q + 1 To 1 Step -1
' Stats = Stats + Cells(79 + i, v - 1).Value
' If Stats > 0.8 Then
' Range(Cells(79 + q + 1, v - 1), Cells(79 + i)).Select
' With Selection.Interior
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
' .Color = 49407
' .TintAndShade = 0
' .PatternTintAndShade = 0
' End With
' End If
' Next i
g = 28 - q
For j = 80 + q To 94
z = z + 1
' b = b - 1
Cells(j, v).Value = Transfert(p, g - z + 2)
Cells(j, v - 1).Value = Transfert(p + 1, g - z + 2)
Cells(j, v - 2).Value = "C" & m - 2 + z
Next j
' traitement règle 80%
Line200:
' Cloture
d = -13
m = 13
For i = 95 To 108
Cells(i, v).Value = Transfert(p, 29 + m)
Cells(i, v - 1).Value = Transfert(p + 1, 29 + m)
m = m - 1
Next i
' nettoyage cellule
Erase Transfert
Application.EnableEvents = False
Cells(95, c).Select
' traitement Case Open
cp = ActiveCell.Column
OpenVal = Cells(95, c).Value
For j = 81 To 93
ValC = Cells(j, cp).Value
If OpenVal < ValC Then GoTo Line55
Cells(j - 1, cp - 2).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
' .Color = 5296274
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
GoTo Line100
Line55:
Next j
Line100:
Application.EnableEvents = True
Commentaire