Comment parcourir les lignes d'un tableau dans un Vlookup

[Amstrad]

Bonsoir,

Debutant sur VBA Excel, j'envisage de réaliser une refacturation téléphonique (fixe et GSM) au sein des différentes directions. Ceci afin d'eviter de multiples actions (copie de feuilles, de colonnes, recherchev etc..) longues et ennuyeuses.
Je commence par le fixe.
Concrètement, nous recevons un fichier brut de notre opérateur téléphonique qui est ensuite mis en forme. Au final, pour chaque direction, il y aura un fichier avec l'ensemble des utilisateurs et leur montant.

1) Tout d'abord, j'ai réalisé la macro suivante qui me permet d'obtenir une feuille avec un montant net pour chaque numéro. Celle ci détaille les appels internes/vers mobiles/internationaux.
Même si le code doit vous sembler optimisable, j'avoue ne pas etre trop mécontent du résultat

Sub Factur_Fixe_02()
'

' Copie de la Feuille Opérateur sur une nouvelle feuille renommée Facturation
Cells.Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Facturation"
Sheets("Facturation").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Range("a:f,h:i,p:q").Delete Shift:=xlToLeft
Selection.Columns.AutoFit

ActiveSheet.Range("$A$1:$J$2000").AutoFilter Field:=3, Criteria1:="<>"
    Columns("A:A").Select
    ' Selection.Sort Key1:=Range("A1")
    
  ' Sous totaux par numéro de tel
    Application.DisplayAlerts = False
    Cells.Select
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7, 17), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Application.DisplayAlerts = True
    
    End Sub

2) C'est pour la suite que je bloque malgré plusieurs essais : En fait, je souhaite identifier le titulaire de la ligne.
Sur ce document, je devrais ajouter les colonnes "Matricule", "Nom", "Prénom", "Direction".
Ces infos se situent sur un tableau que l'on nomme Référentiel Téléphonique. Cette feuille est très "lourde" : Colonnes de A à AV pour 7000 lignes environ).

