XL 2013 VBA: utiliser l'adresse d'une plage

[stan1]

Bonjour tous le monde!

Débutant, e cherche de l'aide pour une macro VBA!
Mon but est de récupérer une valeur déterminé par l'utilisateur. Une inputbox s'affiche et demande un nombre. Ce nombre détermine l'en-tête d'une colonne et la colonne correspondante est sélectionnée.

Maintenant j'aimerais pouvoir utiliser les coordonnées de cette colonne pour d'autres actions mais j'y arrive pas.
Pour le moment j'ai ce code:

Sub bingo()
Dim Cel As Range
Dim reponse As Integer

reponse = InputBox("Année")

  Set Cel = Cells.Find(what:="reponse*")
  If Not Cel Is Nothing Then
    Cells(2, Cel.Column).Resize(Cells(Rows.Count, Cel.Column).End(xlUp).Row).Select
 
COMMENT UTILISER CETTE SELECTION?
 
Else
    MsgBox "Année nom trouvée "
    Exit Sub
  End If

End Sub

Le premier problème c'est que la valeur entrée dans inputbox que je définis comme la variable "reponse", apparemment la fonction Cell.Find cherche le mot "reponse" que la valeur de la variable.

Mon deuxième problème c'est de pouvoir utiliser la plage correspondant par la valeur entrée. C'est-à-dire effectuer des actions sur cette plage par la suite. Dans le code j'ai utilisé .select mais en fait je ne vaux même pas le sélection mais seulement utiliser la plage.

Merci d'avance à la personne qui m'apportera son aide!

 

[stan1]

Re : VBA: utiliser l'adresse d'une plage

J'ai trouvé pour la fonction cells.find:

Il ne faut pas utiliser les guillemets pour faire référence à la valeur entée dans l'inputbox!

Par contre toujours bloqué pour utiliser l'adresse de la plage sélectionnée!

 

[stan1]

Re : VBA: utiliser l'adresse d'une plage

Re,

J'ai utilisé "set" et ça ne bug plus mais je n'arrive toujours pas à utiliser la plage sélectionnée par exemple dans une ligne comme:

a= sheets("feuil2").Range.(???)

Dim Cel As Range
Dim reponse As Integer
Dim pl As Range

reponse = InputBox("Année")
    Set f = Sheets("feuil1")
    Set Cel = Cells.Find(what:=reponse)
        If Not Cel Is Nothing Then
    Cells(2, Cel.Column).Resize(Cells(Rows.Count, Cel.Column).End(xlUp).Row).Select
         
    Set pl = Selection
  Else
    MsgBox "Pas trouvé le nom "
    End If

Par exemple ici pour utiliser la sélection je ne peux pas faire :
a= sheets("feuil2").Range.("pl")

 

[stan1]

Re : VBA: utiliser l'adresse d'une plage

Re ,

Au cas ou mon explication n'est pas claire:

Dim Cel As Range
Dim year As Integer
Dim pl As Range

response = InputBox("year") 'Etape 1
    Set f = Sheets("sheet1")
    Set Cel = Cells.Find(what:=response)  'Etape 2
        If Not Cel Is Nothing Then
    Cells(2, Cel.Column).Resize(Cells(Rows.Count, Cel.Column).End(xlUp).Row).Select 'Etape 3
    Set pl = Selection 'Etape 4
  Else
    MsgBox "year not found "
    End If

L'utilisateur entre une année dans l'inputbox(étape 1). Cette "année" correspond à l'entête d'une colonne qui est ensuite sélectionnée (étape 2). La colonne correspondante est célectionnée (étape 3).
Dans l'étape 4 je souhaite conserver l'adresse de la colonne dans une variable ici "pl" pour pouvoir la réutiliser par la suite.

Par exemple ici :

Dim mydico As Object, a, i%
    Set f = Sheets("sheet5")
    Set mydico = CreateObject("Scripting.Dictionary")
    a = f.Range("pl")

Je souhaite appliquer un "scipting.dictionary" sur la colonne sélectionnée. Mais ça ne marche pas! Il y a un problème au niveau de l'utilisation de l'adresse.

Merci d'avance si quelqu'un peut m'aider!!

 

[MJ13]

Re : VBA: utiliser l'adresse d'une plage

Bonjour Stan

Regarde ces codes qui pourraient t'aider:

Sub Test1()
Range("zone").Select
End Sub
Sub test2()
Range("zone").Select
ZoneA = Selection.Address
Cells(1, 1).Select
Cells(1, 1) = "a"
Range(ZoneA).Select
End Sub
Sub DefinirZOne()
    Range("D10:F16").Select
    ActiveWorkbook.Names.Add Name:="ZoneB", RefersToR1C1:="=Feuil1!R10C4:R16C6"
End Sub
Sub Test3()
Range("ZoneB").Select
End Sub

 

[stan1]

Re : VBA: utiliser l'adresse d'une plage

