VBA macro et fonction RECHECHEV

Résolu/Fermé

mimi - 14 déc. 2005 à 18:12
Marie - 20 avril 2010 à 09:43

Bonjour à tous

j'utilise dans une macro la fonction RECHERCHEV ci-dessous
'=RECHERCHEV($G6;Feuil1!$A:$E;2;FAUX)
je renvoie la valeur de la colonne 2 du tableau feuille1 qui démarre en A et fini en E
MAIS ma colonne qui contient le valeur à renvoyer est variable (je dois renvoyer la colonne 3 ensuite
comment dire à ma macro que mon numéro de colonne est variable ?
J'ai essayé en déclarant une variable R par dim R as integer puis j'ai mis R à la place de 2 mais
cela ne marche pas
Merci pour votre aide à tous

mimi

A voir également:

Vba cells(i j)
Ï majuscule - Guide
I showkeyplus - Télécharger - Utilitaires
"Shutdown -i" ✓ - Forum Windows
Ou i ou a a ✓ - Forum Musique / Radio / Clip
Vba range avec variable ✓ - Forum VB / VBA

7 réponses

Réponse 1 / 7

Utilisateur anonyme
4 déc. 2008 à 22:49

Bonjour,

Je n'ai pas eu le temps de répondre. Le boulot, les études ...

Ceci dit, j'ai regarder la syntaxe du code et je n'y trouve que 2 choses bizarres !

1) Les variables [ i ] et [ j ] ne sont pas déclarés.

2) Dans l'instruction [ Cells(L, C).Select ], les lettres [ L ] et [ C ] ne sont pas déclaré.

Si tu veux tout sélectionner, l'instruction [ Cells.Select ] est suffisante.

Voici ce que ça donne :

Sub PlanningMarque()


    Dim Marque As String
    Dim Compteur As Double
    Dim Compteur2 As Double
    Dim Model As String
    Dim Segment As String
    Dim BodyType As String
    Dim Temps As String
    Dim i As Long, j As Long

    'Initiatlisation des variables
    Compteur = -3 'colonne Concatener
    Compteur2 = -2 'colonne Transfert
    Compteur3 = -5
    Compteur4 = -4

    For i = 5 To 40 'i = Lignes
        For j = 6 To 31 'j = Colonnes

            ' traitement des variables
            Marque = Cells(2, j).Value
            Temps = Cells(1, j).Value
            Segment = Cells(i, 1).Value
            BodyType = Cells(i, 2).Value

            Cells(i, j).Offset(i, Compteur).Select
            ActiveCell.FormulaR1C1 = "=CONCATENATE(Segment, BodyType, Marque, Temps)"
            Cells(i, j).Offset(i, Compteur2).Select
            ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Tableau!R6C1:R213C13, 6, 'False')"
            Model = Cells(i, 4).Value
            Cells(i, j).Select

            If Cells(4, j) Is Empty Then
                Cells(i, j).Select
                ActiveCell.FormulaR1C1 = "=CONCATENATE(Model, BodyType, Marque, Temps)"
            ElseIf Cells(4, j) Is Not Empty Then
                'Cells(L, C).Select
                Cells.Select
                ActiveCell.FormulaR1C1 = "=VLOOKUP(Model,Tableau!R6C1:R213C13, 6, 'False')"
            End If

        Next i
        Compteur = Compteur - 1
        Compteur2 = Compteur2 - 1

    Next j

End Sub
'

Je ne travaille que très peu avec l'instruction [ ElseIf ], mais je crois que tu dois valider
cette structure de contrôle, tu pourrais essayer comme ceci :

If Cells(4, j) Is Empty Then
    Cells(i, j).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(Model, BodyType, Marque, Temps)"
Else
    If Cells(4, j) Is Not Empty Then
        'Cells(L, C).Select
        Cells.Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(Model,Tableau!R6C1:R213C13, 6, 'False')"
    End If
End If
'

Et le second If imbriqué est implicite :

If Cells(4, j) Is Empty Then
    Cells(i, j).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(Model, BodyType, Marque, Temps)"
Else
    'Cells(L, C).Select
    Cells.Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(Model,Tableau!R6C1:R213C13, 6, 'False')"
End If
'

Lupin

tindette
5 janv. 2009 à 11:57

Bonjour Lupin,