Prenons l'exemple pour l'ajout automatique du matricule.
Au sein de ce référentiel, la colonne Numéro de téléphone se trouve en S et le matricule en W.
Mon problème est de savoir comment parcourir les différentes lignes au sein de la fonction "WorksheetFunction.VLookup" ? (à la place des points d'interrogation dans le code ci dessous) :

Sub Identification()

Set mat = Sheets("feuil4").Range("S1:W1000")

For Boucle = 2 To 7100

Cells(Boucle, 2).Value = WorksheetFunction.VLookup(Range("A" [B][U]?????[/U][/B]).Value, mat, 5, False)
Next Boucle

End Sub

Oui, je sais ma boucle est enorme (7100)...

Merci pour votre aide, j'espere que je ne vous ai pas "perdu" en route

 

[camarchepas]

Re : Comment parcourir les lignes d'un tableau dans un Vlookup

Bonjour Amstrad,

Si le principe est juste de chercher le matricule dans une liste , une solution peut être
Voici un ptit bout de code pour essai , a adapter ensuite à ton cas pratique
Si le nom de feuille n'est pas le bon , modifier par le nom de la feuille contenant la liste des matricules

Sub Recherche()
'Recherche si Matricule renseigné

Dim Matricule As String
Dim Trouve As Range
Element = InputBox("Entrer le matricule à vérifier", "ATTENTE REPONSE UTILISATEUR")
Set Trouve = Sheets("Feuil4").Range("A:A").Find(Element, lookat:=xlWhole)
If Not Trouve Is Nothing Then
' Trouvé
  msgbox "Trouvé en ligne :" &  Trouve.Row
 Else
' Non trouvé dans la base
  MsgBox "Ce code de fiche d'intervention n'est pas connu dans la base", vbCritical, "PAS DE RETOUR POUR CETTE RECHERCHE"
End If
End Sub

 

[Amstrad]

Re : Comment parcourir les lignes d'un tableau dans un Vlookup

Bonjour Amstrad,

Si le principe est juste de chercher le matricule dans une liste , un solution peut être
Voici un ptit bout de code pour essai , a adapter ensuite à ton cas pratique
Si le nom de feuille n'est pas le bon , modifier par le nom de la feuille contenant la liste des matricules

Sub Recherche()
'Recherche si Matricule renseigné

Dim Matricule As String
Dim Trouve As Range
Element = InputBox("Entrer le matricule à vérifier", "ATTENTE REPONSE UTILISATEUR")
Set Trouve = Sheets("Feuil4").Range("A:A").Find(Element, lookat:=xlWhole)
If Not Trouve Is Nothing Then
' Trouvé
  msgbox "Trouvé en ligne :" &  Trouve.Row
 Else
' Non trouvé dans la base
  MsgBox "Ce code de fiche d'intervention n'est pas connu dans la base", vbCritical, "PAS DE RETOUR POUR CETTE RECHERCHE"
End If
End Sub

Merci camarchepas, en cette période, j'ai cru que ma demande n'aurait aucune réponse .
En fait, je pense que je dois utiliser l'équivalent d'un recherchev. Du coup, j'ai lu que le find n'était pas approprié. Contrairement au vlookup.

Pour résumer, j'ai dans un tableau, une colonne de numéros de téléphones (7000 lignes).
Pour chacun des numéros,je dois affecter un matricule, un nom, un prénom et une direction.
Pour chacun des derniers champs, il y a une colonne dédiée sur un autre fichier.

C'est pour cela que j'essaye de finaliser ma "boucle".
Meme si je trouve cela redondant de faire une boucle par type de champ recherché (matricule, nom, prenom, direction)....:-/

Merci

 

[camarchepas]

Re : Comment parcourir les lignes d'un tableau dans un Vlookup

Amstrad,

Moi c'était plutot Commodore , mais bon c'est pour le clin d'oeil.

Voici donc le code pour VLookup : ATTENTION , il faut que tes zones soient bien définies.

Je pense qu'il serait bon de préciser la feuille sur laquele tu travailles, car si c'est une autre qui est sélectionnée, risque d'erreur. Enfin tout dépent du contexte d'utilisation

Option Explicit

Sub Identification()
Dim Mat As Range
Dim Boucle As Long

Set Mat = Sheets("feuil4").Range("S1:W1000")
 For Boucle = 2 To 7100
  Cells(Boucle, 2).Value = WorksheetFunction.VLookup(Range("A" & Boucle).Value, Mat, 5, False)
 Next Boucle

 End Sub

 

[Amstrad]

Re : Comment parcourir les lignes d'un tableau dans un Vlookup

Amstrad,

Moi c'était plutot Commodore , mais bon c'est pour le clin d'oeil.

Voici donc le code pour VLookup : ATTENTION , il faut que tes zones soient bien définies.

Je pense qu'il serait bon de préciser la feuille sur laquele tu travailles, car si c'est une autre qui est sélectionnée, risque d'erreur. Enfin tout dépent du contexte d'utilisation

Option Explicit

Sub Identification()
Dim Mat As Range
Dim Boucle As Long

Set Mat = Sheets("feuil4").Range("S1:W1000")
 For Boucle = 2 To 7100
  Cells(Boucle, 2).Value = WorksheetFunction.VLookup(Range("A" & Boucle).Value, Mat, 5, False)
 Next Boucle

 End Sub

Merci camarchepas.
J'avais deja testé le "(Range("A" & Boucle).Value" dans le vlookup et j'avais eu une erreur (une 1004 de mémoire).
J'avais donc ensuite testé de mettre une cellule en dur à la place du " & boucle" pour cibler le problème : le test OK mais bien sur cetait un seul matricule ki s'affichait.

Tu conseilles de "préciser la feuille sur laquelle je travaille" : le fait de specifier la feuille dans l'objet Mat ne repond à pas à ce problème ?

Merci

 

[camarchepas]

Re : Comment parcourir les lignes d'un tableau dans un Vlookup

Amstrad ,

Je ne t'es pas envoyé ce code sans l'avoir testé , et dans mon fichier de test cela fonctionne.

Si tu mettais un exemple de ton fichier, au moins au niveau de la forme et du format des données en enlevant bien sur tous ce qui pourrait être confidentiel je pourrais vérifier ce qui cloche, car sinon , je pense qu'en 2014 , ça ne tournera toujours pas et ce juste pour un problème d'adaptation

 

[Amstrad]

Re : Comment parcourir les lignes d'un tableau dans un Vlookup

Amstrad ,

Je ne t'es pas envoyé ce code sans l'avoir testé , et dans mon fichier de test cela fonctionne.

Si tu mettais un exemple de ton fichier, au moins au niveau de la forme et du format des données en enlevant bien sur tous ce qui pourrait être confidentiel je pourrais vérifier ce qui cloche, car sinon , je pense qu'en 2014 , ça ne tournera toujours pas et ce juste pour un problème d'adaptation

En effet, avec un nouveau fichier de tests ou je saisis au hasard des numéros de tel dans la colonne A (avec le référentiel tel comme fichier source), cela fonctionne.
Par contre, si je fais un copier coller de numéros de tel issus de mon fichier généré (cf etape 1), cela ne fonctionne pas : les cellules restent vides là ou j'attends des matricules. Pourtant, le format des cellules des numéros est le meme "standard"..
Si je ne trouve pas d'ici cette nuit, j'ajouterais les fichiers après avoir supprimé les données confidentielles....:-/

Merci

 

[camarchepas]

Re : Comment parcourir les lignes d'un tableau dans un Vlookup

Bonjour ,

Je pense que l'on a affaire au cas classique du 0 à gauche qui disparait lors de la recopie dans une colonne non préparée.

Pour ce problème , il faut forcer le mode cellule de la colonne recevant les numéro de téléphone en mode texte, et faire un copié collé des valeurs de la colonne source.

Problème maintes fois rencontré sur un autre type d'info dans mon pro, ceci est donc devenu une habitude de travail.

 

[Amstrad]

Re : Comment parcourir les lignes d'un tableau dans un Vlookup

Bonjour ,

Je pense que l'on a affaire au cas classique du 0 à gauche qui disparait lors de la recopie dans une colonne non préparée.

Pour ce problème , il faut forcer le mode cellule de la colonne recevant les numéro de téléphone en mode texte, et faire un copié collé des valeurs de la colonne source.

Problème maintes fois rencontré sur un autre type d'info dans mon pro, ceci est donc devenu une habitude de travail.

Bonsoir,

Oui, j'avais découvert ce phénomène lors de la première etape (cf message initial). Le formatage de la colonne au préalable avait en effet permis de régler le problème.

Et là...je me triture la tête, cela ne fonctionne pas. Meme en mettant au préalable la colonne en texte, je ne récupère pas le matricule (colonne W de la feuille du référentiel sur la base du numéro du tel SDA -> colonne S).
C'est OK si je saisis le numéro manuellement mais sinon non : idem si je copie la feuille de resultat dans une autre feuille..

pffou j'ai la tete dans le sac! Ci joint le fichier Excel (modifié au niveau des infos confidentielles, j'ai également supprimé 7000 lignes environ ).
Si vous pouviez m'aider à trouver la soluce, vous m'enleveriez une sacré epine...

