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
|
Option Explicit
Sub Main()
Dim Code As String, Lgn As Long
'* Récupérer code a traiter
Code = [C30]
'* Ligne affichage
Lgn = 20
Call RécupererLesDonnées(Code, Lgn)
End Sub
Sub RécupererLesDonnées(Code As String, Lgn As Long)
Dim Cel As Range, MaPlage As Range, PlageMvts As Range
Dim FirstAddress As String
Dim TotalG As Integer, TotalH As Integer, Diff As Integer
Dim LastLig As Long, LastCol As Long
'*** Initialiser les variables
Set MaPlage = Range("C2:C" & [B2].End(xlDown).Row)
'*** Rechercher le code
Set Cel = MaPlage.Find(Code, LookIn:=xlValues)
If Not Cel Is Nothing Then
FirstAddress = Cel.Address
Do
Cells(Lgn, 12) = Cel.Value
Cells(Lgn, 13) = Cel.Offset(0, 4)
Cells(Lgn, 14) = Cel.Offset(0, 5)
Lgn = Lgn + 1
Set Cel = MaPlage.FindNext(Cel)
Loop While Not Cel Is Nothing And Cel.Address <> FirstAddress
End If
'* Calcul des Valeurs
LastLig = Cells(Rows.Count, 12).End(xlUp).Row
LastCol = Cells(20, Columns.Count).End(xlToLeft).Column
'* Plage des résultats
Set PlageMvts = Range(Cells(20, 12), Cells(LastLig, LastCol))
'* Total des colonnes
TotalG = Application.Sum(PlageMvts.Columns(2))
TotalH = Application.Sum(PlageMvts.Columns(3))
'* Affichage du résultat
Lgn = LastLig + 1
Cells(Lgn, 12) = "Total"
Cells(Lgn, 13) = TotalG
Cells(Lgn, 14) = TotalH
Diff = TotalG - TotalH
Lgn = Lgn + 1
Cells(Lgn, 12) = "Différence"
Cells(Lgn, 13) = Diff
'*** Libère la mémoire ------------------------------------------
Set MaPlage = Nothing: Set Cel = Nothing: Set PlageMvts = Nothing
End Sub |
Partager