Bonjour MJ13!

Merci pour ton aide! Mais j'ai essayé avec les différentes versions que tu m'as donnée et toujours rien!

Ici en affectant la selection à la variable "ZoneA": puis en essayant de la réutiliser pour le dictionary :
[Set mondico = CreateObject("Scripting.Dictionary")
a = f.Range(ZoneA)]

Sub bingo()

Dim Cel As Range
Dim ZoneA&
Dim reponse As Integer

reponse = InputBox("Année")
With Sheets("feuil1")
  Set Cel = .Cells.Find(what:=reponse)
  If Not Cel Is Nothing Then
    Cells(2, Cel.Column).Resize(Cells(Rows.Count, Cel.Column).End(xlUp).Row).Select
    ZoneA=selection.address
      Else
    MsgBox "Année non trouvée"    
  End If
End With

Set f = Sheets("Feuil1")
    Set mondico = CreateObject("Scripting.Dictionary")
    a = f.Range(ZoneA)
    For i = LBound(a) To UBound(a)
        If a(i, 1) <> "" Then mondico(a(i, 1)) = ""
    Next i
    End With

End Sub

 

[stan1]

Re : VBA: utiliser l'adresse d'une plage

En fait je ne souhaite pas définir une zone mais justement utiliser (dans un dictionary) une zone qui a été définie par l'utilisateur avec la fonction cells.find().... (sélection qui a été modifiée entre le deux avec .resize)....

 

[Dranreb]

Re : VBA: utiliser l'adresse d'une plage

Bonjour.
Pourquoi n'en prendriez vous pas d'abord les valeurs dans un tableau en mémoire pour limiter le nombre d'accès aux cellules ?
T = Intersect(f.[2:5000], f.UsedRange, Cel.EntireColumn).Value

 

[Si...]

Re : VBA: utiliser l'adresse d'une plage

salut

à voir

Sub bingo()
  Dim C As Range, P As Range, D, T()
  Dim reponse As Long
  reponse = InputBox("Année")
  Set C = Rows(1).Find(reponse)
  If Not C Is Nothing Then
     Set D = CreateObject("Scripting.Dictionary")
     Set P = Cells(2, C.Column).Resize(Columns(C.Column).End(xlDown).Row)
     For Each C In P: D(C.Value) = "": Next
   Else
    MsgBox "Année nom trouvée "
  End If
  'exemple d'utilisation du dico
  [A:A] = ""
  T = D.keys
  [A2].Resize(UBound(T)) = Application.Transpose(T)
End Sub

mais quel est l'intérêt du dico si tu ne l'utilses pas ?

Tu peux aussi, comme le suggère Dranreb (salut à toi), utiliser un tableau pour un gain de rapidité !

 

[stan1]

Re : VBA: utiliser l'adresse d'une plage

Re,

Merci beaucoup à vous trois pour votre aide! Avec vos conseils et exemples je suis arrivé au code suivant:

Dim Cel As Range
Dim a As Variant
Dim response As Variant
Dim i&, cl&
Dim mondico As Object

With Sheets("feuil1")
    response = InputBox("year:")
    Set Cel = .UsedRange.Find(what:=response)
        If Not Cel Is Nothing Then
        cl = Cel.Column
        a = .Range(.Cells(2, cl), .Cells(.Cells(Rows.Count, cl).End(xlUp).Row, cl))
                Else
        MsgBox "year not found "
        End If
End With
    
    Set mondico = CreateObject("Scripting.Dictionary")
        For i = LBound(a) To UBound(a)
            If a(i, 1) <> "" Then mondico(a(i, 1)) = ""
        Next i

@

Bonjour.
Pourquoi n'en prendriez vous pas d'abord les valeurs dans un tableau en mémoire pour limiter le nombre d'accès aux cellules ?
T = Intersect(f.[2:5000], f.UsedRange, Cel.EntireColumn).Value

Je ne maitrise pas encore assez les tableaux donc je ne vois pas comment procéder ainsi!

mais quel est l'intérêt du dico si tu ne l'utilses pas ?

Oui je l'utilise mais dans la suite du code!

Merci encore à vous trois!

 

[Dranreb]

Re : VBA: utiliser l'adresse d'une plage

Pourtant dans le code que vous reproduisez, vous utilisez bien déjà correctement un tableau contenu dans un Variant a. Pourquoi pas un tableau en soit d'ailleurs ? Ce n'est pas parce que beaucoup ne mettent pas les () à la déclaration (allez savoir pourquoi) qu'il faut faire la même ânerie. Pour l'alimenter la règle est simple: La propriété Value d'un Range représentant plusieurs cellules est justement très exactement un tableau d'éléments Variant toujours à 2 dimensions basé 1, même s'il n' y a qu'une ligne ou une colonne. Ça s'applique donc à la propriété Value de toute expression Range, y compris le résultat d'une intersection.