Déterminer un plage partielle dans une colonne

Bonjour,

En supposant que tes données se situent en colonne A,

'---------------------------------------------------------
Sub test1()
Dim Rg As Range
Dim LeMin As Long, LeMax As Long

With Feuil1
Set Rg = .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

With Rg
LeMin = Evaluate("Min(IF(" & .Address & "=""kr"",ROW(" & .Address &
")))")
LeMax = Evaluate("Max(IF(" & .Address & "=""kr"",ROW(" & .Address &
")))")
End With

With Worksheets("Feuil1")
MsgBox .Name & "!" & .Range(.Cells(LeMin, "A"), .Cells(LeMax,
"A")).Address
End With
End Sub
'---------------------------------------------------------

MichD

.

Même procédure, mais une meilleure formulation :

'------------------------------------------------------------
Sub test1()
Dim Rg As Range, Sh As Worksheet
Dim LeMin As Long, LeMax As Long

'Onglet feuille à adapter
Set Sh = Worksheets("Feuil1")

With Sh
'Plage de cellules à définir de tes données
Set Rg = .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

With Rg
LeMin = Evaluate("Min(IF(" & .Address & _
"=""kr"",ROW(" & .Address & ")))")
LeMax = Evaluate("Max(IF(" & .Address & _
"=""kr"",ROW(" & .Address & ")))")
End With

With Sh
MsgBox .Name & "!" & .Range(.Cells(LeMin, Rg.Column), _
.Cells(LeMax, Rg.Column)).Address
End With
End Sub
'------------------------------------------------------------

MichD

Et une dernière variante :

'----------------------------------------------------------------
Sub test1()
Dim Rg As Range, Sh As Worksheet
Dim LeMin As Long, LeMax As Long
Dim Mot As String

'Onglet feuille à adapter
Set Sh = Worksheets("Feuil1")

'Expression retenue
Mot = "Kr"

With Sh
Set Rg = .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

With Rg
LeMin = Evaluate("Min(IF(" & .Address & _
"=""" & Mot & """,ROW(" & .Address & ")))")
LeMax = Evaluate("Max(IF(" & .Address & _
"=""" & Mot & """,ROW(" & .Address & ")))")
End With

With Sh
MsgBox .Name & "!" & .Range(.Cells(LeMin, Rg.Column), _
.Cells(LeMax, Rg.Column)).Address
End With
End Sub
'----------------------------------------------------------------

MichD

"MichD" a écrit dans le message de groupe de discussion :
osbboi$gv7$1@gioia.aioe.org...

Même procédure, mais une meilleure formulation :

'------------------------------------------------------------
Sub test1()
Dim Rg As Range, Sh As Worksheet
Dim LeMin As Long, LeMax As Long

'Onglet feuille à adapter
Set Sh = Worksheets("Feuil1")

With Sh
'Plage de cellules à définir de tes données
Set Rg = .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

With Rg
LeMin = Evaluate("Min(IF(" & .Address & _
"=""kr"",ROW(" & .Address & ")))")
LeMax = Evaluate("Max(IF(" & .Address & _
"=""kr"",ROW(" & .Address & ")))")
End With

With Sh
MsgBox .Name & "!" & .Range(.Cells(LeMin, Rg.Column), _
.Cells(LeMax, Rg.Column)).Address
End With
End Sub
'------------------------------------------------------------

MichD

Bonjour MichD,

Merci pour tes précieuses réponses.

Pour gérer les erreurs, en cas ou Mot n'est pas trouvé.

Que fait-on ?

Merci.

Une manière de procéder :

'------------------------------------------------------------
Sub test1()
Dim Rg As Range, Sh As Worksheet
Dim LeMin As Long, LeMax As Long
Dim Mot As String, R As Variant

On Error Resume Next
'Onglet feuille à adapter
Set Sh = Worksheets("Feuil1")

'Expression retenue
Mot = "Kr"

With Sh
Set Rg = .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

With Rg
LeMin = Evaluate("Min(IF(" & .Address & _
"=""" & Mot & """,ROW(" & .Address & ")))")
LeMax = Evaluate("Max(IF(" & .Address & _
"=""" & Mot & """,ROW(" & .Address & ")))")
End With

With Sh
R = .Name & "!" & .Range(.Cells(LeMin, Rg.Column), _
.Cells(LeMax, Rg.Column)).Address
If Err.Number <> 0 Then
Err = 0
MsgBox "Le """ & Mot & """ & n'a pas été trouvé."
Else
MsgBox R
End If
End With
End Sub
'------------------------------------------------------------

MichD

Merci infiniment MichD.

J'ai constaté que je peux tester avec la valeur de LeMax.

'--------------------
If LeMax = 0 Then
MsgBox "Le """ & Mot & """ n'a pas été trouvé"
End If
'--------------------