Problème : ThisWorkbook.Sheets

Version imprimable

22/08/2013, 10h25
tibss

Problème : ThisWorkbook.Sheets
Bonjour,

J'ai un petit soucis,

Dans une macro, je veux compter le nombre de cellules ayant une valeur différente de 20 dans une plage. Pour se faire, j'ai réalisé la macro suivante :
Code:

1 2 3 4 5 6 7 8 Dim Nbr As Integer Nbr = 0 For Each cell In ThisWorkbook.Sheets("Feuil1").Range(Cells(25, 4), Cells(27, 4)) If cell.Value <> 20 Then Nbr = Nbr + 1 Next
Le compilateur m'affiche l'erreur suivante : "Erreur d'exécution '1004'. Erreur définie par l'application ou par l'objet".
Cette erreur semble être due à "For Each cell In ThisWorkbook.Sheets("Feuil1").Range(Cells(25, 4), Cells(27, 4))".
La macro fonctionne lorsque je l'exécute en me trouvant sur la feuille où s'effectue le comtpage, mais ne marche pas quand je l'exécute alors que je me trouve sur une autre feuille du classeur.

Merci d'avance pour votre aide.

Bonjour,

Essaie comme cela:

Code:

1
2
3
4
5
6
7
Dim Nbr As Integer
 
Nbr = 0
 
For Each cell In ThisWorkbook.ActiveSheet.Range(Cells(25, 4), Cells(27, 4))
If cell.Value <> 20 Then Nbr = Nbr + 1
Next

Cdt,
S.

22/08/2013, 11h20
Jean-Philippe André

Salut,

ton problème vient de la partie

Code:

Cells(25, 4), Cells(27, 4)

Car comme tu fais référence à un objet "plus haut" dans l'arbre que les cells, il faut appliquer le même niveau pour les Cells.

Donc deux solutions principales :

Code:

For Each cell In ThisWorkbook.Sheets("Feuil1").Range(ThisWorkbook.Sheets("Feuil1").Cells(25, 4), ThisWorkbook.Sheets("Feuil1").Cells(27, 4))

ou plus simplement

Code:

For Each cell In ThisWorkbook.Sheets("Feuil1").Range("D25:D27")
22/08/2013, 11h26
tibss

Merci beaucoup jpcheck !

Tu as effectivement raison, j'avais complètement omis cette subtilité !!

Merci aussi SMBeccaria.

A bientôt,
29/01/2023, 14h32
raf58

Citation:

Envoyé par Jean-Philippe André

Salut,

ton problème vient de la partie

Code:

Cells(25, 4), Cells(27, 4)

Car comme tu fais référence à un objet "plus haut" dans l'arbre que les cells, il faut appliquer le même niveau pour les Cells.

Donc deux solutions principales :

Code:

For Each cell In ThisWorkbook.Sheets("Feuil1").Range(ThisWorkbook.Sheets("Feuil1").Cells(25, 4), ThisWorkbook.Sheets("Feuil1").Cells(27, 4))

ou plus simplement

Code:

For Each cell In ThisWorkbook.Sheets("Feuil1").Range("D25:D27")

bonjour !
j'exhume ce "vieux" post : je ne comprends pas bien l'histoire de l'arbre (je débute en VBA...), je suppose que c'est une histoire de hierarchie dans la structure VB, mais quoi précisément ?
merci !
29/01/2023, 15h40
Jean-Philippe André

Salut,

la logique VBA veut qu'à partir du moment où tu travailles à un "niveau" (Classeur, feuille, plage de cellules, cellule), l'ensemble de tes références doit être traité avec la même "considération".

Si tu utilises Worksheets() dans ta ligne, toutes les cellules doivent être déterminées en prenant en compte leur feuille d'appartenance.
29/01/2023, 15h57
raf58

2 pièce(s) jointe(s)

Citation:

Envoyé par Jean-Philippe André

Salut,

la logique VBA veut qu'à partir du moment où tu travailles à un "niveau" (Classeur, feuille, plage de cellules, cellule), l'ensemble de tes références doit être traité avec la même "considération".

Si tu utilises Worksheets() dans ta ligne, toutes les cellules doivent être déterminées en prenant en compte leur feuille d'appartenance.

ok merci, c'est pas encore complètement clair mais je suis sur la piste.

en fait, j'ai un pb avec le code suivant, voir fichier test ci joint : sur mon poste, la variable "a" revient vide alors qu'elle devrait contenir la liste Tableau2[clients]
ça fait 2 heures que je patine sur le pb sans trouver la solution, une idée ?

merci !

Pièce jointe 633305
29/01/2023, 23h44
deedolith

Code:

On Error Resume Next

Toi, tu cherches les emmerdes !!
Si des erreurs (ou exceptions, comme tu veux) surviennent, c'est qu'il y a une raison, et les ignorer est tout sauf la bonne réponse à y apporter !!!
Commence par virer cette horreur sans nom.

La discipline n'a jamais tué personne, Option Explicit c'est pour qui ?

Et la solution du lien ci-dessous ne serait-elle pas plus adaptée a ce que tu veux faire ?
https://www.excel-exercice.com/menu-...ant-dynamique/
30/01/2023, 12h32
raf58

Citation:

