VBA : récupérer plage de données correspondant à une valeur Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
s
sarahhhhhhhh
Nouveau venu
Nouveau venu
Messages : 3
Inscrit le : 30 juillet 2015
Version d'Excel : 2013 FR

Message par sarahhhhhhhh » 30 juillet 2015, 16:59

Bonjour,

J'aimerais récupérer les indices de début et de fin de chaque plage correspondant à une valeur.
Par exemple, j'ai une colonne A contenant plusieurs valeurs (1 valeur = 1 cellule)
A
4
4
3
3
3
3
3
2
2
2
2
1
3
3
3
3
5

J'aimerais récupérer les deux plages de données contenant la valeur "3", c'est à dire que je voudrais avoir l'indice correspondant à la première et dernière ligne de la plage 1 et l'indice de la première ligne et dernière ligne de la plage 2. Comment puis-je procéder ?

Merci d'avance,

Cordialement
h
h2so4
Passionné d'Excel
Passionné d'Excel
Messages : 9'151
Appréciations reçues : 376
Inscrit le : 16 juin 2013
Version d'Excel : 365 UK Windows 10

Message par h2so4 » 30 juillet 2015, 18:41

Bonjour,

une solution via une fonction personnalisée

à utiliser

=indplage(plage, valeur)

exemple
=indplage(A1:A18,3) recherche les indices de début et de fin des plages contenant 3

résultat par exemple 4-8;14-17
code à rajouter dans un module
Function indplage(r, v)
    indplage = ""
    For Each c In r
        If c = v Then
            If f = False Then
                f = True
                If s <> "" Then s = s & ";" & c.Row Else s = c.Row
            End If
        ElseIf f = True Then
            f = False
            s = s & "-" & c.Row - 1
        End If
    Next
 If f = True Then s = s & "-" & r.Rows.Count
indplage = s
End Function
Avatar du membre
ThauThème
Passionné d'Excel
Passionné d'Excel
Messages : 3'860
Appréciations reçues : 177
Inscrit le : 19 octobre 2014
Version d'Excel : 2010 FR

Message par ThauThème » 30 juillet 2015, 18:59

Bonsoir Sarah, Acide, bonsoir le forum,

Une autre proposition avec un bouton et une boîte d'entrée pour déterminer la valeur de référence :
Sub Macro1()
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim PV As Range 'déclare la variable PV (Plage des Valeurs)
Dim DL As Integer 'déclare la variable DL (Dernière Ligne)
Dim PL As Range 'déclare la variable PL (PLage)
Dim CEL As Range 'déclare la variable CEL (CELlule)
Dim V As Variant 'déclare la variable V (Valeur)
Dim TA() As Variant 'déclare la variable TA (Tableau des Adresses)

Set O = Sheets("Feuil1") 'définit l'onglet O (à adapter)
Set PV = O.Range("B1") 'initialise la plage PV
DL = O.Cells(Application.Rows.Count, 1).End(xlUp).Row 'définit la dernière ligne éditée DL de la colonne 1 (=A)
Set PL = O.Range("A1:A" & DL) 'définit la plage PL
O.Range("C1").CurrentRegion.ClearContents 'efface les éventuelles anciennes données en colonne C
'définit la boite d'entrée V qui va déterminer la valeur de référence
V = Application.InputBox("Tapez la valeur de référence ou cliquer dans une cellule la contenant !", "REFÉRENCE")
If V = False Or V = "" Then Exit Sub 'si bouton "Annuler" ou boîte d'entrée non renseignée, sort de la procédure
For Each CEL In PL 'boucle sur toutes les cellules CEL de la plage PL
    'si la valeur de la cellule CEL (convertie en texte) est égale à V, redéfinit la plage PV
    If CStr(CEL.Value) = V Then Set PV = IIf(PV.Column = 2, CEL, Application.Union(PV, CEL))
Next CEL 'prochaine cellule de la boucle
For I = 1 To PV.Areas.Count 'boucle sur le nombre de plages non contiguës de PV
    ReDim Preserve TA(1 To I) 'redimensionne le tableau des adresses TA
    TA(I) = PV.Areas(I).Address(0, 0) 'récupère l'adresse de la plage non contiguë
Next I 'prochaine plage de la boucle
'renvoie dans la cellule C1 redimensionnée le tableau des adresse TA trtansposé
If PV.Address(0, 0) = "B1" Then MsgBox "Acune cellule ne contient la valeur :" & V: Exit Sub
O.Range("C1").Resize(UBound(TA)) = Application.Transpose(TA)
End Sub
Sarah_v01.xlsm
(24.85 Kio) Téléchargé 72 fois
À plus,

ThauTheme


