1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
| Sub IPTT()
'
' IPTT Macro
' Macro enregistrée le 23/11/2012 par i0155970
'
'
With ThisWorkbook.Worksheets("Feuil1").Cells
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
For Each ws In ThisWorkbook.Worksheets
With ws.Range("G13:G5312")
.NumberFormat = "0"
.Value = .Value
End With
Next
ThisWorkbook.Sheets("Feuil1").Name = "CSMS"
With ThisWorkbook.Sheets("IP%1014")
.Range("M3:M999").FormulaR1C1 = "=VLOOKUP(RC4,CSMS!R13C7:R5312C31,4,0)"
.Range("N3:N999").FormulaR1C1 = "=VLOOKUP(RC4,CSMS!R13C7:R5312C31,5,0)"
.Range("O3:O999").FormulaR1C1 = "=VLOOKUP(RC4,CSMS!R13C7:R5312C31,6,0)"
.Range("P3:P999").FormulaR1C1 = "=VLOOKUP(RC4,CSMS!R13C7:R5312C31,7,0)"
.Range("P3:P999").NumberFormat = "m/d/yyyy"
.Range("V3:V999").FormulaR1C1 = "=IF(AND(VLOOKUP(RC4,CSMS!R13C7:R5312C31,11,0)=""N/A"",AND(VLOOKUP(RC4,CSMS!R13C7:R5312C31,20,0)=""yes"")),""Destroyed on site"",VLOOKUP(RC4,CSMS!R13C7:R5312C31,11,0))"
.Range("Y3:Y999").FormulaR1C1 = "=IF(VLOOKUP(RC4,CSMS!R13C7:R5312C31,12,0)=""N/A"",VLOOKUP(RC4,CSMS!R13C7:R5312C31,22,0),VLOOKUP(RC4,CSMS!R13C7:R5312C31,12,0))"
.Range("M3:Y999").Copy
.Range("M3:Y999").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
End Sub |
Partager