Discussion :

[alive85]

Bonjour,
Je veux me servir de la fonction Countif pour rechercher dans un tableau (listobject) le nombre d'occurences entre ma 1ère ligne de tableau et ma cellule sélectionnée (Target), mais je ne parviens pas à déclarer le range. : est-ce que c'et possible à faire ?
Merci d'avance

[Philippe Tulliez]

Bonjour,
Bien entendu
Pour déclarer une variable objet Tableau Structuré, c'est par exemple

Code :

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

1
2
Dim oList As ListObject
Set oList = Range("T_maTable").ListObject

Les propriétés qui renvoient un objet Range
Toute la table, c'est Range
Uniquement les données c'est dataBodyRange
Les données d'une colonne, c'est ListColumns("Nom de l'étiquette").DataBodyRange

C'est une façon de le faire, mais il y en a d'autres

Exemple

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
Sub t()
 Dim oList As ListObject
 Dim oRange As Range
 Dim oDataRange As Range
 Dim oColumnName As Range
 Dim txt As String
 Set oList = Range("T_MyTable").ListObject
 With oList
   Set oRange = .Range
   Set oDataRange = .DataBodyRange
   Set oColumnName = .ListColumns("Nom").DataBodyRange
 End With
 txt = "Table complète : " & oRange.Address
 txt = txt & vbCrLf & "Uniquement les données : " & oDataRange.Address
 txt = txt & vbCrLf & "La colonne des noms : " & oColumnName.Address
 MsgBox txt
 Set oList = Nothing
 Set oRange = Nothing
 Set oDataRange = Nothing
 Set oColumnName = Nothing
End Sub

[alive85]

Merci Philippe Tulliez, je regarde ça dès que j'ai 5 minutes.

[alive85]

Du coup, j'essaie de mettre ça en forme avec WorksheetFunction.CountIf() mais je n'arrive pas à y déclarer ma plage
j'ai essayé avec ça et come par magie... ça ne marche pas

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
Sub Numero_auto(ByVal Target As Range)
Dim Num_Titre as integer
Dim MaPlage as Range
Dim Debut as Range
Dim Fin as Range
Dim oList as ListObject
 
Set oList = Range("Electricite1").ListObject
Set Debut = oList.DataBodyRange.Rows(1)
Set Fin = Target.Row
Set MaPlage = Range(Debut,Fin)
 
Num_Titre = WorksheetFunction.CountIf(MaPlage, "[MB] Titre")
MsgBox(Num_Titre)
 
End Sub

[Philippe Tulliez]

Bonjour,
Je vous donne la manière de définir une plage de colonne et je ne vois nulle part l'application de ce que j'ai donné comme réponse. Pourquoi les variables debut et fin
Dans cette ligne, Num_Titre = WorksheetFunction.CountIf(MaPlage, "[MB] Titre"), qu'essayez-vous de dénombrer ?

[EDIT]
Dans l'illustration ci-dessous, j'affiche les valeurs au-dessus de 10 dans la colonne dont l'étiquette est Qté du tableau structuré nommé T_Stock et il n'y a pas de magie, cela fonctionne.

Le code de la procédure

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub t()
  Dim oList As ListObject
  Dim oColumnRange As Range
  Dim fn As WorksheetFunction
  Set oList = Range("T_Stock").ListObject
  Set oColumnRange = oList.ListColumns("Qté").DataBodyRange
  Set fn = Application.WorksheetFunction
  '
  MsgBox fn.CountIf(oColumnRange, ">10")
  '
  Set oList = Nothing: Set oColumnRange = Nothing: Set fn = Nothing
End Sub

On peut faire plus court

Code :

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

1
2
3
4
5
6
7
8
9
10
Sub t()
  Dim oColumnRange As Range
  Dim fn As WorksheetFunction
  Set oColumnRange = Range("T_Stock[Qté]")
  Set fn = Application.WorksheetFunction
  '
  MsgBox fn.CountIf(oColumnRange, ">10")
  '
  Set oColumnRange = Nothing: Set fn = Nothing
End Sub

et encore plus court

Code :

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

1
2
3
4
5
6
7
8
Sub t()
  Dim fn As WorksheetFunction
  Set fn = Application.WorksheetFunction
  '
  MsgBox fn.CountIf(Range("T_Stock[Qté]"), ">10")
  '
  Set fn = Nothing
End Sub

On pourrait bien entendu se passer également de la variable fn mais la question initiale était "Comment déclarer une plage dans un tableau en VBA"

[alive85]

Dans cette ligne, Num_Titre = WorksheetFunction.CountIf(MaPlage, "[MB] Titre"), qu'essayez-vous de dénombrer ?

j'essaie de compter le nombre de fois où "[MB] Titre" apparait dans la colonne "Forme" entre la 1ère ligne du tableau et la ligne dans laquelle j'ai ma cellule sélectionnée.

[Philippe Tulliez]

Bonjour,

le nombre d'occurences entre ma 1ère ligne de tableau et ma cellule sélectionnée (Target)

Désolé, mais je n'avais pas lu correctement votre question.

Voici le code d'une procédure qui dénombre le nombre de fois que le texte Oignon filet est présent dans la plage de cellules de la colonne "Libellé" et ce de la première ligne des données jusqu'à la cellule sélectionnée (pour l'exemple B11)

Code de la procédure

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
Sub t()
  Const TableName As String = "T_Stock"       ' Nom de la table
  Const LabelName As String = "Libellé"       ' Colonne où a lieu le dénombrement
  Const LookupText As String = "Oignon filet" ' Texte cherché
  Dim oList As ListObject
  Dim oColumnRange As Range
  Dim fn As WorksheetFunction
  Dim NumberOffRows As Long
  Set oList = Range("T_Stock").ListObject
  Set oColumnRange = oList.ListColumns(LabelName).DataBodyRange
  Set fn = Application.WorksheetFunction
  NumberOffRows = ActiveCell.Row - oList.Range.Row
  Set oColumnRange = oList.ListColumns(LabelName).DataBodyRange.Resize(NumberOffRows)
  '
  MsgBox "Recherche effectuée dans la plage : " & oColumnRange.Address _
         & vbCrLf & "Trouvé " & fn.CountIf(oColumnRange, LookupText) & " x " & LookupText
  '
  Set oList = Nothing: Set oColumnRange = Nothing: Set fn = Nothing
End Sub

Illustration



[EDIT]
Je viens de constater que dans la ligne 9 du code publié, j'avais oublié de faire référence à la valeur de la constante déclarée en début de procédure

Au lieu de

Code :

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

Set oList = Range("T_Stock").ListObject

il faut écrire

Code :

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

Set oList = Range(TableName).ListObject

[alive85]

Super, ça fonctionne ! merci Philippe

question bonus : si je veux que mon code soit utilisable pour plusieurs tableaux, il faut que je remplace Const par Dim en début de code puis que je libère la variable en fin d'exécution ?

[Philippe Tulliez]

Bonjour,
Pour que le code fonctionne pour d'autres tableaux, il faut créer une procédure (Function ou Sub) avec plusieurs arguments. Nom de la table, Colonne à traiter, N° de la ligne de fin ou objet Range correspondant à la sélection c'est selon.
C'est exactement comme cela qu'il faut faire dès que tu écris presque le même code une seconde fois

[alive85]

ok, merci pour tout