Envoyé par deedolith

Code:

On Error Resume Next

Toi, tu cherches les emmerdes !!
Si des erreurs (ou exceptions, comme tu veux) surviennent, c'est qu'il y a une raison, et les ignorer est tout sauf la bonne réponse à y apporter !!!
Commence par virer cette horreur sans nom.

La discipline n'a jamais tué personne, Option Explicit c'est pour qui ?

Et la solution du lien ci-dessous ne serait-elle pas plus adaptée a ce que tu veux faire ?
https://www.excel-exercice.com/menu-...ant-dynamique/

euh... non je ne cherches pas les emmerdes comme tu dis

j'ai juste récupéré ce code assez "standard" semble t il pour gérer une combobox
je débute en VBA, et j'ai mis au moins 2h pour essayer de comprendre ce que ça faisait

mon besoin est de pouvoir sélectionner une valeur dans une liste, mais en entrant quelques caractères pour qu'il me propose une liste réduite
typiquement saisir "a" pour qu'il ne me propose que "banane" et "ananas" parmi la liste de fruits

la fonctionnalité "validation de données" standard de excel ne le permet pas
est ce que ce que tu proposes le permet, avant que je m'y plonge ?

et surtout pouvoir mettre la liste sur une feuille différente de celle de la combobox : c'est la que ce code plante, impossible de savoir pourquoi !

merci pour tes conseils
Raphaël

Le code que tu nous as présenté était naïf et extrêmement mauvais (a ce demander s'il a été testé), j'ai tout scratché.

Ce que tu désire requiert de programmer plusieurs d'évènements, et de bien avoir en tête leur mécaniques.
En d'autre termes, connaitre ce qui les déclenche, et il n'y a pas que les interactions utilisateur (les propriétés / méthodes tel que value, text, dropdown le font aussi).
Il faut aussi être capable de traquer l'état de la combobox (liste ouverte ou fermée), ce qui n'est pas possible en standard,
ainsi que savoir quand court-circuiter les évènements (ils interfèrent entre eux).

Bref, voila ce que j'ai pu faire de mieux (c'est lent, et malheureusement, on y peut rien):

Code:

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
Option Explicit
 
Private DropDown As Boolean         '// track l'etat de la combobox (ouvert / fermé)
Private UpdateDropDown As Boolean   '// track le besoin de mise à jour de la liste
 
Private Sub ComboBox1_DropButtonClick()
        '// affiche la liste restreinte
    If Not (DropDown) Then
        If (UpdateDropDown) Then
            Dim ListValeurs As Variant
            ListValeurs = getList
 
            If ComboBox1.Value <> "" Then
                Dim D1 As Scripting.Dictionary
                Set D1 = CreateObject("Scripting.Dictionary")
 
                Dim Key As String
                Key = UCase("*" & Replace(ComboBox1.Value, " ", "*") & "*")
 
                Dim Item As Variant
                For Each Item In ListValeurs
                    If UCase(Item) Like Key Then
                        D1(Item) = ""
                    End If
                Next
                ComboBox1.List = D1.keys
            End If
        End If
    End If
    DropDown = Not DropDown
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        '// affiche et rafraichis la combobox (si besoin)
    If Not Intersect(Range("Tableau1[cc2]"), Target) Is Nothing And Target.Count = 1 Then
        ComboBox1.List = getList
        setPosition ComboBox1, Target
 
        ComboBox1.Value = Target.Value
        ComboBox1.Visible = True
    Else
        Me.ComboBox1.Visible = False
    End If
End Sub
 
Private Function getList() As Variant
        '// obtiens la liste des valeurs
    Dim Wb As Excel.Workbook
    Set Wb = Me.Parent
    Dim valuesRange As Range
    Set valuesRange = Worksheets("Feuil2").Range("Tableau2[clients]")
 
    Dim ListValeurs As Variant
    ListValeurs = Application.Transpose(valuesRange.Value)
    getList = ListValeurs
End Function
 
Private Sub setPosition(ByRef Cb As MSForms.ComboBox, ByRef Rng As Excel.Range)
        '// positionne la cb sur une cellule
    Cb.Height = Rng.Height + 3
    Cb.Width = Rng.Width
    Cb.Top = Rng.Top
    Cb.Left = Rng.Left
End Sub
 
Private Sub ComboBox1_Change()
        '// repercute la valeur de la cb dans la cellule
    ActiveCell.Value = ComboBox1.Value
End Sub
 
Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        '// Affiche la liste complete
    ComboBox1.List = getList
    UpdateDropDown = False  '// court-circuite l'evennement ComboBox1_DropButtonClick
                            '// sinon la liste sera mise à jour (et restreinte)
    ComboBox1.DropDown
    UpdateDropDown = True
End Sub
 
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        '// reproduit l'appuis sur la touche entrée
        '// lorsqu'on saisit une cellule
    If KeyCode = 13 Then
        ActiveCell.Offset(1).Select
    End If
End Sub

PS: Par expérience, vouloir altérer le comportement de contrôles n'est pas une bonne idée.
Pour la simple raison que les contrôles réalisent des opérations en interne qui sont totalement hors de notre champ d'action.
VBA n'a malheureusement pas à sa disposition la toute puissance de .Net.