[VBA] RechercheV sur des tableaux de taille variable
Bonjour,
Dans le fichier joint, il y a déjà des macros, j'avance dans mon travail au fur est à mesure.
Là je bloque sur un point (voir macro Action à mener dans Module 1)
Le but est dans la feuille S, insérer une colonne, la redimensionner, mettre un titre (j'ai fait ça avec l'enregistreur de macro, le code est sûrement améliorable, je suis preneur des solutions)
La suite est impossible avec l'enregistreur et donc je bloque.
Dans la colonne I (Action à mener) de la feuille "S", je veux aller chercher les commentaires de la colonne I de la feuille "S-1". J'ai pensé à la fonction RECHERCHEV, mais comment faire avec l'argument matrice, sachant que le tableau de la feuille "S-1" aura un nombre de ligne variable tous les jours ?
Ensuite, il faut dupliquer cette RECHERCHEV (ou une autre solution) sur toutes les lignes de la colonne I de la feuille "S"
Merci pour vos aides précieuses
Cordialement,
Elfinols
bonjour,
si j'ai bien compris,
Edit salut Tauthème
Sub Import_feuille()
'
' Import de la feuille du fichier extract vers le fichier d'indicateurs
Sheets("owssvr").Copy Before:=Workbooks("Mise à jour QR.xlsm").Sheets(1)
Sheets("owssvr").Name = "S" 'l'onglet importé est renommé "S"
' Convertir la table en plage de cellule
ActiveWorkbook.Worksheets("S").ListObjects(1).Unlist
End Sub
Sub Mise_en_forme()
'
' Mise_en_forme de l'onglet "S"
'Définition de la largeur des colonnes
Columns("A:A").ColumnWidth = 4 ' ID
Columns("B:B").ColumnWidth = 9 ' Statut
Columns("C:C").ColumnWidth = 12 ' Référence Produit
Columns("D:D").ColumnWidth = 19 ' Libéllé Produit
Columns("E:E").ColumnWidth = 7 ' Indice
Columns("F:F").ColumnWidth = 14 ' Created By
Columns("G:G").ColumnWidth = 80 ' Question
Columns("H:H").ColumnWidth = 13 ' Impact Ligne
Columns("I:I").ColumnWidth = 11 ' N° Ticket
Columns("J:J").ColumnWidth = 14 ' Domaine concerné
Columns("K:K").ColumnWidth = 80 ' Réponse
With Columns("L:L") ' Date création (created) + format date courte
.NumberFormat = "m/d/yyyy"
.ColumnWidth = 12
End With
Columns("M:M").ColumnWidth = 16 ' Impact Produit
Columns("N:N").ColumnWidth = 12 ' Acteur du traitement
Columns("O:O").ColumnWidth = 10 ' Référence ST
Columns("P:P").ColumnWidth = 9 ' Demande Société
Columns("Q:Q").ColumnWidth = 9 ' Traitement société
Columns("R:R").ColumnWidth = 14 ' N° Commande
Columns("S:S").ColumnWidth = 12 ' Date de prise en compte
Columns("T:T").ColumnWidth = 12 ' Date de clôture
Columns("U:U").ColumnWidth = 36 ' Lien documentaire 1
Columns("V:V").ColumnWidth = 36 ' Lien documentaire 2
Columns("W:W").ColumnWidth = 8 ' Nb jours traitement
Columns("X:Y").Delete Shift:=xlToLeft
' Centrage horizontal et vertical des cellules, retour à la ligne auto et hauteur automatique des lignes
With Worksheets("S").UsedRange
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Rows.AutoFit
End With
End Sub
Sub Action_à_mener()
'
' Action_à_mener Macro
'
With Sheets("S")
.Columns("I:I").Insert Shift:=xlToRight
.Range("I1") = "Action à mener"
.Columns("I:I").ColumnWidth = 40
dltab = Sheets("s-1").Cells(Rows.Count, 1).End(xlUp).Row
tableau_owssvr = "'S-1'!$A$1:$I" & dltab
' Je suppose que l'on peut optimiser le code des 4 lignes ci-dessus
' dans la feuille "S" je veux insérer une colonne de taille 40, et mettre en titre "action à mener"
'Ensuite ça se complique, je veux dans cette colonne I faire une rechercheV sur le "tableau" de la feuille "S"
' qui est de taille variable, donc comment sélectionner l'argument matrice de la fonction rechercheV ?
dl = .Cells(Rows.Count, 1).End(xlUp).Row
.Columns("I:I").NumberFormat = "general"
.Range("I2:I" & dl).Formula = "=vlookup(A2," & tableau_owssvr & ",9,false)"
End With
End Sub
Bonjour Elfinols, bonjour le forum,
La règle d'or en VBA c'est d'éviter autant que possible les Select. L'enregistreur de macro c'est idéal quand on n'a aucune idée de comment faire mais il génère beaucoup de pollution qu'il faut nettoyer...
Il faut toujours spécifier l'onglet quand tu travailles sur des onglets différents (idem pour des classeurs, etc)
Ton code :
Sub Action_à_mener()
Dim S As Worksheet 'déclare la variable S (Onglet S)
Dim S1 As Worksheet 'déclare la variable S1 (Onglet S-1)
Dim DL As Integer 'déclare la variable DL (Dernière Ligne)
Set S = Worksheets("S") 'définit l'onglet S
Set S1 = Worksheets("S-1") 'définit l'onglet S1
DL = S1.Cells(Application.Rows.Count, "I").End(xlUp).Row 'définit la dernière ligne éditée de la colonne I de l'onglet S1
S1.Range("A2:I" & DL).Name = "Tableau_owssvr" 'définit la plage nommée "Tableau_owssvr"
S.Columns(9).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 'insère une colonne après la colonne K
S.Columns(9).NumberFormat = "General" 'comme la colonne est au format "texte", remet le format "Standard"
S.Columns(9).ColumnWidth = 40 'définit la largeur de la colonne
S.Range("I1").Value = "Action à mener" 'écrit en I1
S.Range("I2").FormulaLocal = "=RECHERCHEV(A2;Tableau_owssvr;9;faux)" 'formule en A2
S.Range("I2").AutoFill Destination:=S.Range("I2:I" & DL) 'recopie la formule sur toutes les lignes
'enfin, dans cette colonne I, il faudrait supprimer tous les "0" 'ça j'ai pas compris
End Sub
[Édition]
Bonjour l'Acide, nos posts se sont croisés...
Merci à vous deux pour ces réponses rapides et appréciables pour moi.
Comme vous avez dû le comprendre, je suis plus que novice dans les macros … voire moins que débutant.
ThauThème :
Concernant ma demande de suppression des 0 : l'onglet "S-1" dans la colonne I "action à mener" n'aura pas toutes ses lignes remplies, de même des lignes de "S" n'existeront pas dans "S-1" puisque le tableau est mis à jour 1 fois par semaine et que de nouvelles lignes apparaissent au temps "S", et donc avec rechercheV, il va apparaitre des 0. Donc pour que ce soit plus clair, j'aimerai faire disparaitre ces 0.
ThauThème, pourrais-tu m'expliciter en détail :
S1.Cells(Application.Rows.Count, "I").End(xlUp).Row
Que j'essaye de comprendre les notions d'objet, méthodes, propriétés, évènement s'il y en a là …
De même pourriez-vous me conseiller un site, un livre qui me fasse apprendre le VBA pour Excel pour un nul comme moi ?
Cordialement
Re,
Ce code correspond à atteindre la dernière cellule non vide d'une colonne. Dans cet exemple c'est la colonne A
- on se place dans la dernière cellule de la colonne : Cells(application.rows.count, "A")
- on choisi la direction : comme on est en bas on veut remonter : .End(xlUp)
- le .Row sert juste à en extraire la ligne dans une variable. On aurait pu écrire Select pour la sélectionner.
Dans certain car on partira d'en haut. Avec le même principe on aura : Cells(1, "A").End(xlDown).Select
Mais si la colonne contient une cellule vide au milieu de la colonne, on ne sera pas forcément à la dernière cellule édité de la colonne
Pareil pour la dernière cellule édité d'une ligne (la ligne 1 par exemple) : Cells(1, Application.Columns.Count).End(xlToLeft).Select
Etc.
Re,
J'avais oublier l'autre point. Que veux-tu faire exactement :
1. Ne plus afficher le zéro dans la cellule ?
2. Supprimer la ligne ?
OK, c'est clair et précis
Pour mes 0, comment faire ? une boucle sur la colonne avec un si la valeur de la cellule = 0 alors on met la valeur = "" (et ça je ne sais même pas comment le traduire en VBA) ou une autre solution ?
sinon pour un site ou un bouquin pour apprendre pour un "nul" comme moi ?
Cordialement,
Re,
Le code modifié :
Sub Action_à_mener()
Dim S As Worksheet 'déclare la variable S (Onglet S)
Dim S1 As Worksheet 'déclare la variable S1 (Onglet S-1)
Dim DL As Integer 'déclare la variable DL (Dernière Ligne)
Set S = Worksheets("S") 'définit l'onglet S
Set S1 = Worksheets("S-1") 'définit l'onglet S1
DL = S1.Cells(Application.Rows.Count, "I").End(xlUp).Row 'définit la dernière ligne éditée de la colonne I de l'onglet S1
S1.Range("A2:I" & DL).Name = "Tableau_owssvr" 'définit la plage nommée "Tableau_owssvr"
S.Columns(9).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 'insère une colonne après la colonne K
S.Columns(9).NumberFormat = "General" 'comme la colonne est au format "texte", remet le format "Standard"
S.Columns(9).ColumnWidth = 40 'définit la largeur de la colonne
S.Range("I1").Value = "Action à mener" 'écrit en I1
S.Range("I2").FormulaLocal = "=RECHERCHEV(A2;Tableau_owssvr;9;faux)" 'formule en A2
S.Range("I2").AutoFill Destination:=S.Range("I2:I" & DL) 'recopie la formule sur toutes les lignes
For I = 2 To DL
If S.Cells(I, "I").Value = 0 Then S.Cells(I, "I").Value = ""
Next I
End Sub
Sinon j'ai tout appris dans un forum... Difficile de te conseiller.