VBA variable dans HLookup

[grodep]

Bonjour à tous, dieu sait s'il existe des threads sur ce sujet, mais j'ai beau faire je ne trouve rien qui me permette de m'en sortir, je vous livre donc mon mini souci :

Je cherche à remplacer le code ci dessous

      If Worksheets("Inscription").Range("F" & b).Value = "A" Then GoTo Suite2

par le code suivant

With Sheets("Inscription")
            alt = WorksheetFunction.HLookup("Lettre", Sheets("Inscription").Range("A1;Z75"), b, False)
            If alt = "A" Then GoTo Suite2

ceci dans l'unique but d'éviter des erreurs si j'insère dans mon tableau des colonnes supplémentaires et que la dite colonne F ("Lettre") ne devienne la colonne G ou autre.
Malheureusement je n'arrive pas à trouver la syntaxe correcte pour la fonction HLOOKUP et je vous avoue que je coince :/

Merci d'avance aux contributeurs pour leurs avis et recommandations.

A toutes fins utiles, je joins un fichier de test sur lequel tourne ce code.

 

[pierrejean]

Re : VBA variable dans HLookup

Bonjour grodep

A tester:

 Set c = Sheets("Inscription").Cells.Find("Lettre", LookIn:=xlValues, lookat:=xlWhole)
 If Worksheets("Inscription").Cells(b, c.Column) = "A" Then GoTo Suite2

 

[camarchepas]

Re : VBA variable dans HLookup

bonjour Pierre Jean, GroDep,

Juste une petite faute de frappe

Cordialement

Sub TESTS()
Dim alt As Variant
With Worksheets("Inscription")
        'derniere cellule non vide colonne B
        derlig = .Range("B" & Rows.Count).End(xlUp).Row
        'mise en memoire plage cellules
        Set plage = .Range("B2:B" & derlig)
    End With
    'boucle balayage plage
    b = 1
    For Each cel In plage
    a = 0
    b = b + 1
        With Sheets("Inscription")
             alt = Application.WorksheetFunction.HLookup("Lettre", Sheets("Inscription").Range("A1:F75"), b, False)
            If alt = "A" Then GoTo Suite2
   '         If Worksheets("Inscription").Range("F" & b).Value = "A" Then GoTo Suite2
    
        End With
        For Each sh In Sheets
                If sh.Name = cel.Value Then a = a + 1
            Next sh
        If a > 0 Then GoTo Suite2
        ' Création d'une nouvelle feuille
        Sheets.Add After:=Sheets(Sheets.Count)
        'nom feuille
        ActiveSheet.Name = cel.Value
Suite2:
    
    Next
    

End Sub

 

[Guest]

Re : VBA variable dans HLookup

Bonjour,

Pas bien compris le but de l'opération.

Puisque tu utilises un Tableau (au sens excel) pourquoi ne pas utiliser ses possibilités en VBA exemple:

With Worksheets("Inscription")
    MsgBox .Range("tableau1[tret]").Column
    MsgBox .ListObjects("tableau1").ListColumns("tret").Range.Column
End With

Voir l'aide vba sur ListObject et ses enfants.

A+

 

[grodep]

Re : VBA variable dans HLookup

merci pierrejean, ça n'est pas la première fois (ni la dernière, je le crains) que tu me tires de l'ornière!

A tout hasard, et sans vouloir abuser n'y a t'il pas moyen d'améliorer cette vilaine boucle destinée à comparer les noms de la colonne B avec ceux des onglets déjà existants?

Set c = Sheets("Inscription").Cells.Find("Lettre", LookIn:=xlValues, lookat:=xlWhole)
 If Worksheets("Inscription").Cells(b, c.Column) = "A" Then GoTo Suite2
        For Each sh In Sheets
                If sh.Name = cel.Value Then a = a + 1
            Next sh
        If a > 0 Then GoTo Suite2
        ' Création d'une nouvelle feuille
        Sheets.Add After:=Sheets(Sheets.Count)

Encore merci !

 

[grodep]

Re : VBA variable dans HLookup

merci çamarchepas, si je te suis, il me fallait juste écrire alt = Application.WorksheetFunction.HLookup au lieu de alt = WorksheetFunction.HLookup !! Incroyable, quoi !


Je reteste ça plus tard, pour le moment quand je fais ta correction, il me parle d'une boucle next sans For. Rien de grave sans doute!

 

[grodep]

Re : VBA variable dans HLookup

hasco, tu dis ne pas comprendre le but de l'opération, mais moi j'avoue que c'est ton code que je ne comprends absolument pas : peux tu m'en dire un peu plus?

 

[Guest]

Re : VBA variable dans HLookup

Bonjour, (Eh oui, n'ai pas eu l'heur de te croiser ce jour)

Ce que je voulais dire c'est qu'avec ton tableau (créé par insertion Tableau) qui est nommé tableau1 tu peux référencer la colonne 'Lettre' par

Range("tableau1[Lettre]")

quelque soit sa position dans le tableau. Que tu peux également parcourir les lignes d'une colonne par :

For each Cel In .Range("Tableau1[NomColonne]")

sans avoir à en connaitre ni la ligne de départ ni la ligne de fin.

Dans le fichier joint tu trouveras la macro suivante qui crée les feuilles nommées par la valeurs de la colonne b ("pteist") pour toutes les lignes qui n'ont pas "A" dans la colonne "Lettre".
A relire ton brouillon il me semble que c'est ce que tu cherches à faire.

Sub test()
    Dim cel As Range
    Dim colLettre As Long
    Dim sh As Worksheet
    With Worksheets("Inscription")
        'Numéro de colonne de "Lettre"
        colLettre = .Range("tableau1[Lettre]").Column
        'Pour chaque cellule de la colonne "pteist"
        For Each cel In .Range("tableau1[pteist]")
            'si la valeur de la colonne lettre est <>A
            If .Cells(cel.Row, colLettre) <> "A" And cel <> "" Then
                'Voir si une feuille existe déjà dans le classeur
                On Error Resume Next
                Set sh = Worksheets(cel.Value)
                On Error GoTo 0
                'Si elle n'existe pas la créer
                If sh Is Nothing Then
                    Set sh = Sheets.Add(after:=Sheets(Sheets.Count))
                    sh.Name = cel.Value
                End If
            End If
            Set sh = Nothing 'important de détruire la référence à la feuille
        Next cel
    End With
End Sub

A+

 

[grodep]

Re : VBA variable dans HLookup

Mille excuses, hasco, c'est exact que j'aurai du commencer par la plus élémentaire des politesses, ma seule excuse étant que relisant pour la énième fois ce post, je n'avais pas le sentiment de vous avoir quitté...

Merci en tout cas de faire tous ces efforts pédagogiques, je prends le temps de comprendre le fichier joint et reviendrai sans doute plus tard, ne serait ce que pour dire que j'ai compris et que ça fonctionne comme je le souhaitais !

 

[grodep]

Re : VBA variable dans HLookup

Ben, c'est parfait, je ne connaissais pas du tout ces fonctions sur les tableaux objets, ça simplifie bien des choses.
Encore une fois merci donc!