Pour résumer le process : à partir de la feuille "Facture Utilisateurs Nov2012", j'execute la macro "Facture_fixe_02"
J'obtiens alors la feuille "facture" qui détaille la facturation par numéro.
Et à partir de cette feuille, je lance la macro identification pour tenter de voir apparaitre dans la colonne H les matricules.
Sauf que je n'obtiens rien :|

Merci

Option Explicit

Sub Identification()
Dim Mat As Range
Dim Boucle As Long

Set Mat = Sheets("referentiel").Range("S1:W1000")

'Cells.Select
'Selection.Copy
'Sheets("feuil5").Select
'Columns("a:a").NumberFormat = "@"
'Selection.PasteSpecial Paste:=xlPasteValues

For Boucle = 2 To 20
  Cells(Boucle, 8).Value = WorksheetFunction.VLookup(Range("A" & Boucle).Value, Mat, 5, False)
  
 Next Boucle

 End Sub

 

[camarchepas]

Re : Comment parcourir les lignes d'un tableau dans un Vlookup

Bonjour Amstrad,

Bon , avec le jeu de données adéquate , j'ai pu cerner l'étendue du dégat .

Petite remarque :
la ligne: Option Explicit doit toujours être la premiére ligne du module

Et oui , mauvais typage de variable entre la référence et l'élément cherché puis dépacement de capacité lors du transtypage.