Je suis Charlie
VBA m'éclate, les formules m'ennuient ! Je n'y peux rien c'est comme ça...
s
sarahhhhhhhh
Nouveau venu
Nouveau venu
Messages : 3
Inscrit le : 30 juillet 2015
Version d'Excel : 2013 FR

Message par sarahhhhhhhh » 31 juillet 2015, 10:26

Merci beaucoup pour vos réponses.
J'aimerais remplir un tableau "plage" avec 1 plage par ligne, la colonne 1 correspondrait au début de la plage et la deuxième colonne à la fin de la plage de sorte à pouvoir parcourir facilement chaque plage de données par la suite.
Avatar du membre
ThauThème
Passionné d'Excel
Passionné d'Excel
Messages : 3'860
Appréciations reçues : 177
Inscrit le : 19 octobre 2014
Version d'Excel : 2010 FR

Message par ThauThème » 31 juillet 2015, 11:21

Re,

C'est en gros ce que faisait le premier code que je t'ai proposé ! À la seule différence que la plage était dans une seule colonne.
Dans le fichier en pièce jointe j'ai remanié le code pour que tu aies la plage séparée avec une colonne Début et une colonne Fin. Puis je me suis dit qu'il serait préférable de faire réagir au double-clic dans une valeur de la colonne A. C'est pour ça qu'il y a deux macros très similaires. La première ouvre une boîte d'entrée pour demander à l'utilisateur la valeur de référence. La seconde agit au double-clic dans la valeur de référence.
Sarah_v02.xlsm
(26.64 Kio) Téléchargé 48 fois
À plus,

ThauTheme


Je suis Charlie
VBA m'éclate, les formules m'ennuient ! Je n'y peux rien c'est comme ça...
h
h2so4
Passionné d'Excel
Passionné d'Excel
Messages : 9'151
Appréciations reçues : 376
Inscrit le : 16 juin 2013
Version d'Excel : 365 UK Windows 10

Message par h2so4 » 31 juillet 2015, 11:27

Bonjour,

solution avec une fonction matricielle personnalisée (à valider par ctrl-maj-entrée) après avoir sélectionné la zone de réception de la réponse et introduit la formule dans la barre de formule.

Function indplage(r, v)
' fonction matricielle
Dim lc()
nr = Application.Caller.Rows.Count
nc = Application.Caller.Columns.Count
If nc <> 2 Then MsgBox "la plage de destination doit avoir 2 colonnes": Exit Function
ReDim lc(1 To nr, 1 To nc)
    For Each c In r
        If c = v Then
            If f = False Then
                f = True
                cr = cr + 1: lc(cr, 1) = c.Row
            End If
        ElseIf f = True Then
            f = False
            lc(cr, 2) = c.Row - 1
        End If
    Next
 If f = True Then lc(cr, 2) = r.Rows.Count
indplage = lc
End Function
indplage.xlsm
(15.16 Kio) Téléchargé 49 fois
s
sarahhhhhhhh
Nouveau venu
Nouveau venu
Messages : 3
Inscrit le : 30 juillet 2015
Version d'Excel : 2013 FR

Message par sarahhhhhhhh » 31 juillet 2015, 12:03

Merci encore une fois, mais je n'arrive toujours pas à faire ce que je souhaite (désolé je suis débutante..)

J'aimerais appeler la fonction indplage et qu'elle me remplisse une variable (tableau) et non remplir réellement des cellules.

Exemple :
Dim var_plage
var_plage = indplage(range,3)
et donc ma variable var_plage contiendrait 2 colonnes : var_plage(1,1,) = 4 ; var_plage(1,2) = 8 ; var_plage(2,1) = 14 ; var_plage(2,2) =17
J'ai essayé de modifier un peu le code pour faire cela mais j'ai des erreurs incompatibilités.
h
h2so4
Passionné d'Excel
Passionné d'Excel
Messages : 9'151
Appréciations reçues : 376
Inscrit le : 16 juin 2013
Version d'Excel : 365 UK Windows 10

Message par h2so4 » 31 juillet 2015, 12:18

re-bonjour,

voici comment faire pour un appel de la fonction à partir de VBA
Function indplage(r, v)
' retourne un tableau
Dim lc(1 To 10, 1 To 2) '10 lignes maximum
       For Each c In r
        If c = v Then
            If f = False Then
                f = True
                cr = cr + 1:
                lc(cr, 1) = c.Row
            End If
        ElseIf f = True Then
            f = False
            lc(cr, 2) = c.Row - 1
        End If
    Next
 If f = True Then lc(cr, 2) = r.Rows.Count
indplage = lc
End Function

Sub test()
Dim plage As Variant
plage = indplage(Range("A1:A18"), 3)
For i = 1 To 2
For j = 1 To 2
MsgBox plage(i, j)
Next j
Next i
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message