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
| Public u As Integer
Dim tabcompte()
Sub bilan()
derniereligne = Range("e65536").End(xlUp).Row
For Each cel In Range("e1:e" & derniereligne)
mLoop = 1
If Not cel = "" And IsNumeric(cel) And Not cel.Offset(mLoop, 2) = "**********" Then ' si les étoiles ne font pas partie du fichier brut
' remplacer par not isemptyCel.Offset(mLoop, 2)
mCompte = InputBox("N° de compte pour " & cel)
Do Until cel.Offset(mLoop, 2) = ""
j = j + 1
ReDim Preserve tabcompte(j)
tabcompte(j) = Array(mCompte, cel.Offset(mLoop, 2).Value, cel.Offset(mLoop, 4))
mLoop = mLoop + 1
Loop
End If
Next cel
chemin = ThisWorkbook.Path & "\"
nomcl = InputBox("NOM FICHIER", "FLASH", "", 5000, 5000)
Workbooks.Add
Range("a1").Select
For i = 1 To j
Zonedecriture = "b" & i & ":d" & i
Range(Zonedecriture) = tabcompte(i)
Cells(i, 1) = Cells(i, 2) & Cells(i, 3)
Next i
ActiveWorkbook.SaveAs Filename:= _
chemin & nomcl, FileFormat:=xlText _
, CreateBackup:=False
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = False
End Sub |
Partager