Recherche V VBA
Bonjour à tous,
Tout nouveau sur le forum, et novice en vba, je viens ici exposer mon problème. Bien que j'ai déjà trouvé un début de réponse sur un autre fil de discussion, notamment sur le code de base, je bloque sur son développement pour qu'il fonctionne avec mon cas :
J'ai un fichier excel qui est un fichier pdf convertit (fiches de salaire). Cette conversion affecte l'emplacement des données sur chaque feuille qui peuvent être déplacées d'une ou deux colonnes (rarement plus) et/ou une ou deux lignes. Je souhaite effectuer une recherche v d'une valeur cible : exemple Salaire horaire (cf fichier joint) et que la formule me donne la correspondance de cette valeur pour chaque feuille.
Voici le code de base que j'ai trouvé :
Sub test()
With Sheets("Test")
.Range("B1").Value = WorksheetFunction.VLookup(.Range("A1").Value, Sheets("Feuil2").Range("A1:AA100"), 2, False)
End With
End Sub
Il fonctionne très bien avec une feuille, mais comment l'adapter pour qu'il fonctionne sur chaque feuille et avec des données rarement à la même place, sans avoir à écrire un code pour chaque feuille?
De plus la valeur cible peut avoir une écriture différente, toujours à cause de cette conversion... Dans mon exemple, salaire horaire peut se retrouver écrit : "salaire horaire", "Salaire Horaire", "salaireHoraire" etc...
Pour compléter, je joins un fichier excel type sur lequel j'ai travaillé. Pour des raisons de confidentialité, j'ai enlevé les infos importantes, s'agissant de bulletins de salaires. Je n'ai laissé que 6 feuilles mais ce sont des fichiers d'en moyenne 150 et ça peut aller jusqu'à plusieurs milliers (pour des très grosses entreprises).
Dans l'attente de votre retour, merci d'avance,
Benjamin
bonsoir,
un proposition
Sub test()
' wst = feuille de synthèse
Set wst = Worksheets("test")
i = 0
' on parcourt chacune des fiches
For Each ws In Worksheets
' si le nom de la feuille est <> de la feuille de synthèse
If ws.Name <> wst.Name Then
' dlw dernière ligne sur la fiche
dlw = ws.Range("a" & Rows.Count).End(xlUp).Row
' on boucle sur les écriture possibles de salaire horaire
For Each sa In Array("Salaire Horaire", "Salaire Horaire", "SalaireHoraire")
' recherche dans la fiche
Set re = ws.Range("c1:A" & dlw).Find(sa, lookat:=xlPart, MatchCase:=False)
If Not re Is Nothing Then
'si trouvé on incrémente compteur de ligne
i = i + 1
' on met le nom de la feuille en colonne 1
wst.Cells(i, 1) = ws.Name
' on met le salaire horaire trouvé en colonne 2
If re.Offset(0, 14) <> "" Then wst.Cells(i, 2) = re.Offset(0, 14) Else wst.Cells(i, 2) = re.Offset(0, 15)
' on a trouvé une occurrence de salaire horaire, inutile de chercher les autres sur la même fiche on sort de la boucle
Exit For
End If
Next
End If
Next
Set re = Nothing
Set wst = Nothing
End SubMerci pour votre réponse rapide! Je n'ai pas encore eu le temps de tester le code, je pourrais le faire en fin de journée ou demain. Je vous tiens au courant.
Merci pour votre réponse rapide! Je n'ai pas encore eu le temps de tester le code, je pourrais le faire en fin de journée ou demain. Je vous tiens au courant.
Bonjour H2so4,
Ta proposition fonctionne très bien, c'est bien cool!
wst.Cells(i, 1) = Ws.Name
par wst.Cells(i, 1) = une recherchev style vlookup
sachant que le nom de l'employé est toujours sur la même ligne mais pas forcément sur la même colonne?
Par avance merci!
Bonjour H2so4,
J'aurai encore besoin de tes conseils avisés! J'ai appliqué ton code à d'autres recherches (heures complémentaires, allègement fillon...). Le problème est que ce sont des éléments que ne se trouvent pas forcément sur chaque fiche de salaire et lorsque j'exécute le code, il me met la réponse trouvée dans la 1ère cellule et non dans la cellule correspondant à la feuille. Je te remets l'exemple en fichier joint, ce sera plus parlant.
Autre question, pour éviter de faire "exécuter" chaque macro, peut-on condenser les codes, à la place d'utiliser la fonction call?
Merci d'avance,
Benjamin
Bonsoir,
un code qui permet de récupérer les différentes info sur la feuille
Option Base 1
Sub test()
' wst = feuille de synthèse
Set wst = Worksheets("test")
r1 = Array(11, 200, 2100, 6350) ' code à sélectionner en colonne A:B
r2 = Array(13, 13, 15, 20) ' si code trouvé 1ère colonne où chercher l'info
r3 = Array(1, 1, -1, 1) ' si pas trouvé dans 1ère colonne,position relative d'une autre colonne où chercher
i = 0
' on parcourt chacune des fiches
For Each ws In Worksheets
' si le nom de la feuille est <> de la feuille de synthèse
If ws.Name <> wst.Name Then
' dlw dernière ligne sur la fiche
dlw = ws.Range("a" & Rows.Count).End(xlUp).Row
i = i + 1
' on met le nom de l'employé en colonne 1
If ws.Range("O7") <> "" Then wst.Cells(i, 1) = ws.Range("O7") Else wst.Cells(i, 1) = ws.Range("N7")
' on boucle sur les codes à sélectionner
For j = 1 To UBound(r1, 1)
' recherche dans la fiche
Set re = ws.Range("B1:A" & dlw).Find(r1(j), lookat:=xlWhole, MatchCase:=False)
If Not re Is Nothing Then
' on met l'info correspondant au code(j) dans la colonne j+1
If ws.Cells(re.Row, r2(j)) <> "" Then wst.Cells(i, j + 1) = ws.Cells(re.Row, r2(j)) Else wst.Cells(i, j + 1) = ws.Cells(re.Row, r2(j) + r3(j))
End If
Next
End If
Next
Set re = Nothing
Set wst = Nothing
End SubBonjour,
Merci pour ta réactivité! Ton code fonctionne très bien mais je suis toujours confronté au même problème, à savoir que même en spécifiant, comme tu l'as fait, que l'on veut qu'il trouve la valeur exacte avec xlWhole, pour la valeur 200, il me donne comme résultat ce qui correspond à 200 mais aussi à 2200...comment faire?
Bonsoir,
avec ton fichier de test, je n'ai pas vu ce problème.
voici une version adaptée à tester.
Option Base 1
Sub test()
' wst = feuille de synthèse
Set wst = Worksheets("test")
r1 = Array(11, 200, 2100, 6350) ' code à sélectionner en colonne A:B
r2 = Array(13, 13, 15, 20) ' si code trouvé 1ère colonne où chercher l'info
r3 = Array(1, 1, -1, 1) ' si pas trouvé dans 1ère colonne,position relative d'une autre colonne où chercher
i = 0
' on parcourt chacune des fiches
For Each ws In Worksheets
' si le nom de la feuille est <> de la feuille de synthèse
If ws.Name <> wst.Name Then
' dlw dernière ligne sur la fiche
dlw = ws.Range("a" & Rows.Count).End(xlUp).Row
i = i + 1
' on met le nom de l'employé en colonne 1
If ws.Range("O7") <> "" Then wst.Cells(i, 1) = ws.Range("O7") Else wst.Cells(i, 1) = ws.Range("N7")
' on boucle sur les codes à sélectionner
For j = 1 To UBound(r1, 1)
' recherche dans la fiche
Set re = ws.Range("B1:A" & dlw).Find(r1(j), lookat:=xlWhole, MatchCase:=False)
If Not re Is Nothing Then
If re.Value = r1(j) Then
' on met l'info correspondant au code(j) dans la colonne j+1
If ws.Cells(re.Row, r2(j)) <> "" Then wst.Cells(i, j + 1) = ws.Cells(re.Row, r2(j)) Else wst.Cells(i, j + 1) = ws.Cells(re.Row, r2(j) + r3(j))
End If
End If
Next
End If
Next
Set re = Nothing
Set wst = Nothing
End SubBonjour,
Merci de te décarcasser pour moi! Malheureusement, j'ai toujours le même problème. La valeur cherchée 200 (Heures complémentaires) ne se trouve qu'en "feuil5" dans mon exemple. Or, ce code me renvoie aussi les valeurs de 2200. En toute logique, il ne devrait y avoir en colonne C que la valeur trouvée 7,07 (correspondant à 200 Heures complémentaires)...
C'est pas simple tout ça!
Bonsoir
voici un code adapté, dans lequel j'ai mis une option de traçage de l'information, si elle activée (instructione traceon=true pour l'activer, traceon=false pour la désactiver), on affiche la rubrique, la valeur trouvée et la colonne dans laquelle la valeur a été trouvée. Ceci permet de vérifier si les paramètres R1,R2 et R3 sont corrects.
ces paramètres permettent de sélectionner les infos que tu veux voir affichées dans ta feuille test.
exemple code 11 si trouvé on cherche en colonne 13 l'info, si on ne la trouve pas on prend ce qu'on trouve dans la colonne immédiatement à droite 1 (donc 14)
de même pour
200,16,1
2100,15,-1 (immédiatement à gauche donc 14)
6350,20,1
Option Base 1
Sub test()
' paramètres
traceon = True 'on active le traçage
r1 = Array(11, 200, 2100, 6350) 'listes des codes à sélectionner en colonne A:B
r2 = Array(13, 16, 15, 20) ' si code trouvé 1ère colonne où chercher l'info
r3 = Array(1, 1, -1, 1) ' si pas trouvé dans 1ère colonne,position relative d'une autre colonne où chercher
' wst = feuille de synthèse
Set wst = Worksheets("test")
i = 0
' on parcourt chacune des fiches
For Each ws In Worksheets
' si le nom de la feuille est <> de la feuille de synthèse
If ws.Name <> wst.Name Then
' dlw dernière ligne sur la fiche
dlw = ws.Range("a" & Rows.Count).End(xlUp).Row
i = i + 1
' on met le nom de l'employé en colonne 1
If ws.Range("O7") <> "" Then wst.Cells(i, 1) = ws.Range("O7") Else wst.Cells(i, 1) = ws.Range("N7")
' on boucle sur les codes à sélectionner
For j = 1 To UBound(r1, 1)
' recherche dans la fiche
Set re = ws.Range("B1:A" & dlw).Find(r1(j), lookat:=xlWhole, MatchCase:=False)
If Not re Is Nothing Then
' on met l'info correspondant au code(j) dans la colonne j+1
If ws.Cells(re.Row, r2(j)) <> "" Then
wst.Cells(i, j + 1) = ws.Cells(re.Row, r2(j))
ad = ws.Cells(re.Row, r2(j)).Address
Else
wst.Cells(i, j + 1) = ws.Cells(re.Row, r2(j) + r3(j))
ad = ws.Cells(re.Row, r2(j) + r3(j)).Address
End If
If traceon Then wst.Cells(i, j + 1) = re.Value & " - " & wst.Cells(i, j + 1) & "(" & ad & ")"
End If
Next
End If
Next
Set re = Nothing
Set wst = Nothing
End SubBonjour,
Ça fonctionne! Un grand merci! J'ai une dernière question. J'ai toujours ce même souci que, lorsque je transforme un pdf en excel, comme tu le sais, il peut y avoir un décalage d'une ou deux colonnes selon les feuilles. Dans mon exemple, toutes les infos en colonne C sur la Feuil2 (salaire horaire, heures majorées, etc) se trouvent en colonne B sur la Feuil3, ce qui décale de fait toutes les infos correspondantes à droite de ces colonnes (je sais pas si cette phrase est super claire, en tout cas surement trop longue!). Bref. Y'a t'il un code pour uniformiser toutes les feuilles? Qu'on retrouve les mêmes infos dans les mêmes colonnes?
Merci à toi
bonsoir,
un essai
Sub reformat()
Set wst = Worksheets("test")
For Each ws In Worksheets
If ws.Name <> wst.Name Then
dlw = ws.Range("a" & Rows.Count).End(xlUp).Row
Set re = ws.Range("B1:A" & dlw).Find(11, lookat:=xlWhole, MatchCase:=False)
If Not re Is Nothing Then
If re.Column = 2 Then
cl = "B"
ElseIf re.Offset(-1, 14) = "Taux" Then
cl = "N"
Else
cl = ""
End If
If cl <> "" Then
For Each c In ws.Range(cl & re.Row & ":" & cl & dlw)
If c <> "" And c.Offset(0, -1) = "" Then c.Offset(0, -1) = c
Next
ws.Range(cl & re.Row - 2 & ":" & cl & dlw).Delete shift:=xlToLeft
End If
End If
End If
Next
End SubNickel!
Un grand merci pour ta disponibilité!! Tu as fais une formation VBA ou tu as appris par toi même? Parce que je compte bien m'y mettre, histoire de comprendre ce qui est écrit et pouvoir faire des codes par moi même!
ponpon a écrit :Nickel!
(...) Tu as fais une formation VBA ou tu as appris par toi même? (...)
pour répondre à ta question, je suis un autodidacte.