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
| Sub test()
ChDir "C:\Documents and Settings\formation\Bureau\ESSAI"
Workbooks.Open Filename:="C:\Documents and Settings\formation\Bureau\ESSAI\fichier2.xls"
Windows("fichier2.xls").Visible = False
End Sub
Sub test2()
ChDir "C:\Documents and Settings\formation\Bureau\ESSAI"
Workbooks.Open Filename:="C:\Documents and Settings\formation\Bureau\ESSAI\fichier3.xls"
Windows("fichier3.xls").Visible = False
End Sub
Sub essai()
Dim wsh1 As Worksheet, wsh2 As Worksheet, wsh3 As Worksheet
Dim plage As Range 'plage de cellules
Dim C As Range, F As Range 'cellule de boucle / cellule de recherche
Dim pMad As Range, pSad As Range, pCca As Range, pTra As Range 'plage d'écriture sur wsh1
Dim codeT As String, codeC As String
Set wsh1 = Workbooks("Fichier1.xls").Worksheets("Feuil1")
Set wsh2 = Workbooks("Fichier2.xls").Worksheets("Répartition par nature")
Set wsh3 = Workbooks("Fichier3.xls").Worksheets("Feuil1")
Set pMad = wsh1.Range("C3:C15") 'plage Libellé MAD
Set pSad = wsh1.Range("C19:C32") 'plage Libellé SAD
Set pCca = wsh1.Range("C35:C47") 'plage Libellé CCAS
'mémorisation des codes traitement (codeT) et charges (codeC)
With wsh3
Set plage = .Range("B2:B" & .Cells(Rows.Count, 2).End(xlUp).Row)
For Each C In plage
If C.Offset(0, 1) <> "" Then
codeT = codeT & C.Value
ElseIf C.Offset(0, 2) <> "" Then
codeC = codeC & C.Value
End If
Next
End With
Set plage = wsh2.Range("E3:E" & wsh2.Cells(Rows.Count, 5).End(xlUp).Row)
'boucle dans classeur2 colonne E
For Each C In plage
If C.Value <> "" Then ' si la cellule n'est pas vide
'Selection de CCA, SAD ou MAD suivant le chiffre
Select Case C.Offset(0, 1)
Case "01": Set pTra = pCca
Case "02": Set pTra = pSad
Case "03": Set pTra = pMad
End Select
'recherche du libelle dans la feuille 1
Set F = pTra.Find(C.Offset(0, -2), LookIn:=xlValues, lookat:=xlWhole)
wsh1.Cells(F.Row, 5) = wsh2.Cells(C.Row, 5) 'Ecriture MONTANT colonne 5
'definir si c'est un TRAIT ou CHARGE
If InStr(1, codeT, wsh2.Cells(C.Row, 4)) > 0 Then
wsh1.Cells(F.Row, 6) = wsh2.Cells(C.Row, 5) 'Ecriture TRAIT colonne 6
ElseIf InStr(1, codeC, wsh2.Cells(C.Row, 4)) > 0 Then
wsh1.Cells(F.Row, 7) = wsh2.Cells(C.Row, 5) 'Ecriture CHARGE colonne 7
End If
End If
Next
Set wsh1 = Nothing: Set wsh2 = Nothing: Set wsh3 = Nothing
Set plage = Nothing: Set C = Nothing: Set F = Nothing
Set pTra = Nothing: Set pCca = Nothing: Set pSad = Nothing: Set pMad = Nothing
End Sub |
Partager