Bonne année 2009!

Je me permets d'écrire ici car j'ai un problème similaire, sauf que c'est le nom de l'onglet que je veux déclarer en variable pour la formule de recherche verticale et non le nom de colonne. J'avais commencé une macro, mais Excel a bien planté et ne veut plus ouvrir mon fichier.
Quand je mets ma variable dans ma formule recherchev, lors de l'exécution, il faut que je cherche via l'explorateur qui s'ouvre le fichier et l'onglet concerné pour chaque case concernée.

En gros j'utilisais la chose suivante pour une donnée contenue dans la case A2 et A4 de mon onglet menu:

Dim nouveau = Worksheet
Dim ancien = Worksheet
nouveau = Sheets(Worksheets("Menu").Range("A2").Value)
ancien = Sheets(Worksheets("Menu").Range("A4").Value)
nouveau.Select

A ce moment-là, nouveau.Name renvoie bien le nom de l'onglet désigné dans la case A2.
Mais si je fais la chose suivante, cela ne marche pas. Pourquoi?

ActiveCell.FormulaR1C1 = "=VLOOKUP(ancien!C:C[1],2,FALSE)"
Range("A2").Select

Peux-tu m'aider?
Merci.

Réponse 2 / 7

mimi
14 déc. 2005 à 19:52

Merci beaucoup LUPIN mais j'ai une anomalie
j'avais une erreur de compilation (référence de variable incorrecte dans NEXT) : j'ai remplacé boucle par R

Maintenant la macro bloque sur la ligne
ActiveCell.Offset(0, 0).Value = Formule
Le message est "erreur d'exécution 1004" erreur définie par l'application ou par l'objet

Encore merci pour votre aide une seconde fois
Mimi

Dim Formule As String
Dim R As Integer

For R = 2 To 4
Formule = "=RECHERCHEV($g6;Feuil1!$A:$E;" & R & ";FAUX)"
ActiveCell.Offset(0, 0).Value = Formule
ActiveCell.Offset(1, 0).Select
Next R

Réponse 3 / 7

Utilisateur anonyme
9 mai 2009 à 02:21

Bonjour Iseult,

Le jardinnage est commencé et je délaisse mon pc pour ma spatule :-)
néanmoins, je veux bien tenter de t'aider si je peux.

Perso, je n'ai jamais vraiment tenter de lire un fichier sans l'ouvrir,
j'ai vu des exemples sur ExceLabo sur une méthode pour lire des
fichier sans les ouvrirs, mais pour ma part je considère qu'il y a
quand même une ouverture indirect.

Ceci dit, dépose le code que tu tente d'assembler et si ce n'est
pas moi, peut-être qu'un d'autre pourra te guider :-)

Lupin

Réponse 4 / 7

Lupin
14 déc. 2005 à 18:44

Salut,

'=RECHERCHEV($G6;Feuil1!$A:$E;2;FAUX)

Dim Formule As String
Dim R As Integer

For R = 2 to 4
  Formule = "=RECHERCHEV($G6;Feuil1!$A:$E;" & R & ";FAUX) 
"
  Activecell.offset(0,0).Value = Formule
  Activecell.offset(1,0).Select
Next Boucle

Lupin

Marie
20 avril 2010 à 09:43

Bonjour
je viens de voir votre réponse et j'aimerai savoir à quoi cela sert? merci d 'avance

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 7

Lupin
14 déc. 2005 à 21:07

re :

Sub Test()
    Dim Formule As String
    Dim R As Integer
    
    For R = 2 To 4
        Formule = "=VLOOKUP($G6,Feuil1!$A:$E," & R & ",FAUX)"
        ActiveCell.Offset(0, 0).Value = Formule
        ActiveCell.Offset(1, 0).Select
    Next R
    
End Sub

Lupin

Mimi
14 déc. 2005 à 22:02