J'ai aussi amélioré la définition de fin de boucle.

Ah oui , si tu veux en commentaire la version avec la fonction Find . (ça pourra toujours servir)

Option Explicit
Sub Factur_Fixe_02()
'
' Facturation Fixe V.02

' Copie de la Feuille active sur une nouvelle feuille renommée Facturation
Cells.Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Facturation"
Sheets("Facturation").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Range("a:f,h:i,p:q").Delete Shift:=xlToLeft
Selection.Columns.AutoFit

ActiveSheet.Range("$A$1:$J$2000").AutoFilter Field:=3, Criteria1:="<>"
    Columns("A:A").Select
    ' Selection.Sort Key1:=Range("A1")
    
  ' Sous totaux par numéro de tel
    Application.DisplayAlerts = False
    Cells.Select
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7, 17), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Application.DisplayAlerts = True
    
   
End Sub

Sub Identification()
Dim Mat As Range
Dim Boucle As Long, LigneFin As Long
Dim Lecture As String

Set Mat = Sheets("referentiel").Range("S1:W1000")

'Cells.Select
'Selection.Copy
'Sheets("feuil5").Select
'Columns("a:a").NumberFormat = "@"
'Selection.PasteSpecial Paste:=xlPasteValues
 
 With Sheets("Facturation")
  LigneFin = .Range("A" & Rows.Count).End(xlUp).Row
  .Columns("H:H").NumberFormat = "@"
  For Boucle = 2 To LigneFin - 1
    Lecture = .Range("A" & Boucle)
    If InStr(1, Lecture, "Total ") > 0 Then Lecture = Replace(Lecture, "Total ", "")
    .Cells(Boucle, 8) = WorksheetFunction.VLookup(CDbl(Lecture), Mat, 5, False)
    '.Cells(Boucle, 8) = Mat.Find(CDbl(Lecture), lookat:=xlWhole).Offset(0, 4)
  Next Boucle
 End With
End Sub

 

[Amstrad]

Re : Comment parcourir les lignes d'un tableau dans un Vlookup

Super, merci à toi camarche pas! Bien sympa de ta part.
Tout content, j'ai pu ajouter egalement les Nom, Prenom, Section et Direction... sur le fichier Test joint dans ce topic.

Le problème est que lorsque je teste avec le fichier d'origine, j'ai l'erreur "Erreur d'execution 1004 Impossible de lire la propriété Vlookup de la classe Worksheetfunction "qui apparait au moment de lancer la procedure Identification à partir de la feuille Facturation. C'est la ligne " .Cells(Boucle, 8) = WorksheetFunction.VLookup(CDbl(Lecture), Mat, 5, False)" qui semble poser problème...
J'ai l'impression qu'il s'agit encore un d'un pb de format de données. Car si je resaissis à la mano le numéro dans la colonne A de "Facturation" et le meme numéro dans la feuille Référentiel, cela semble fonctionner.
Outre la modif de la macro, peux tu m'indiquer si tu avais fait une manip sur le fichier Excel en lui meme (au niveau du format des colonnes) ?

