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 44 45
| Sub comptabiliser()
Dim F1 As Worksheet
Dim F2 As Worksheet
Set F1 = Sheets("Feuil1")
Set F2 = Sheets("Compta")
Dim Tablo
F2.Cells.ClearContents
Dim i As Long
Application.ScreenUpdating = False
Tablo = F1.Range("A2", "Z" & F1.Range("A" & Rows.Count).End(xlUp).Row)
F2.Cells(1, 1).Resize(1, 8) = Array("Date", "code journal", "compte", "débit", "crédit", "Libellé pièce", "Pièce", "référence pièce")
Set d = CreateObject("Scripting.Dictionary")
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(Tablo)
clé = Format(CDate(Tablo(i, 2)), "m/d/yyyy") & "|" & "VE" & "|" & "70600000" & "|" & "" & "|" & Tablo(i, 10) & "" & "|" & Tablo(i, 6) & "|" & Tablo(i, 1) & "|" & Tablo(i, 9)
clé1 = Format(CDate(Tablo(i, 2)), "m/d/yyyy") & "|" & "VE" & "|" & "44571200" & "|" & "" & "|" & Tablo(i, 11) & "" & "|" & Tablo(i, 6) & "|" & Tablo(i, 1) & "|" & Tablo(i, 9)
clé2 = Format(CDate(Tablo(i, 2)), "m/d/yyyy") & "|" & "VE" & "|" & "411" & Tablo(1, 5) & "|" & Tablo(i, 12) & "" & "|" & "" & "|" & Tablo(i, 6) & "|" & Tablo(i, 1) & "|" & Tablo(i, 9)
d(clé) = d(clé)
d1(clé1) = d1(clé1)
d2(clé2) = d2(clé2)
Next i
F2.Range("A2").Resize(d.Count) = Application.Transpose(d.keys)
F2.Range("A2").Resize(d.Count).TextToColumns Other:=1, DataType:=xlDelimited, OtherChar:="|"
j = F2.Cells(Rows.Count, 1).End(xlUp).Row + 1
F2.Range("A" & j).Resize(d.Count) = Application.Transpose(d1.keys)
F2.Range("A" & j).Resize(d.Count).TextToColumns Other:=1, DataType:=xlDelimited, OtherChar:="|"
l = F2.Cells(Rows.Count, 1).End(xlUp).Row + 1
F2.Range("A" & l).Resize(d.Count) = Application.Transpose(d2.keys)
F2.Range("A" & l).Resize(d.Count).TextToColumns Other:=1, DataType:=xlDelimited, OtherChar:="|"
F2.Select
Call flitrer
Application.ScreenUpdating = True
End Sub
[ATTACH]619337[/ATTACH] |
Partager