Merci beaucoup LUPIN : la macro fonctionne correctement maintenant (j'ai mis FALSE à la place de FAUX car j'avais comme résultat #NOM?)

For R = 2 To 4
Formule = "=VLOOKUP($G6,Feuil1!$A:$E," & R & ",FALSE)"
ActiveCell.Offset(0, 0).Value = Formule
ActiveCell.Offset(1, 0).Select
Next R

Bonne soirée à tous

Mimi

Michel_ja Messages postés 10 Date d'inscription jeudi 12 juin 2008 Statut Membre Dernière intervention 21 janvier 2015
30 nov. 2008 à 11:45

Bonjour Lupin, je me permet de t'écrire car j'ai un problème semblable. J'ai une macro avec une boucle For Next et j'ai une erreur "référence de variable de contrôle incorrect dans Next". Voici le code de la sub, j'espère que tu pourrais m'aider : MERCI D'AVANCE

Sub PlanningMarque()

Dim Marque As String
Dim Compteur As Double
Dim Compteur2 As Double
Dim Model As String
Dim Segment As String
Dim BodyType As String
Dim Temps As String

'Initiatlisation des variables
Compteur = -3 'colonne Concatener
Compteur2 = -2 'colonne Transfert
Compteur3 = -5
Compteur4 = -4

For i = 5 To 40 'i = Lignes
For j = 6 To 31 'j = Colonnes

' traitement des variables
Marque = Cells(2, j).Value
Temps = Cells(1, j).Value
Segment = Cells(i, 1).Value
BodyType = Cells(i, 2).Value

Cells(i, j).Offset(i, Compteur).Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(Segment, BodyType, Marque, Temps)"
Cells(i, j).Offset(i, Compteur2).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Tableau!R6C1:R213C13, 6, 'False')"
Model = Cells(i, 4).Value
Cells(i, j).Select

If Cells(4, j) Is Empty Then
Cells(i, j).Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(Model, BodyType, Marque, Temps)"

ElseIf Cells(4, j) Is Not Empty Then
Cells(L, C).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(Model,Tableau!R6C1:R213C13, 6, 'False')"

End If

Next i
Compteur = Compteur - 1
Compteur2 = Compteur2 - 1

Next j

End Sub

Réponse 6 / 7

Utilisateur anonyme
5 janv. 2009 à 13:55

Bonjour tindette,

ActiveCell.FormulaR1C1 = "=VLOOKUP(ancien!C:C[1],2,FALSE)"

Dans cet instruction, le mot [ ancien ] représente une variable, mais
celle-ci étant entre guillemets, elle n'est pas interprèté.

Revenons d'abord à l'instruction :

ancien = Sheets(Worksheets("Menu").Range("A4").Value)

Ici [ ancien ] sous-entend de façon implicite [ ancien.Name ],
je te recommande de toujours être explicite, moins d'erreur
lors de la compilation.

d'où l'instruction explicite s'écrit comme suit :

ancien.Name = Sheets(Worksheets("Menu").Range("A4").Value)

Maintenant pour l'insertion de la formule.

ActiveCell.FormulaR1C1 = "=VLOOKUP(ancien!C:C[1],2,FALSE)"

doit être décomposé ainsi :

ActiveCell.FormulaR1C1 = "=VLOOKUP(" & ancien.Name & "!C:C[1],2,FALSE)"

Pour ma part, lorsque je construit une formule par programmation, je la pousse
d'abord dans un variable pour en voir le contenu et en vérifier la syntaxe avec
le déboggeur.

De plus je rencontre souvent ce type de manoeuvre pour insérer une formule
en utilisant le propriété [ FormulaR1C1 ] au lieu de [ Value ].

La propriété [ FormulaR1C1 ] est une propriété permettant de lire la formule
dans une cellule plutôt que sa valeur.

Option Explicit

Sub Test()

    Dim Formule As String
    Dim Valeur As Variant
    
    ' Insertion de la formule
    Formule = "=SUM(A2:A5)"
    ActiveCell.Value = Formule
    
    'Maintenant si tu veux lire la valeur
    Valeur = ActiveCell.Value
    
    ' Et si tu veux lire la formule
    Formule = ""
    Formule = ActiveCell.FormulaR1C1
    
    
    MsgBox ActiveCell.Value
    MsgBox ActiveCell.FormulaR1C1
    
End Sub
'

Mais attention, ce n'est pas une obligation de travailler de cette façon.

Bonne année 2009

Lupin

tindette
5 janv. 2009 à 15:09

Merci, cela fonctionne, mais pas avec nouveau.Name et ancien.Name pour affecter le contenu de la cellule.

