Lorenzogazier
XLDnaute Nouveau
Bonjour,
j'utilise une Macro qui met en forme un fichier selon qu'il est du type A, B, C,
D, ou E. Précédemment elle fonctionnait sous 2000, mais depuis que je suis
passé sous 2007, j'ai des problèmes de #N/A ou #VALEURS dans certaines
colonnes des fichiers A et E.
Je vous joindrai les fichiers par mail, car trop volumineux, en attendant merci
d'avance pour vos réponses, voici le code de la macro :
Public var2
Sub AFAC()
varp2 = Now()
Application.ScreenUpdating = False
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(LEFT(RC[-1],11),1)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
var2 = ActiveCell.Value
If var2 = "A" Then
Call AFACA
Else
If var2 = "B" Then
Call AFACB
Else
If var2 = "C" Then
Call AFACC
Else
If var2 = "D" Then
Call AFACD
Else
If var2 = "E" Then
Call AFACE
Else
Application.ScreenUpdating = True
varp3 = Now()
MsgBox "Ce n'est pas un fichier AFAC A B C D ou E !!!!! (Temps de traitement = " & Format(varp3 - varp2, "HH:MM:SS") & ")", vbInformation, "Finish"
Exit Sub
End If
End If
End If
End If
End If
Application.ScreenUpdating = True
varp3 = Now()
MsgBox "Mise en forme de l'AFAC " & var2 & " terminée ! (Temps de traitement = " & Format(varp3 - varp2, "HH:MM:SS") & ")", vbInformation, "Finish"
End Sub
Sub AFACA()
Dim var1 'nombre de ligne
'Préparation
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.ClearContents
Range("A2").Select
Selection.End(xlDown).Select
Selection.ClearContents
var1 = ActiveCell.Row - 1
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1))
Cells.Select
Cells.EntireColumn.AutoFit
'Période
Range("A1").Select
ActiveCell.FormulaR1C1 = "Période"
Range("U2").Select
ActiveCell.FormulaR1C1 = "=DATE(LEFT(RC[-20],4),RIGHT(RC[-20],2),1)"
Selection.Copy
Range("U2:U" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.NumberFormat = "mmmm-yy"
Range("U2:U" & var1).Select
Selection.ClearContents
'ZET
Range("B1").Select
ActiveCell.FormulaR1C1 = "ZET"
'PDLA
Range("C1").Select
ActiveCell.FormulaR1C1 = "PDL"
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEFT(RC[-18],1)=""4"",LEFT(RC[-18],1)=""5"",LEFT(RC[-18],1)=""6"",LEFT(RC[-18],1)=""7""),""0""&FIXED(RC[-18],0,1),RC[-18])"
Selection.Copy
Range("U2:U" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("U2:U" & var1).Select
Selection.ClearContents
'suppression colonne D
Columns("D
").Select
Selection.Delete Shift:=xlToLeft
'Tarif
Range("D1").Select
ActiveCell.FormulaR1C1 = "Tarif"
'Fréq
Range("E1").Select
ActiveCell.FormulaR1C1 = "Fréq"
'multi-PCE
Range("F1").Select
ActiveCell.FormulaR1C1 = "PDL multi-PCE"
'fact mois anté
Range("G1").Select
ActiveCell.FormulaR1C1 = "Fact mois anté"
'Ajust manuel
Range("H1").Select
ActiveCell.FormulaR1C1 = "Ajust manuel"
'Annul fact
Range("I1").Select
ActiveCell.FormulaR1C1 = "Annul Fact"
'Date début
Range("J1").Select
ActiveCell.FormulaR1C1 = "Date début"
Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-10],4),LEFT(RIGHT(RC[-10],4),2),RIGHT(RC[-10],2))"
Selection.Copy
Range("T2:T" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("J2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("T2:T" & var1).Select
Selection.ClearContents
Range("J2:J" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Date fin
Range("K1").Select
ActiveCell.FormulaR1C1 = "Date fin"
Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-9],4),LEFT(RIGHT(RC[-9],4),2),RIGHT(RC[-9],2))"
Selection.Copy
Range("T2:T" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("K2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("T2:T" & var1).Select
Selection.ClearContents
Range("K2:K" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'code agrégé
Range("L1").Select
ActiveCell.FormulaR1C1 = "code agrégé"
'code ind
Range("M1").Select
ActiveCell.FormulaR1C1 = "code ind"
'justif ajust manuel
Range("N1").Select
ActiveCell.FormulaR1C1 = "justif ajust manuel"
'Quantité
Range("O1").Select
ActiveCell.FormulaR1C1 = "Quantité"
Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RIGHT(RC[-5],1)=""-"",""-""&LEFT(RC[-5],LEN(RC[-5])-1),RC[-5])"
Selection.Copy
Range("T2:T" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("O2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("T2:T" & var1).Select
Selection.ClearContents
Range("O2:O" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'Prorata temporis
Range("P1").Select
ActiveCell.FormulaR1C1 = "Prorata temporis"
Range("P2" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.00"
'Prix unitaire
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Prix unitaire"
Range("Q2:Q" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.00"
'Montant
Range("R1").Select
ActiveCell.FormulaR1C1 = "Montant"
Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RIGHT(RC[-2],1)=""-"",""-""&LEFT(RC[-2],LEN(RC[-2])-1),RC[-2])"
Selection.Copy
Range("T2:T" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("R2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("T2:T" & var1).Select
Selection.ClearContents
Range("R2:R" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.00"
'TVA
Range("S1").Select
ActiveCell.FormulaR1C1 = "TVA"
Range("S2:S" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=RC[-1]/100"
Selection.Copy
Range("T2:T" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("S2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("T2:T" & var1).Select
Selection.ClearContents
Range("S2:S" & var1).Select
Selection.NumberFormat = "0.0%"
'Mise en forme finale
Range("A1:S1").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("A1:S" & var1).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("O2:S" & var1).Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1:S" & var1).Select
Selection.AutoFilter
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
Sub AFACB()
Dim var1 'nombre de ligne
'Préparation
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.ClearContents
Range("A2").Select
Selection.End(xlDown).Select
Selection.ClearContents
var1 = ActiveCell.Row - 1
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1))
Cells.Select
Cells.EntireColumn.AutoFit
'Période
Range("A1").Select
ActiveCell.FormulaR1C1 = "Période"
Range("Z2").Select
ActiveCell.FormulaR1C1 = "=DATE(LEFT(RC[-25],4),RIGHT(RC[-25],2),1)"
Selection.Copy
Range("Z2:Z" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.NumberFormat = "mmmm-yy"
Range("Z2:Z" & var1).Select
Selection.ClearContents
'ZET
Range("B1").Select
ActiveCell.FormulaR1C1 = "ZET"
'PDLA
Range("C1").Select
ActiveCell.FormulaR1C1 = "PDL"
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEFT(RC[-23],1)=""4"",LEFT(RC[-23],1)=""5"",LEFT(RC[-23],1)=""6"",LEFT(RC[-23],1)=""7""),""0""&FIXED(RC[-23],0,1),RC[-23])"
Selection.Copy
Range("Z2:Z" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("Z2:Z" & var1).Select
Selection.ClearContents
'suppression colonne D
Columns("D").Select
Selection.Delete Shift:=xlToLeft
'Tarif
Range("D1").Select
ActiveCell.FormulaR1C1 = "Tarif"
'n° du PCE
Range("E1").Select
ActiveCell.FormulaR1C1 = "PCE"
Range("E2:E" & var1).Select
Selection.NumberFormat = "@"
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[-21],2)=""GI"",RC[-21],IF(LEN(RC[-21])=13,""0""&RC[-21],FIXED(RC[-21],0,1)))"
Selection.Copy
Range("Z2:Z" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("E2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("Z2:Z" & var1).Select
Selection.ClearContents
'Fréq
Range("F1").Select
ActiveCell.FormulaR1C1 = "Fréq"
'Raison du relevé
Range("G1").Select
ActiveCell.FormulaR1C1 = "Raison du relevé"
'Date début
Range("H1").Select
ActiveCell.FormulaR1C1 = "Date début"
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-18],4),LEFT(RIGHT(RC[-18],4),2),RIGHT(RC[-18],2))"
Selection.Copy
Range("Z2:Z" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("H2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("Z2:Z" & var1).Select
Selection.ClearContents
Range("H2:H" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Date fin
Range("I1").Select
ActiveCell.FormulaR1C1 = "Date fin"
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-17],4),LEFT(RIGHT(RC[-17],4),2),RIGHT(RC[-17],2))"
Selection.Copy
Range("Z2:Z" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("I2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("Z2:Z" & var1).Select
Selection.ClearContents
Range("I2:I" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Ancien index
Range("J1").Select
ActiveCell.FormulaR1C1 = "Ancien index"
Range("J2:J" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0"
'Nouvel index
Range("K1").Select
ActiveCell.FormulaR1C1 = "Nouvel index"
Range("K2:K" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0"
'Volume brut
Range("L1").Select
ActiveCell.FormulaR1C1 = "Volume brut"
Range("L2:L" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0"
'Ancien index converti
Range("M1").Select
ActiveCell.FormulaR1C1 = "Ancien index converti"
Range("M2:M" & var1).Select
Selection.NumberFormat = "#,##0"
'Nouvel index converti
Range("N1").Select
ActiveCell.FormulaR1C1 = "Nouvel index converti"
Range("N2:N" & var1).Select
Selection.NumberFormat = "#,##0"
'Volume converti
Range("O1").Select
ActiveCell.FormulaR1C1 = "Volume converti"
Range("O2:O" & var1).Select
Selection.NumberFormat = "#,##0"
'Energie (MWh)
Range("P1").Select
ActiveCell.FormulaR1C1 = "Energie (MWh)"
Range("P2" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'Coefficient thermique
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Coef. therm."
Range("Q2:Q" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'PTA
Range("R1").Select
ActiveCell.FormulaR1C1 = "PTA"
Range("R2:R" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'PCS
Range("S1").Select
ActiveCell.FormulaR1C1 = "PCS"
Range("S2:S" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'???
Range("T1").Select
ActiveCell.FormulaR1C1 = "???"
'???
Range("U1").Select
ActiveCell.FormulaR1C1 = "???"
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-5],4),LEFT(RIGHT(RC[-5],4),2),RIGHT(RC[-5],2))"
Selection.Copy
Range("Z2:Z" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("U2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("Z2:Z" & var1).Select
Selection.ClearContents
Range("U2:U" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'???
Range("V1").Select
ActiveCell.FormulaR1C1 = "???"
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-4],4),LEFT(RIGHT(RC[-4],4),2),RIGHT(RC[-4],2))"
Selection.Copy
Range("Z2:Z" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("V2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("Z2:Z" & var1).Select
Selection.ClearContents
Range("V2:V" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Mise en forme finale
Range("A1:V1").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("A1:V" & var1).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("J2:V" & var1).Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1:V" & var1).Select
Selection.AutoFilter
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
Sub AFACC()
Dim var1 'nombre de ligne
'Préparation
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.ClearContents
Range("A2").Select
Selection.End(xlDown).Select
Selection.ClearContents
var1 = ActiveCell.Row - 1
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1))
Cells.Select
Cells.EntireColumn.AutoFit
'Période
Range("A1").Select
ActiveCell.FormulaR1C1 = "Période"
Range("W2").Select
ActiveCell.FormulaR1C1 = "=DATE(LEFT(RC[-22],4),RIGHT(RC[-22],2),1)"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.NumberFormat = "mmmm-yy"
Range("W2:W" & var1).Select
Selection.ClearContents
'ZET
Range("B1").Select
ActiveCell.FormulaR1C1 = "ZET"
'PDLA
Range("C1").Select
ActiveCell.FormulaR1C1 = "PDL"
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEFT(RC[-20],1)=""4"",LEFT(RC[-20],1)=""5"",LEFT(RC[-20],1)=""6"",LEFT(RC[-20],1)=""7""),""0""&FIXED(RC[-20],0,1),RC[-20])"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("W2:W" & var1).Select
Selection.ClearContents
'suppression colonne D
Columns("D").Select
Selection.Delete Shift:=xlToLeft
'Tarif
Range("D1").Select
ActiveCell.FormulaR1C1 = "Tarif"
'n° du PCE
Range("E1").Select
ActiveCell.FormulaR1C1 = "PCE"
Range("E2:E" & var1).Select
Selection.NumberFormat = "@"
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[-18],2)=""GI"",RC[-18],IF(LEN(RC[-18])=13,""0""&RC[-18],FIXED(RC[-18],0,1)))"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("E2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("W2:W" & var1).Select
Selection.ClearContents
'Fréq
Range("F1").Select
ActiveCell.FormulaR1C1 = "Fréq"
'Date début
Range("G1").Select
ActiveCell.FormulaR1C1 = "Date début"
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-16],4),LEFT(RIGHT(RC[-16],4),2),RIGHT(RC[-16],2))"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("G2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("W2:W" & var1).Select
Selection.ClearContents
Range("G2:G" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Date fin
Range("H1").Select
ActiveCell.FormulaR1C1 = "Date fin"
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-15],4),LEFT(RIGHT(RC[-15],4),2),RIGHT(RC[-15],2))"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("H2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("W2:W" & var1).Select
Selection.ClearContents
Range("H2:H" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Nombre de journée gazière (nb JG)
Range("I1").Select
ActiveCell.FormulaR1C1 = "nb JG"
'Ancien index
Range("J1").Select
ActiveCell.FormulaR1C1 = "Ancien index"
Range("J2:J" & var1).Select
Selection.NumberFormat = "#,##0"
'Nouvel index
Range("K1").Select
ActiveCell.FormulaR1C1 = "Nouvel index"
Range("K2:K" & var1).Select
Selection.NumberFormat = "#,##0"
'Index converti début MM
Range("L1").Select
ActiveCell.FormulaR1C1 = "index converti début (MM)"
Range("L2:L" & var1).Select
Selection.NumberFormat = "#,##0"
'Index converti début MM
Range("M1").Select
ActiveCell.FormulaR1C1 = "index converti fin (MM)"
Range("M2:M" & var1).Select
Selection.NumberFormat = "#,##0"
'Type de relevé
Range("N1").Select
ActiveCell.FormulaR1C1 = "Type de relevé"
'Date
Range("O1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-8],4),LEFT(RIGHT(RC[-8],4),2),RIGHT(RC[-8],2))"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("O2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("W2:W" & var1).Select
Selection.ClearContents
Range("O2:O" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Volume brut
Range("P1").Select
ActiveCell.FormulaR1C1 = "Volume brut"
Range("P2" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0"
'Volume converti
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Volume converti"
Range("Q2:Q" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0"
'PCS
Range("R1").Select
ActiveCell.FormulaR1C1 = "PCS"
'ActiveCell.FormulaR1C1 = _
' "=LEFT(RC[-5],SEARCH(""."",RC[-5],1)-1)&"",""&RIGHT(RC[-5],LEN(RC[-5])-SEARCH(""."",RC[-5],1))"
Range("R2:R" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'PTA-PTZ
Range("S1").Select
ActiveCell.FormulaR1C1 = "PTA-PTZ"
Range("S2:S" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'Energie (MWh)
Range("T1").Select
ActiveCell.FormulaR1C1 = "Energie (MWh)"
Range("T2:T" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'Mise en forme finale
Range("A1:T1").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("A1:T" & var1).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("P2:T" & var1).Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1:T" & var1).Select
Selection.AutoFilter
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
Sub AFACD()
Dim var1 'nombre de ligne
'Préparation
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.ClearContents
Range("A2").Select
Selection.End(xlDown).Select
Selection.ClearContents
var1 = ActiveCell.Row - 1
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
))
Cells.Select
Cells.EntireColumn.AutoFit
'Période
Range("A1").Select
ActiveCell.FormulaR1C1 = "Période"
Range("W2").Select
ActiveCell.FormulaR1C1 = "=DATE(LEFT(RC[-22],4),RIGHT(RC[-22],2),1)"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.NumberFormat = "mmmm-yy"
Range("W2:W" & var1).Select
Selection.ClearContents
'ZET
Range("B1").Select
ActiveCell.FormulaR1C1 = "ZET"
'PDLA
Range("C1").Select
ActiveCell.FormulaR1C1 = "PDL"
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEFT(RC[-20],1)=""4"",LEFT(RC[-20],1)=""5"",LEFT(RC[-20],1)=""6"",LEFT(RC[-20],1)=""7""),""0""&FIXED(RC[-20],0,1),RC[-20])"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("W2:W" & var1).Select
Selection.ClearContents
'suppression colonne D
Columns("D").Select
Selection.Delete Shift:=xlToLeft
'Tarif
Range("D1").Select
ActiveCell.FormulaR1C1 = "Tarif"
'Date début
Range("E1").Select
ActiveCell.FormulaR1C1 = "Date début"
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-10],4),LEFT(RIGHT(RC[-10],4),2),RIGHT(RC[-10],2))"
Selection.Copy
Range("O2:O" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("E2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("O2:O" & var1).Select
Selection.ClearContents
Range("E2:E" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Date fin
Range("F1").Select
ActiveCell.FormulaR1C1 = "Date fin"
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-9],4),LEFT(RIGHT(RC[-9],4),2),RIGHT(RC[-9],2))"
Selection.Copy
Range("O2:O" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("F2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("O2:O" & var1).Select
Selection.ClearContents
Range("F2:F" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Journée gazière (JG)
Range("G1").Select
ActiveCell.FormulaR1C1 = "JG"
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]="""","""",DATE(LEFT(RC[-8],4),LEFT(RIGHT(RC[-8],4),2),RIGHT(RC[-8],2)))"
Selection.Copy
Range("O2:O" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("G2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("O2:O" & var1).Select
Selection.ClearContents
Range("G2:G" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Energie mesurée(MWh)
Range("H1").Select
ActiveCell.FormulaR1C1 = "Energie mesurée(MWh)"
Range("H2:H" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'Energie M+1(MWh)
Range("I1").Select
ActiveCell.FormulaR1C1 = "Energie M+1(MWh)"
Range("I2:I" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'CED(MWh)
Range("J1").Select
ActiveCell.FormulaR1C1 = "CED(MWh)"
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RIGHT(RC[-5],1)=""-"",""-""&LEFT(RC[-5],LEN(RC[-5])-1),RC[-5])"
Selection.Copy
Range("O2:O" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("J2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("O2:O" & var1).Select
Selection.ClearContents
Range("J2:J" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'Prix unitaire(MWh)
Range("K1").Select
ActiveCell.FormulaR1C1 = "Prix(€/MWh)"
Range("K2:K" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'CED calculé (€)
Range("L1").Select
ActiveCell.FormulaR1C1 = "CED calculé (€)"
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=RC[-1]*RC[-2]"
Selection.Copy
Range("L2:L" & var1).Select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.NumberFormat = "#,##0.00"
'Mise en forme finale
Range("A1:L1").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("A1:L" & var1).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("H2:L" & var1).Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1:L" & var1).Select
Selection.AutoFilter
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
Sub AFACE()
Dim var1 'nombre de ligne
'Préparation
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.ClearContents
Range("A2").Select
Selection.End(xlDown).Select
Selection.ClearContents
var1 = ActiveCell.Row - 1
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
))
Cells.Select
Cells.EntireColumn.AutoFit
'Période
Range("A1").Select
ActiveCell.FormulaR1C1 = "Période"
Range("W2").Select
ActiveCell.FormulaR1C1 = "=DATE(LEFT(RC[-22],4),RIGHT(RC[-22],2),1)"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.NumberFormat = "mmmm-yy"
Range("W2:W" & var1).Select
Selection.ClearContents
'ZET
Range("B1").Select
ActiveCell.FormulaR1C1 = "ZET"
'PDLA
Range("C1").Select
ActiveCell.FormulaR1C1 = "PDL"
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEFT(RC[-20],1)=""4"",LEFT(RC[-20],1)=""5"",LEFT(RC[-20],1)=""6"",LEFT(RC[-20],1)=""7""),""0""&FIXED(RC[-20],0,1),RC[-20])"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("W2:W" & var1).Select
Selection.ClearContents
'suppression colonne D
Columns("D").Select
Selection.Delete Shift:=xlToLeft
'Tarif
Range("D1").Select
ActiveCell.FormulaR1C1 = "Tarif"
'multi-PCE
Range("E1").Select
ActiveCell.FormulaR1C1 = "PDL multi-PCE"
'Date de la CJA
Range("F1").Select
ActiveCell.FormulaR1C1 = "Date de la CJA"
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-9],4),LEFT(RIGHT(RC[-9],4),2),RIGHT(RC[-9],2))"
Selection.Copy
Range("O2:O" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("F2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("O2:O" & var1).Select
Selection.ClearContents
Range("F2:F" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Energie au PDLA (MWh/jour)
Range("G1").Select
ActiveCell.FormulaR1C1 = "Energie au PDLA (MWh/jour)"
Range("O2").Select
Range("G2:G" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'CJAj
Range("H1").Select
ActiveCell.FormulaR1C1 = "CJAj (MWh/jour)"
Range("H2:H" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'CJAm
Range("I1").Select
ActiveCell.FormulaR1C1 = "CJAm (MWh/jour)"
Range("I2:I" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'CJAa
Range("J1").Select
ActiveCell.FormulaR1C1 = "CJAa (MWh/jour)"
Range("J2:J" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'Dépassement
Range("K1").Select
ActiveCell.FormulaR1C1 = "Dépassement"
Range("K2:K" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'suppression colonne L
Columns("L:L").Select
Selection.Delete Shift:=xlToLeft
'Mise en forme finale
Range("A1:K1").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("A1:K" & var1).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("G2:K" & var1).Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1:K" & var1).Select
Selection.AutoFilter
'Selection.AutoFilter Field:=11, Criteria1:="<>0", Operator:=xlAnd
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Selection.End(xlToRight).Select
End Sub
j'utilise une Macro qui met en forme un fichier selon qu'il est du type A, B, C,
D, ou E. Précédemment elle fonctionnait sous 2000, mais depuis que je suis
passé sous 2007, j'ai des problèmes de #N/A ou #VALEURS dans certaines
colonnes des fichiers A et E.
Je vous joindrai les fichiers par mail, car trop volumineux, en attendant merci
d'avance pour vos réponses, voici le code de la macro :
Public var2
Sub AFAC()
varp2 = Now()
Application.ScreenUpdating = False
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(LEFT(RC[-1],11),1)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
var2 = ActiveCell.Value
If var2 = "A" Then
Call AFACA
Else
If var2 = "B" Then
Call AFACB
Else
If var2 = "C" Then
Call AFACC
Else
If var2 = "D" Then
Call AFACD
Else
If var2 = "E" Then
Call AFACE
Else
Application.ScreenUpdating = True
varp3 = Now()
MsgBox "Ce n'est pas un fichier AFAC A B C D ou E !!!!! (Temps de traitement = " & Format(varp3 - varp2, "HH:MM:SS") & ")", vbInformation, "Finish"
Exit Sub
End If
End If
End If
End If
End If
Application.ScreenUpdating = True
varp3 = Now()
MsgBox "Mise en forme de l'AFAC " & var2 & " terminée ! (Temps de traitement = " & Format(varp3 - varp2, "HH:MM:SS") & ")", vbInformation, "Finish"
End Sub
Sub AFACA()
Dim var1 'nombre de ligne
'Préparation
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.ClearContents
Range("A2").Select
Selection.End(xlDown).Select
Selection.ClearContents
var1 = ActiveCell.Row - 1
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1))
Cells.Select
Cells.EntireColumn.AutoFit
'Période
Range("A1").Select
ActiveCell.FormulaR1C1 = "Période"
Range("U2").Select
ActiveCell.FormulaR1C1 = "=DATE(LEFT(RC[-20],4),RIGHT(RC[-20],2),1)"
Selection.Copy
Range("U2:U" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.NumberFormat = "mmmm-yy"
Range("U2:U" & var1).Select
Selection.ClearContents
'ZET
Range("B1").Select
ActiveCell.FormulaR1C1 = "ZET"
'PDLA
Range("C1").Select
ActiveCell.FormulaR1C1 = "PDL"
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEFT(RC[-18],1)=""4"",LEFT(RC[-18],1)=""5"",LEFT(RC[-18],1)=""6"",LEFT(RC[-18],1)=""7""),""0""&FIXED(RC[-18],0,1),RC[-18])"
Selection.Copy
Range("U2:U" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("U2:U" & var1).Select
Selection.ClearContents
'suppression colonne D
Columns("D
").SelectSelection.Delete Shift:=xlToLeft
'Tarif
Range("D1").Select
ActiveCell.FormulaR1C1 = "Tarif"
'Fréq
Range("E1").Select
ActiveCell.FormulaR1C1 = "Fréq"
'multi-PCE
Range("F1").Select
ActiveCell.FormulaR1C1 = "PDL multi-PCE"
'fact mois anté
Range("G1").Select
ActiveCell.FormulaR1C1 = "Fact mois anté"
'Ajust manuel
Range("H1").Select
ActiveCell.FormulaR1C1 = "Ajust manuel"
'Annul fact
Range("I1").Select
ActiveCell.FormulaR1C1 = "Annul Fact"
'Date début
Range("J1").Select
ActiveCell.FormulaR1C1 = "Date début"
Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-10],4),LEFT(RIGHT(RC[-10],4),2),RIGHT(RC[-10],2))"
Selection.Copy
Range("T2:T" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("J2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("T2:T" & var1).Select
Selection.ClearContents
Range("J2:J" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Date fin
Range("K1").Select
ActiveCell.FormulaR1C1 = "Date fin"
Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-9],4),LEFT(RIGHT(RC[-9],4),2),RIGHT(RC[-9],2))"
Selection.Copy
Range("T2:T" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("K2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("T2:T" & var1).Select
Selection.ClearContents
Range("K2:K" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'code agrégé
Range("L1").Select
ActiveCell.FormulaR1C1 = "code agrégé"
'code ind
Range("M1").Select
ActiveCell.FormulaR1C1 = "code ind"
'justif ajust manuel
Range("N1").Select
ActiveCell.FormulaR1C1 = "justif ajust manuel"
'Quantité
Range("O1").Select
ActiveCell.FormulaR1C1 = "Quantité"
Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RIGHT(RC[-5],1)=""-"",""-""&LEFT(RC[-5],LEN(RC[-5])-1),RC[-5])"
Selection.Copy
Range("T2:T" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("O2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("T2:T" & var1).Select
Selection.ClearContents
Range("O2:O" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'Prorata temporis
Range("P1").Select
ActiveCell.FormulaR1C1 = "Prorata temporis"
Range("P2
" & var1).SelectFor Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.00"
'Prix unitaire
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Prix unitaire"
Range("Q2:Q" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.00"
'Montant
Range("R1").Select
ActiveCell.FormulaR1C1 = "Montant"
Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RIGHT(RC[-2],1)=""-"",""-""&LEFT(RC[-2],LEN(RC[-2])-1),RC[-2])"
Selection.Copy
Range("T2:T" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("R2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("T2:T" & var1).Select
Selection.ClearContents
Range("R2:R" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.00"
'TVA
Range("S1").Select
ActiveCell.FormulaR1C1 = "TVA"
Range("S2:S" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=RC[-1]/100"
Selection.Copy
Range("T2:T" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("S2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("T2:T" & var1).Select
Selection.ClearContents
Range("S2:S" & var1).Select
Selection.NumberFormat = "0.0%"
'Mise en forme finale
Range("A1:S1").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("A1:S" & var1).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("O2:S" & var1).Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1:S" & var1).Select
Selection.AutoFilter
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
Sub AFACB()
Dim var1 'nombre de ligne
'Préparation
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.ClearContents
Range("A2").Select
Selection.End(xlDown).Select
Selection.ClearContents
var1 = ActiveCell.Row - 1
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1))
Cells.Select
Cells.EntireColumn.AutoFit
'Période
Range("A1").Select
ActiveCell.FormulaR1C1 = "Période"
Range("Z2").Select
ActiveCell.FormulaR1C1 = "=DATE(LEFT(RC[-25],4),RIGHT(RC[-25],2),1)"
Selection.Copy
Range("Z2:Z" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.NumberFormat = "mmmm-yy"
Range("Z2:Z" & var1).Select
Selection.ClearContents
'ZET
Range("B1").Select
ActiveCell.FormulaR1C1 = "ZET"
'PDLA
Range("C1").Select
ActiveCell.FormulaR1C1 = "PDL"
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEFT(RC[-23],1)=""4"",LEFT(RC[-23],1)=""5"",LEFT(RC[-23],1)=""6"",LEFT(RC[-23],1)=""7""),""0""&FIXED(RC[-23],0,1),RC[-23])"
Selection.Copy
Range("Z2:Z" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("Z2:Z" & var1).Select
Selection.ClearContents
'suppression colonne D
Columns("D
").SelectSelection.Delete Shift:=xlToLeft
'Tarif
Range("D1").Select
ActiveCell.FormulaR1C1 = "Tarif"
'n° du PCE
Range("E1").Select
ActiveCell.FormulaR1C1 = "PCE"
Range("E2:E" & var1).Select
Selection.NumberFormat = "@"
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[-21],2)=""GI"",RC[-21],IF(LEN(RC[-21])=13,""0""&RC[-21],FIXED(RC[-21],0,1)))"
Selection.Copy
Range("Z2:Z" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("E2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("Z2:Z" & var1).Select
Selection.ClearContents
'Fréq
Range("F1").Select
ActiveCell.FormulaR1C1 = "Fréq"
'Raison du relevé
Range("G1").Select
ActiveCell.FormulaR1C1 = "Raison du relevé"
'Date début
Range("H1").Select
ActiveCell.FormulaR1C1 = "Date début"
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-18],4),LEFT(RIGHT(RC[-18],4),2),RIGHT(RC[-18],2))"
Selection.Copy
Range("Z2:Z" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("H2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("Z2:Z" & var1).Select
Selection.ClearContents
Range("H2:H" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Date fin
Range("I1").Select
ActiveCell.FormulaR1C1 = "Date fin"
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-17],4),LEFT(RIGHT(RC[-17],4),2),RIGHT(RC[-17],2))"
Selection.Copy
Range("Z2:Z" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("I2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("Z2:Z" & var1).Select
Selection.ClearContents
Range("I2:I" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Ancien index
Range("J1").Select
ActiveCell.FormulaR1C1 = "Ancien index"
Range("J2:J" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0"
'Nouvel index
Range("K1").Select
ActiveCell.FormulaR1C1 = "Nouvel index"
Range("K2:K" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0"
'Volume brut
Range("L1").Select
ActiveCell.FormulaR1C1 = "Volume brut"
Range("L2:L" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0"
'Ancien index converti
Range("M1").Select
ActiveCell.FormulaR1C1 = "Ancien index converti"
Range("M2:M" & var1).Select
Selection.NumberFormat = "#,##0"
'Nouvel index converti
Range("N1").Select
ActiveCell.FormulaR1C1 = "Nouvel index converti"
Range("N2:N" & var1).Select
Selection.NumberFormat = "#,##0"
'Volume converti
Range("O1").Select
ActiveCell.FormulaR1C1 = "Volume converti"
Range("O2:O" & var1).Select
Selection.NumberFormat = "#,##0"
'Energie (MWh)
Range("P1").Select
ActiveCell.FormulaR1C1 = "Energie (MWh)"
Range("P2
" & var1).SelectFor Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'Coefficient thermique
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Coef. therm."
Range("Q2:Q" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'PTA
Range("R1").Select
ActiveCell.FormulaR1C1 = "PTA"
Range("R2:R" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'PCS
Range("S1").Select
ActiveCell.FormulaR1C1 = "PCS"
Range("S2:S" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'???
Range("T1").Select
ActiveCell.FormulaR1C1 = "???"
'???
Range("U1").Select
ActiveCell.FormulaR1C1 = "???"
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-5],4),LEFT(RIGHT(RC[-5],4),2),RIGHT(RC[-5],2))"
Selection.Copy
Range("Z2:Z" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("U2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("Z2:Z" & var1).Select
Selection.ClearContents
Range("U2:U" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'???
Range("V1").Select
ActiveCell.FormulaR1C1 = "???"
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-4],4),LEFT(RIGHT(RC[-4],4),2),RIGHT(RC[-4],2))"
Selection.Copy
Range("Z2:Z" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("V2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("Z2:Z" & var1).Select
Selection.ClearContents
Range("V2:V" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Mise en forme finale
Range("A1:V1").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("A1:V" & var1).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("J2:V" & var1).Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1:V" & var1).Select
Selection.AutoFilter
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
Sub AFACC()
Dim var1 'nombre de ligne
'Préparation
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.ClearContents
Range("A2").Select
Selection.End(xlDown).Select
Selection.ClearContents
var1 = ActiveCell.Row - 1
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1))
Cells.Select
Cells.EntireColumn.AutoFit
'Période
Range("A1").Select
ActiveCell.FormulaR1C1 = "Période"
Range("W2").Select
ActiveCell.FormulaR1C1 = "=DATE(LEFT(RC[-22],4),RIGHT(RC[-22],2),1)"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.NumberFormat = "mmmm-yy"
Range("W2:W" & var1).Select
Selection.ClearContents
'ZET
Range("B1").Select
ActiveCell.FormulaR1C1 = "ZET"
'PDLA
Range("C1").Select
ActiveCell.FormulaR1C1 = "PDL"
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEFT(RC[-20],1)=""4"",LEFT(RC[-20],1)=""5"",LEFT(RC[-20],1)=""6"",LEFT(RC[-20],1)=""7""),""0""&FIXED(RC[-20],0,1),RC[-20])"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("W2:W" & var1).Select
Selection.ClearContents
'suppression colonne D
Columns("D
").SelectSelection.Delete Shift:=xlToLeft
'Tarif
Range("D1").Select
ActiveCell.FormulaR1C1 = "Tarif"
'n° du PCE
Range("E1").Select
ActiveCell.FormulaR1C1 = "PCE"
Range("E2:E" & var1).Select
Selection.NumberFormat = "@"
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[-18],2)=""GI"",RC[-18],IF(LEN(RC[-18])=13,""0""&RC[-18],FIXED(RC[-18],0,1)))"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("E2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("W2:W" & var1).Select
Selection.ClearContents
'Fréq
Range("F1").Select
ActiveCell.FormulaR1C1 = "Fréq"
'Date début
Range("G1").Select
ActiveCell.FormulaR1C1 = "Date début"
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-16],4),LEFT(RIGHT(RC[-16],4),2),RIGHT(RC[-16],2))"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("G2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("W2:W" & var1).Select
Selection.ClearContents
Range("G2:G" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Date fin
Range("H1").Select
ActiveCell.FormulaR1C1 = "Date fin"
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-15],4),LEFT(RIGHT(RC[-15],4),2),RIGHT(RC[-15],2))"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("H2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("W2:W" & var1).Select
Selection.ClearContents
Range("H2:H" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Nombre de journée gazière (nb JG)
Range("I1").Select
ActiveCell.FormulaR1C1 = "nb JG"
'Ancien index
Range("J1").Select
ActiveCell.FormulaR1C1 = "Ancien index"
Range("J2:J" & var1).Select
Selection.NumberFormat = "#,##0"
'Nouvel index
Range("K1").Select
ActiveCell.FormulaR1C1 = "Nouvel index"
Range("K2:K" & var1).Select
Selection.NumberFormat = "#,##0"
'Index converti début MM
Range("L1").Select
ActiveCell.FormulaR1C1 = "index converti début (MM)"
Range("L2:L" & var1).Select
Selection.NumberFormat = "#,##0"
'Index converti début MM
Range("M1").Select
ActiveCell.FormulaR1C1 = "index converti fin (MM)"
Range("M2:M" & var1).Select
Selection.NumberFormat = "#,##0"
'Type de relevé
Range("N1").Select
ActiveCell.FormulaR1C1 = "Type de relevé"
'Date
Range("O1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-8],4),LEFT(RIGHT(RC[-8],4),2),RIGHT(RC[-8],2))"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("O2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("W2:W" & var1).Select
Selection.ClearContents
Range("O2:O" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Volume brut
Range("P1").Select
ActiveCell.FormulaR1C1 = "Volume brut"
Range("P2
" & var1).SelectFor Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0"
'Volume converti
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Volume converti"
Range("Q2:Q" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0"
'PCS
Range("R1").Select
ActiveCell.FormulaR1C1 = "PCS"
'ActiveCell.FormulaR1C1 = _
' "=LEFT(RC[-5],SEARCH(""."",RC[-5],1)-1)&"",""&RIGHT(RC[-5],LEN(RC[-5])-SEARCH(""."",RC[-5],1))"
Range("R2:R" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'PTA-PTZ
Range("S1").Select
ActiveCell.FormulaR1C1 = "PTA-PTZ"
Range("S2:S" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'Energie (MWh)
Range("T1").Select
ActiveCell.FormulaR1C1 = "Energie (MWh)"
Range("T2:T" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'Mise en forme finale
Range("A1:T1").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("A1:T" & var1).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("P2:T" & var1).Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1:T" & var1).Select
Selection.AutoFilter
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
Sub AFACD()
Dim var1 'nombre de ligne
'Préparation
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.ClearContents
Range("A2").Select
Selection.End(xlDown).Select
Selection.ClearContents
var1 = ActiveCell.Row - 1
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
))
Cells.Select
Cells.EntireColumn.AutoFit
'Période
Range("A1").Select
ActiveCell.FormulaR1C1 = "Période"
Range("W2").Select
ActiveCell.FormulaR1C1 = "=DATE(LEFT(RC[-22],4),RIGHT(RC[-22],2),1)"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.NumberFormat = "mmmm-yy"
Range("W2:W" & var1).Select
Selection.ClearContents
'ZET
Range("B1").Select
ActiveCell.FormulaR1C1 = "ZET"
'PDLA
Range("C1").Select
ActiveCell.FormulaR1C1 = "PDL"
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEFT(RC[-20],1)=""4"",LEFT(RC[-20],1)=""5"",LEFT(RC[-20],1)=""6"",LEFT(RC[-20],1)=""7""),""0""&FIXED(RC[-20],0,1),RC[-20])"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("W2:W" & var1).Select
Selection.ClearContents
'suppression colonne D
Columns("D
").SelectSelection.Delete Shift:=xlToLeft
'Tarif
Range("D1").Select
ActiveCell.FormulaR1C1 = "Tarif"
'Date début
Range("E1").Select
ActiveCell.FormulaR1C1 = "Date début"
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-10],4),LEFT(RIGHT(RC[-10],4),2),RIGHT(RC[-10],2))"
Selection.Copy
Range("O2:O" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("E2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("O2:O" & var1).Select
Selection.ClearContents
Range("E2:E" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Date fin
Range("F1").Select
ActiveCell.FormulaR1C1 = "Date fin"
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-9],4),LEFT(RIGHT(RC[-9],4),2),RIGHT(RC[-9],2))"
Selection.Copy
Range("O2:O" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("F2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("O2:O" & var1).Select
Selection.ClearContents
Range("F2:F" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Journée gazière (JG)
Range("G1").Select
ActiveCell.FormulaR1C1 = "JG"
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]="""","""",DATE(LEFT(RC[-8],4),LEFT(RIGHT(RC[-8],4),2),RIGHT(RC[-8],2)))"
Selection.Copy
Range("O2:O" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("G2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("O2:O" & var1).Select
Selection.ClearContents
Range("G2:G" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Energie mesurée(MWh)
Range("H1").Select
ActiveCell.FormulaR1C1 = "Energie mesurée(MWh)"
Range("H2:H" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'Energie M+1(MWh)
Range("I1").Select
ActiveCell.FormulaR1C1 = "Energie M+1(MWh)"
Range("I2:I" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'CED(MWh)
Range("J1").Select
ActiveCell.FormulaR1C1 = "CED(MWh)"
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RIGHT(RC[-5],1)=""-"",""-""&LEFT(RC[-5],LEN(RC[-5])-1),RC[-5])"
Selection.Copy
Range("O2:O" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("J2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("O2:O" & var1).Select
Selection.ClearContents
Range("J2:J" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'Prix unitaire(MWh)
Range("K1").Select
ActiveCell.FormulaR1C1 = "Prix(€/MWh)"
Range("K2:K" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'CED calculé (€)
Range("L1").Select
ActiveCell.FormulaR1C1 = "CED calculé (€)"
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=RC[-1]*RC[-2]"
Selection.Copy
Range("L2:L" & var1).Select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.NumberFormat = "#,##0.00"
'Mise en forme finale
Range("A1:L1").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("A1:L" & var1).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("H2:L" & var1).Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1:L" & var1).Select
Selection.AutoFilter
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
Sub AFACE()
Dim var1 'nombre de ligne
'Préparation
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.ClearContents
Range("A2").Select
Selection.End(xlDown).Select
Selection.ClearContents
var1 = ActiveCell.Row - 1
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
))
Cells.Select
Cells.EntireColumn.AutoFit
'Période
Range("A1").Select
ActiveCell.FormulaR1C1 = "Période"
Range("W2").Select
ActiveCell.FormulaR1C1 = "=DATE(LEFT(RC[-22],4),RIGHT(RC[-22],2),1)"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.NumberFormat = "mmmm-yy"
Range("W2:W" & var1).Select
Selection.ClearContents
'ZET
Range("B1").Select
ActiveCell.FormulaR1C1 = "ZET"
'PDLA
Range("C1").Select
ActiveCell.FormulaR1C1 = "PDL"
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEFT(RC[-20],1)=""4"",LEFT(RC[-20],1)=""5"",LEFT(RC[-20],1)=""6"",LEFT(RC[-20],1)=""7""),""0""&FIXED(RC[-20],0,1),RC[-20])"
Selection.Copy
Range("W2:W" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("W2:W" & var1).Select
Selection.ClearContents
'suppression colonne D
Columns("D
").SelectSelection.Delete Shift:=xlToLeft
'Tarif
Range("D1").Select
ActiveCell.FormulaR1C1 = "Tarif"
'multi-PCE
Range("E1").Select
ActiveCell.FormulaR1C1 = "PDL multi-PCE"
'Date de la CJA
Range("F1").Select
ActiveCell.FormulaR1C1 = "Date de la CJA"
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-9],4),LEFT(RIGHT(RC[-9],4),2),RIGHT(RC[-9],2))"
Selection.Copy
Range("O2:O" & var1).Select
ActiveSheet.Paste
Selection.Copy
Range("F2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("O2:O" & var1).Select
Selection.ClearContents
Range("F2:F" & var1).Select
Selection.NumberFormat = "d-mmm-yy"
'Energie au PDLA (MWh/jour)
Range("G1").Select
ActiveCell.FormulaR1C1 = "Energie au PDLA (MWh/jour)"
Range("O2").Select
Range("G2:G" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'CJAj
Range("H1").Select
ActiveCell.FormulaR1C1 = "CJAj (MWh/jour)"
Range("H2:H" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'CJAm
Range("I1").Select
ActiveCell.FormulaR1C1 = "CJAm (MWh/jour)"
Range("I2:I" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'CJAa
Range("J1").Select
ActiveCell.FormulaR1C1 = "CJAa (MWh/jour)"
Range("J2:J" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'Dépassement
Range("K1").Select
ActiveCell.FormulaR1C1 = "Dépassement"
Range("K2:K" & var1).Select
For Each cel In Selection.Cells
cel.Formula = cel.Formula
Next
Selection.NumberFormat = "#,##0.000"
'suppression colonne L
Columns("L:L").Select
Selection.Delete Shift:=xlToLeft
'Mise en forme finale
Range("A1:K1").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("A1:K" & var1).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("G2:K" & var1).Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1:K" & var1).Select
Selection.AutoFilter
'Selection.AutoFilter Field:=11, Criteria1:="<>0", Operator:=xlAnd
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Selection.End(xlToRight).Select
End Sub
