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 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158
| Option Explicit
Public Function derligneColonne(sheetsBDD As String, by As String) As Long
Dim j As Long
j = 2
If by = "Ligne" Then
Do
j = j + 1
Loop While Sheets(sheetsBDD).Cells(j, 1).Value <> ""
Else
Do
j = j + 1
Loop While Sheets(sheetsBDD).Cells(1, j).Value <> ""
End If
derligneColonne = j - 1
End Function
Public Sub Copy(sheetsBDD As String, crit As String, sheetsToCopy As String)
Dim ligne As Long
Dim col As Long
Dim ligneToCopy As Long
Dim i As Long
Dim j As Long
col = derligneColonne(sheetsBDD, "col")
' Copier le titre
For j = 1 To col
Sheets(sheetsToCopy).Cells(1, j).Value = Sheets(sheetsBDD).Cells(2, j).Value
Next j
ligne = derligneColonne(sheetsBDD, "Ligne")
ligneToCopy = derligneColonne(sheetsToCopy, "Ligne") + 1
' Copier les valeurs
For i = 3 To ligne
If Sheets(sheetsBDD).Cells(i, 1).Value = crit Then
For j = 1 To col
Sheets(sheetsToCopy).Cells(ligneToCopy, j).Value = Sheets(sheetsBDD).Cells(i, j).Value
Next j
ligneToCopy = ligneToCopy + 1
End If
Next i
End Sub
Public Function VerifAppartenance(SheetsVerif As String, colonne As Long, critere As String, vectorbeg As Long, VectorLenght As Long) As Long
Dim i As Long
Dim result As Long
result = 0
For i = vectorbeg To VectorLenght
If Sheets(SheetsVerif).Cells(i, colonne).Value = critere Then
result = result + 1
Else
result = result + 0
End If
Next i
VerifAppartenance = result
End Function
Public Sub crit(sheetsBDD As String)
Dim i As Long
Dim derligne As Long
Dim derligneBDD As Long
Dim firstCrit As String
Dim verif As Long
Sheets.Add(After:=Worksheets(Worksheets.Count)).name = "Criteres"
Sheets("Criteres").Cells(1, 1).Value = "Criteres"
derligneBDD = derligneColonne(sheetsBDD, "Ligne")
firstCrit = Sheets(sheetsBDD).Cells(3, 1).Value
Sheets("Criteres").Cells(2, 1).Value = firstCrit
For i = 3 To derligneBDD
firstCrit = Sheets(sheetsBDD).Cells(i, 1).Value
derligne = derligneColonne("Criteres", "Ligne")
verif = VerifAppartenance("Criteres", 1, firstCrit, 2, derligne)
If verif > 0 Then
firstCrit = firstCrit
Else
Sheets("Criteres").Cells(derligne + 1, 1).Value = firstCrit
End If
Next i
End Sub
Sub CreateCopy(sheetsBDD As String)
Dim i As Long
Dim derligneCritere As Long
Dim critere As String
Dim name As String
Call crit(sheetsBDD)
derligneCritere = derligneColonne("Criteres", "Ligne")
For i = 2 To derligneCritere
critere = Sheets("Criteres").Cells(i, 1).Value
name = critere
Sheets.Add(After:=Worksheets(Worksheets.Count)).name = name
Call Copy(sheetsBDD, critere, name)
Next i
End Sub
Sub essai()
CreateCopy ("Transaction Listing")
'MsgBox VerifAppartenance("Feuil3", 1, "ZZ", 2, 10)
MsgBox "Termin? Grand Patron"
End Sub |
Partager