Désignation d'un range sous la forme .Cells(

[cibleo]

Bonjour le forum,

Je bute sur la définition d'une zone multi-cellules avec la syntaxe de l'objet Cells 🙄

With Sheets("Feuil2")
Col =5 'pour l'exemple
With .Range(.Cells(3, Col), .Cells(.Rows.Count, Col).End(xlUp))
.../...
end with

Ici l'adresse renvoyée est $E$3:$E$26, 26 étant la dernière ligne de la colonne E.
J'aimerais obtenir $E$3:$E$24, en considérant que la dernière ligne prise en compte doit être celle de la colonne G (la 24 ici)

résultat affiché dans la fenêtre exécution :

?Sheets("Feuil2").Range(Sheets("Feuil2").Cells(3, 5), Sheets("Feuil2").Cells(Sheets("Feuil2").Rows.Count, 5).End(xlUp)).address
$E$3:$E$26

?Sheets("Feuil2").Range(Sheets("Feuil2").Cells(3, 5), Sheets("Feuil2").Cells(Sheets("Feuil2").Rows.Count, 7).End(xlUp)).address
$E$3:$G$24

Cela va vous paraitre élémentaire, mais je n'y arrive pas avec la syntaxe de l'objet Cells 🙁
Pouvez-vous m'éclairer ?

Cibleo

 

[youky(BJ)]

Re : Désignation d'un range sous la forme .Cells(

Bonjour,
Sans doute comme ceci.
With .Range("E1:E" & .[G65536].End(3).Row)
Bruno

 

[Cousinhub]

Re : Désignation d'un range sous la forme .Cells(

bonjour,

Bonjour Youki

En gardant les .Cells....

Sub ee()
Dim Col As Byte
Dim Plg As Range
With Sheets("Feuil1")
    Col = 5 'pour l'exemple
    Set Plg = .Range(.Cells(3, Col), .Cells(Rows.Count, Col + 2).End(xlUp).Offset(, -2))
    Plg.Select 'pour la démo
End With
End Sub

Bonne journée

 

[Staple1600]

Re : Désignation d'un range sous la forme .Cells(

Bonsoir la compagnie

Une variante

Sub a()
MsgBox Cells(1, "E").Resize([G65536].End(xlUp).Row - 2).Address
End Sub

 

[ROGER2327]

Re : Désignation d'un range sous la forme .Cells(

Bonjour à tous

Dans le style de bhbh, ma préférence va à ceci :​

    With Sheets("Feuil2")
        Col = 5 'pour l'exemple
        With .Range(.Cells(3, Col), .Cells(.Rows.Count, Col + 2).End(xlUp).Offset(0, -2))
            '.../...
        End With
    End With

Bonne soirée.


ROGER2327
#6172

Mercredi 11 Tatane 139 (Saintes Canicule et Canule, jouvencelles - fête Suprême Quarte)
6 Thermidor An CCXX, 7,1242h - prêle
2012-W30-2T17:05:53Z

 

[cibleo]

Re : Désignation d'un range sous la forme .Cells(

Bonsoir à tous,

Pour plus de compréhension, j'ai intégré la solution de bhbh et roger dans la macro initiale parcequ'elle emploie l'objet Cells.
Je boucle sur les colonnes E,G,I et J et la dernière ligne prise en compte doit être celle de la colonne G.
Donc à chaque itération, la plage renvoyée doit être successivement :
E3:E24
G3:G24
I3:I24
J3:J24

Sub Maxi_et_Mini1()
Dim LesColonnes(), Col&, k&, MaxVal#, MinVal#
    LesColonnes = Array("Prix du litre", "Conso Mensuelle", "Conso Moyenne", "Coût gazole au km")
    With Sheets("Feuil2")
        'Boucle sur les colonnes E,G,I et J
        For k = LBound(LesColonnes) To UBound(LesColonnes)
            Col = .Rows("2:2").Find(What:=LesColonnes(k), LookAt:=xlWhole).Column
            
            'La solution de bhbh et Roger2327
            Set Plg = .Range(.Cells(3, Col), .Cells(Rows.Count, Col + 2).End(xlUp).Offset(, -2))
            Plg.Select
            
            'La solution initiale qui ne convient pas
            Set Plg1 = .Range(.Cells(3, Col), .Cells(.Rows.Count, Col).End(xlUp))
            Plg1.Select
            MsgBox "Solution bhbh ou Roger2327" & vbCrLf & Plg.Address & vbCrLf & _
            vbCrLf & "Solution initiale ne convenant pas" & vbCrLf & Plg1.Address
            'MsgBox Plg1.Address
            ''''Blablabla
        Next k
    End With
End Sub

Sinon Staple et youki, on peut passer par une autre notation effectivement.
Bref, c'est pas ça 🙁

Cibleo

 

[Cousinhub]

Re : Désignation d'un range sous la forme .Cells(

Re-,

Effectivement, avec le code entier, bien plus facile, mais c'est dur de tout donner en même temps.....🙁

Tu rajoutes une variable de type Long, tu calcules la dernière cellule de la colonne G, et tu intègres cette variable dans ta définition de plage....

Bon courage

 

[ROGER2327]

Re : Désignation d'un range sous la forme .Cells(

Bonsoir à tous

Ce qui est difficile, ce n'est pas de trouver la réponse, c'est de poser la bonne question...​

Sub Maxi_et_Mini1()
Dim LesColonnes(), Col&, Lig&, k&, MaxVal#, MinVal#
    LesColonnes = Array("Prix du litre", "Conso Mensuelle", "Conso Moyenne", "Coût gazole au km")
    With Sheets("Feuil2")
        Col = .Rows("2:2").Find(What:=LesColonnes(1), LookAt:=xlWhole).Column
        Lig = .Cells(Rows.Count, Col).End(xlUp).Row
        For k = LBound(LesColonnes) To UBound(LesColonnes)
            Col = .Rows("2:2").Find(What:=LesColonnes(k), LookAt:=xlWhole).Column
            With .Range(.Cells(3, Col), .Cells(Lig, Col))
                MaxVal = Application.Max(.Cells)
                MinVal = Application.Min(.Cells)
                .Interior.ColorIndex = xlNone
                .Find(What:=MaxVal, LookIn:=xlFormulas).Interior.ColorIndex = 45
                .Find(What:=MinVal).Interior.ColorIndex = 43
            End With
        Next k
    End With
End Sub

ROGER2327
#6173

Mercredi 11 Tatane 139 (Saintes Canicule et Canule, jouvencelles - fête Suprême Quarte)
6 Thermidor An CCXX, 8,2875h - prêle
2012-W30-2T19:53:24Z

 

[cibleo]

Re : Désignation d'un range sous la forme .Cells(

Une nouvelle fois merci, Roger 🙂
Cela va me permettre de mettre en valeur la nième petite valeur ou grande valeur dans certaines plages.

.../...
If Col = 5 Or Col = 11 Then
  MaxVal1 = Application.Large(.Cells, 2)
  .Find(What:=MaxVal1).Interior.ColorIndex = 19
End If
If Col = 9 Then
  MinVal1 = Application.Small(.Cells, 2)
  .Find(What:=MinVal1).Interior.ColorIndex = 15
End If

Cibleo