Merci, désolé pour le derangement :-/

 

[camarchepas]

Re : Comment parcourir les lignes d'un tableau dans un Vlookup

Bonjour ,

Non , aucune modification de colonne , sinon c'est pas du jeu , ...

2 choses à vérifier :
le numéro recherché est bien dans la feuille référentiel
la ligne : Set Mat = Sheets("referentiel").Range("S1:W1000") doit englober l'ensemble des lignes de la feuille referentiel

A quelle ligne est situé le numéro recherché dans referentiel ?

Sur le format ,du numéro dans referentiel, est-il possible d'avoir cette colonne en texte comme cela pas de transtypage.

l'on pourrait sinon au pire créer une colonne supplémentaire construite pas le vba au format désiré ....

 

[Amstrad]

Re : Comment parcourir les lignes d'un tableau dans un Vlookup

Bonjour ,

Non , aucune modification de colonne , sinon c'est pas du jeu , ...

2 choses à vérifier :
le numéro recherché est bien dans la feuille référentiel
la ligne : Set Mat = Sheets("referentiel").Range("S1:W1000") doit englober l'ensemble des lignes de la feuille referentiel

A quelle ligne est situé le numéro recherché dans referentiel ?

Sur le format ,du numéro dans referentiel, est-il possible d'avoir cette colonne en texte comme cela pas de transtypage.

l'on pourrait sinon au pire créer une colonne supplémentaire construite pas le vba au format désiré ....

Bonsoir,

J'ai fais plusieurs tests :
- préparation de la colonne AW au format Standard puis Special et Text (3 Tests) via les Menus Excel puis copier coller (valeurs) de la colonne S du Référentiel [Num Tel] sur ladite colonne AW. (en AX, j'avais alors copié les matricules)
Situation idem.
- Test idem mais via la procédure suivante :

Sub copie_coll()

 Sheets("referentiel").Select
 Columns("AX:AX").NumberFormat = "General" 'test effectué egalement avec Texte
    Columns("S:S").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Columns("AW:AW").Select
    Selection.PasteSpecial Paste:=xlPasteValues
End Sub

- Si au sein d'une colonne Telephone (celle ki a été copié coller ou celle d'origine ->S), je recopie le numéro au sein de la cellule et que je lance la procedure identification, la cela fonctionne......

Suis assez surpris qu'il y a autant d'embuches pour traduire une recherchev.......:|

P.S : pour répondre à tes questions, camarchepas:
- les numéros recherchés figurent bien dans la feuille référentiel (les numéros recherchés figurent aux lignes 300, 700 etc..)
- J'ai meme elargi la plage à Range("S1:W12000")

 

[Amstrad]

Re : Comment parcourir les lignes d'un tableau dans un Vlookup

En enlevant la fonction CDlb du VLookup, cela a l'air de mieux fonctiionner !

 

[Amstrad]

Re : Comment parcourir les lignes d'un tableau dans un Vlookup

Pour terminer, est ce que qqun pourrait m'indiquer comment on gère les N/A avec vlookup ?

En plus des matricules, je souhaite ajouté d'autres champs : nom, prenom, section et direction:

.Cells(Boucle, 8) = WorksheetFunction.VLookup(Lecture, Mat, 5, False)
.Cells(Boucle, 9) = WorksheetFunction.VLookup(Lecture, Mat, 8, False)
.Cells(Boucle, 10) = WorksheetFunction.VLookup(Lecture, Mat, 7, False)
.Cells(Boucle, 11) = WorksheetFunction.VLookup(Lecture, Mat, 9, False)

Par contre, à la source, ces colonne possèdent des N/A qui font stopper logiquement le vlookup.
Sur un site anglais, j'ai vu qu'ils proposaient cette exemple : =IF(COUNTIF(A1:A100,"Dog"),VLOOKUP("Dog",A1100,2,FALSE),0)
Mais je ne vois pas comment je peux l'integrer dans mon code.