Rechercher la dernière occurrence d'une valeur dans les cellules d'une colonne

**Kestion100** · 06/11/2015, 15h46

Bonjour,
Je souhaiterais imprimer une zone allant de la cellule Ax à la cellule Dy

Avec x = le numéro de ligne de la première occurrence du texte "201" dans la colonne C

y = le numéro de ligne de la dernière occurrence du texte "214" dans la colonne C

Avec mon code, aucun problème pour Ax, en revanche, j'arrive toujours à:

y = le numéro de ligne de la première occurrence du texte "214" dans la colonne C

Le code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Sub PRINTSEL()
'
' PRINTSEL Macro
'
 
'
 
 
    Dim ZONEP As Long, i As Integer, Page As Long, Rep As Integer, RepPage As Long, DebutImp As Long, FinImp As Long
 
 
 
 
 
    Worksheets("REMISE BULLETINS FAMILLES").Activate
 
 
 
 
    With Sheets("REMISE BULLETINS FAMILLES").Range("C1:C2000")
 
 
 
     Dim xd As Range
Dim ligned As Integer
Dim colonned As Integer
 
Set xd = Sheets("REMISE BULLETINS FAMILLES").Cells.Find("201", , xlValues)
 
ligned = xd.Row
 
 
DebutImp = ligned
 
MsgBox DebutImp       
 
 
With Sheets("REMISE BULLETINS FAMILLES").Range("C1:C2000")
 
 
    Set C = Sheets("REMISE BULLETINS FAMILLES").Range("C2:C2000").Find("214", LookAt:=xlWhole, SearchOrder:=xlByColumns)
 
    If Not C Is Nothing Then
        Do
 
        Set C = .FindNext(C)
        C.Activate
 
 
 
        FinImp = C.Row
       Exit Do
       Loop While Not C Is Nothing
 
    End If
End With
 
 
MsgBox FinImp
 
 
 
 
 
 
 Range("" & "A" & DebutImp & ":" & "D" & FinImp & "").Select
 
 
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = "" & "A" & DebutImp & ":" & "D" & FinImp & ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "REMISE BULLETINS FAMILLES"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "&Z&F"
        .RightFooter = "Page &P"
        .LeftMargin = Application.InchesToPoints(0.196850393700787)
        .RightMargin = Application.InchesToPoints(0.196850393700787)
        .TopMargin = Application.InchesToPoints(0.275590551181102)
        .BottomMargin = Application.InchesToPoints(0.29)
        .HeaderMargin = Application.InchesToPoints(0.15748031496063)
        .FooterMargin = Application.InchesToPoints(0.15748031496063)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    Selection.PrintOut Copies:=1, Collate:=True
 
 
 
 
End Sub

Quelqu'un peut-il m'aider à modifier:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
With Sheets("REMISE BULLETINS FAMILLES").Range("C1:C2000")
 
 
    Set C = Sheets("REMISE BULLETINS FAMILLES").Range("C2:C2000").Find("214", LookAt:=xlWhole, SearchOrder:=xlByColumns)
 
    If Not C Is Nothing Then
        Do
 
        Set C = .FindNext(C)
        C.Activate
 
 
 
        FinImp = C.Row
       Exit Do
       Loop While Not C Is Nothing
 
    End If
End With

Pour attraper le numéro de ligne de la dernière occurrence du texte "214" dans la colonne C

MERCI BEAUCOUP D'AVANCE

**Kestion100** · 06/11/2015, 15h50

RESOLU AVEC:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Sub PRINTSEL()
'
' PRINTSEL Macro
'
 
'
 
 
    Dim ZONEP As Long, i As Integer, Page As Long, Rep As Integer, RepPage As Long, DebutImp As Long, FinImp As Long
 
 
 
 
 
    With Sheets("REMISE BULLETINS FAMILLES").Range("C1:C2000")
 
 
     Dim xd As Range
Dim ligned As Integer
Dim colonned As Integer
 
Set xd = Sheets("REMISE BULLETINS FAMILLES").Cells.Find("201", , xlValues)
 
ligned = xd.Row
 
 
DebutImp = ligned
 
MsgBox DebutImp                               
 
 
End With
 
With Sheets("REMISE BULLETINS FAMILLES").Range("C1:C2000")
 
 
    Set C = Sheets("REMISE BULLETINS FAMILLES").Range("C2:C2000").Find("214", LookAt:=xlWhole, SearchOrder:=xlByColumns)
 
    If Not C Is Nothing Then
        Do
 
        Set C = .FindPrevious(C)
        C.Activate
 
 
 
        FinImp = C.Row
       Exit Do
       Loop While Not C Is Nothing
 
    End If
End With
 
 
MsgBox FinImp
 
 
 
 
 Range("" & "A" & DebutImp & ":" & "D" & FinImp & "").Select
 
 
 
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = "" & "A" & DebutImp & ":" & "D" & FinImp & ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "REMISE BULLETINS FAMILLES"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "&Z&F"
        .RightFooter = "Page &P"
        .LeftMargin = Application.InchesToPoints(0.196850393700787)
        .RightMargin = Application.InchesToPoints(0.196850393700787)
        .TopMargin = Application.InchesToPoints(0.275590551181102)
        .BottomMargin = Application.InchesToPoints(0.29)
        .HeaderMargin = Application.InchesToPoints(0.15748031496063)
        .FooterMargin = Application.InchesToPoints(0.15748031496063)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    Selection.PrintOut Copies:=1, Collate:=True
 
 
 
 
End Sub

Rechercher la dernière occurrence d'une valeur dans les cellules d'une colonne

Macros et VBA Excel

Discussions similaires

Partager

Partager