Voici le programme (en plus mon fichier Excel qui avait planté a pu se rouvrir), je vois le résultat qui est cela attendu et derrière, le fichier Excel plante de nouveau et ferme Excel. Je ne sais pas si c'est bien normal.

Sub Macro4()
' déclare les variables qui seront utilisées en nom d'onglet
Dim nouveau As Worksheet
Dim ancien As Worksheet
Dim x As Object
' affecte les valeurs des cellules de l'onglet menu aux variables
Set nouveau = Sheets(Worksheets("Menu").Range("A2").Value)
Set ancien = Sheets(Worksheets("Menu").Range("A4").Value)
' affiche les noms des variables ainsi définies
MsgBox nouveau.Name
MsgBox ancien.Name
'sélectionne le nouvel onglet créé
nouveau.Select
Range("B2").Select
'tente de faire une recherche verticale en désignant l'onglet ancien
'(à partir de la variable)
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1]," & ancien.Name & "!C[-1]:C,2,FALSE)"
' colle la formule sur toute la colonne B jusqu'à la ligne 28000
Selection.AutoFill Destination:=Range("B2:B28000"), Type:=xlFillDefault
Range("B2:B28").Select
' sélectionne l'onglet ancien
ancien.Select
Range("B2").Select
Set nouveau = Nothing
Set ancien = Nothing
End Sub

tindette > tindette
5 janv. 2009 à 15:15

Le plantage, j'ai trouvé, c'est à cause du 28000, ça mouline longtemps pour recalculer.
En fait à la place de 28000, je voudrais mettre le N° de la dernière ligne contenant une valeur dans la colonne A, mais je ne sais pas comment faire. Je vais chercher. En attendant, je mets 5000, cela couvre tous mes cas de fichiers et rame moins.

tindette > tindette
5 janv. 2009 à 15:25

J'ai trouvé !

Voici le programme qui marche avec les commentaires! Merci pour ta réponse!

Sub Macro4()
' déclare les variables qui seront utilisées en nom d'onglet
Dim nouveau As Worksheet
Dim ancien As Worksheet
Dim DLigne As Variant
' affecte les valeurs des cellules de l'onglet menu aux variables
Set nouveau = Sheets(Worksheets("Menu").Range("A2").Value)
Set ancien = Sheets(Worksheets("Menu").Range("A4").Value)
' recherche et enregistre le N° de la dernière ligne de la colonne A
DLigne = Range("A1").End(xlDown).Address
DLigne = Range(DLigne).Row
' affiche les noms des variables ainsi définies
MsgBox nouveau.Name
MsgBox ancien.Name
'sélectionne le nouvel onglet créé
nouveau.Select
Range("B2").Select
'fait une recherche verticale du contenu de la colonne N-1 de cette ligne
' dans l'onglet ancien (à partir de la variable) et renvoie la colonne 2 de la zone sélectionnée
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1]," & ancien.Name & "!C[-1]:C,2,FALSE)"
' colle la formule sur toute la colonne B jusqu'à la dernière ligne de la colonne A
Selection.AutoFill Destination:=Range("B2:B" & DLigne), Type:=xlFillDefault
Range("B2:B28").Select
' sélectionne l'onglet ancien
ancien.Select
Range("B2").Select
Set nouveau = Nothing
Set ancien = Nothing
Set DLigne = Nothing
End Sub

Réponse 7 / 7

Utilisateur anonyme
5 janv. 2009 à 15:38

re:

désolé j'avais omis l'instruction [ SET ] !

Bonne continuité

Lupin

iseult
7 mai 2009 à 05:52

Bonjour Lupin,

Je vois que tu es bien calé sur le sujet.
Ma question concerne la recherchev,
en fait, j'aimerais faire un recherchev sur un fichier Excel sans l'ouvir.

J'ai trouver les ligne pour aller chercher des données vers un fichier non ouvert (.csv exactement)
de même j'ai trouver ton code pour le vlookup mais je n'arrvie pas à compiler les 2.
J'avoue je ne suis vraiment pas une experte en VB.

Pourrais tu m'aider si tu en as le temps.

Merci d'avance

Iseult

Discussions similaires

Tréma sur "i" minuscule

Bouton ON OFF, O ou I ?

"L'indice n'appartient à la sélection" + VBA

Comment faire un i tréma

''e'' et ''i'' Tréma

Discussions similaires

Newsletters