Aide sur une macro de retraitement de stock
Bien le bonjour !
Je sors tout juste d'une formation VBA de 3 jours (que j'ai adoré
Seulement voilà : les macros faites durant la formation étaient plutôt simples en comparaison à celle que j'essaye de faire qui (me paraît) beaucoup trop complexe.
En PJ un exemple du process ultra manuel que je souhaite automatiser :
- lorsqu'une cellule de la colonne C contient une clé (disons K102240)
- je copie et recherche cette clé dans la feuille Table_K
- je copie les cellules adjacentes à cette clé (donc ici B4:C5)
- je colle (en valeur) en Feuil1 en colonne A au bas du tableau (à la première ligne vide rencontrée, ici A6)
- je me déplace en D6 et écrit =B6*D3
- je descend d'une ligne (donc D7) et écrit =B7*D3
- je copie/colle valeur D6 et D7 (pour neutraliser la formule)
- je me déplace en D3 et ajoute = devant la quantité existante et - D6 - D7 derrière la quantité existante
- je copie/colle valeur D3 (pour neutraliser la formule)
- j'efface la clé en C3
- je reprends ma recherche de Clé en colonne C et recommence ces étapes
- je m'arrête lorsque la colonne C est entièrement vide
J'ai donc utilisé l'enregistreur de macro puis j'ai commencé à modifié le code, et me voilà déjà bloqué :
Range("C2").Select
If ActiveCell = "" Then
ActiveCell.Offset(1, 0).Select
Else
Selection.Copy
End If=> si je ne m'abuse, en procédant ainsi la macro va tester toute la colonne C ? Comment lui indiquer de s'arrêter au bas du tableau ?
2ème problème : la recherche de la clé dans la feuille Table_K.
Vu le code écrit par l'enregistreur lorsque j'ai utilisé Ctrl + F, j'en déduis que ce n'est pas la bonne méthode ? J'ai pensé à une rechercheV mais j'ai 1 à N lignes à aller chercher donc ça semble compromis.
Si quelqu'un peut m'aider ce serait super !
Pour vous donner une idée, cette tâche représente 6 à 7h de boulot fastidieux, 2 fois par mois ^^'
Quelques remarques :
- je n'arrive pas à trouver un titre explicite, c'est dire si j'ai su exploiter la fonction recherche du forum
- en formation on déclarait des variables à tout-va, ici je n'en ai pas "ressenti" le besoin. Me trompé-je ?
- les commentaires écrits dans la macro ne traduise pas le code actuellement écrit mais le code que je souhaite écrire ^^
- à un moment il faudra bien que j'intègre une boucle dans le code, je ne sais pas encore laquelle
- globalement ce que j'essaye de créer est faisable ou c'est chaud ?
Un grand merci d'avance !
Sub Extraction_K()
' éclate la quantité selon la clé en C, ajoute les lignes de la clé au bas du tableau
'insérer un test sur cellule vide en C
'si vide, descendre d'une ligne
Range("C2").Select
If ActiveCell = "" Then
ActiveCell.Offset(1, 0).Select
Else
Selection.Copy
End If
'rechercher la clé dans Table_K
'copier les cellules adjacentes à cette clé (pour chaque ocurrence)
Sheets("Table_K").Select
Range("D3").Select
Cells.Find(What:="K102240", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
'copier les cellules adjacentes à cette clé (pour chaque ocurrence)
Range("B4:C5").Select
Application.CutCopyMode = False
Selection.Copy
'coller en valeur en colonne A, dans la première cellule vide rencontrée
Sheets("Feuil1").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D6").Select
Application.CutCopyMode = False
'inscrire en colonne D de la ligne =Taux (colonne B)* Quantité de la clé (colonne D)
ActiveCell.FormulaR1C1 = "=RC[-2]*R[-3]C"
Range("D7").Select
'répéter pour chaque ligne jusqu'à rencontrer une ligne vide
ActiveCell.FormulaR1C1 = "=RC[-2]*R[-4]C"
'sélectionner les cellules calculées de la colonne D et copier/coller valeur
Range("D6:D7").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'se placer sur la cellule quantité de la clé
'soustraire les quantités calculées au bas du tableau
Range("D3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=2875-R[3]C-R[4]C"
'copier/coller valeur la quantité de la clé
Range("D3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'effacer la clé
Range("C3").Select
Application.CutCopyMode = False
Selection.ClearContents
'descendre d'une ligne
Range("C4").Select
'recommencer jusquà ce que la colonne C soit vide
End SubBonjour subzero le forum
Ta demande me paraît pourtant simple, enfin si tu as 3 jours de vba!!!
déjà pour faire toute ta colonne C tu peux faire
for i= 2 to fin
if cells(i,3)<>"" then
(là tes deux calculs)
end if
next iBien sur il faudra trouver le fin avec un truc du style fin=range("C"&rows.count).end(3).row
maintenant si tu passes ton fichier avec les indications claires de ce que tu veux faire je te fais la macro qui va bien
a+
Papou
bonjour
salut patitec
une suggestion sans VBA ni formule compliquée
et on gère bien plus que des stocks si nécessaire.
https://forum.excel-pratique.com/viewtopic.php?p=514965#p514965
Bonjour JmBc (moi aussi je ne sais pas recopier) mais moi c'est volontaire Jmd
Oui mais lui il sort d'un stage de Vba, c'est pour coder du Vba je pense !!!!!
a+
Papou
re
Papou paritec
désolé pour le clic t à la place du r (ces lettres sont proches, et mon correcteur orthographique te souligne en rouge quoi que je tape
ah oui pour le stage
beh moi, je déconseille VBA et tous les stages qui vont avec
et Microsoft aussi. Ils voudraient se débarrasser de ce vieux logiciel devenu inutile. Voir Power Query et Power BI.
c'est mon avis perso et celui de MS. Mais je sais que certains y croient encore.
Oui c'est un coup de gueule
Ce qui n'enlève rien à la compétence des VBAistes, que je respecte au plus haut point.
Mais je leur suggère de regarder M et DAX ou SQL et Python, ou encore C et ses avatars. L'avenir est là.
bon travail, quel que soit ton langage.
amités excelliennes
Re JMD le forum
Tu ne vis que pour tes formules et tes tableaux, mais mais mais
le traitement des données justement avec tes formules et des fichiers un peu lourd, tu te traines et si par malheur tu gères sur un événementiel, là tes formules et les recalculs sont une galère.
Moi je suis comme Bruno45
Cela ne m'empêche pas de me pencher dessus, mais pas simple sans formation particulière, mais je suis dessus en C+
et en attendant je macrote en vba.
bonne journée
a+
papou
Bonjour et merci pour vos réponses !
Ta demande me paraît pourtant simple, enfin si tu as 3 jours de vba!!!
Oui c'est pour ça que j'ai un peu honte d'être bloqué dès la 3ème ligne
maintenant si tu passes ton fichier avec les indications claires de ce que tu veux faire je te fais la macro qui va bien
ça serait super ! Je regarderai ensuite le code pour comprendre ce qu'il me manquait
Je reprends l'exemple ci-dessus, n'hésite pas à me dire si une étape ne paraît pas claire :
- lorsqu'une cellule de la colonne C contient une clé (disons K102240)
- je copie et recherche cette clé dans la feuille Table_K
- je copie les cellules adjacentes à cette clé (donc ici B4:C5)
- je colle (en valeur) en Feuil1 en colonne A au bas du tableau (à la première ligne vide rencontrée, ici A6)
- je me déplace en D6 et écrit =B6*D3
- je descend d'une ligne (donc D7) et écrit =B7*D3
- je copie/colle valeur D6 et D7 (pour neutraliser la formule)
- je me déplace en D3 et ajoute = devant la quantité existante et - D6 - D7 derrière la quantité existante
- je copie/colle valeur D3 (pour neutraliser la formule)
- j'efface la clé en C3
- je reprends ma recherche de Clé en colonne C et recommence ces étapes
- je m'arrête lorsque la colonne C est entièrement vide
re
une macro qui veut imiter ce qu'on fait sur des feuilles de papier ne durera pas
Excel c'est autre chose, surtout si en plus on utilise ses "extensions" Power Query inclus, Power Pivot(pas gratuit) et Power BI (gratuit)
mais pour une gestion de stock, des RECHERCHEV suffisent, sans ralentissement majeur sur les PC et Mac actuels.
paritec,
VBA est un langage qui a eu son bon temps, mais qui est aujourd'hui dépassé.
note que j'ai à peu près ton âge
amitiés à tous
J'utilise très souvent la rechercheV mais dans le cas présent je n'ai jamais réussi :
la clé apparaît plusieurs fois dans la table avec des valeurs différentes donc si j'écris
=RECHERCHEV(C3;Table_K!A:B;2;FAUX), j'obtiens le résultat 221478
Sauf que j'ai deux résultats à aller chercher avec la clé en C3 : est-ce que tu connais une astuce permettant à la recherche de ne pas prendre le premier résultat mais le suivant ? Puisque dans la table chaque clé peut avoir 1 à N résultat, la 3ème recherche ne devra pas prendre le premier ni le second résultat, et ainsi de suite.
re
une table que tu interroges avec RECHERCHEV ne doit pas avoir de doublons
par ex la table des produits : les références des produits sont uniques
ou bien la table des fournisseurs : leur ID doit être unique
as-tu regardé mon exemple dans le lien plus haut ?
Bonjoir Jmd
une question, a quoi servent les lignes que tu ajoutes à ta liste dans la feuil1 en ligne 6 et suivante???
c'est pour être supprimée après ????
dans l'attente de ta réponse
a+
Papou
re
de quel tableau parles-tu ?
note que par principe, je n'efface JAMAIS de données dans Excel. Comme n'importe quel progiciel de gestion de données SGBD ou noSQL.
amitiés
Re Jmd le forum
là ce n'est pas supprimer, c'est juste pour savoir si ces données doivent être copiées ou non, car à mon avis, il a copié les données mais ce n'était que pour faire le calcul, puisque à la finale il retranche le résultat à la cellule D même ligne!!!
Donc pour répondre correctement si effectivement le but est juste de calculer il n'y a pas à les copier!!
a+
Papou
Re jmd le forum
maintenant si tu donnes des cours de C+ cela m'intéresse, ou si tu as des sites axé C+ avec niveau débutant moins
a+
Bon weekend
Papou
re
je donne pas de cours de C(x)
mais il y a des tonnes de tutos, en texte ou en vidéo
que veux-tu faire avec C ?
aurais-tu intérêt à apprendre un autre langage ?
@jmd
oui j'ai regardé l'exemple mais, sauf erreur, je n'ai pas vu quelque chose qui puisse m'aider.
une table que tu interroges avec RECHERCHEV ne doit pas avoir de doublons
par ex la table des produits : les références des produits sont uniques
ou bien la table des fournisseurs : leur ID doit être unique
On est d'accord, donc comme je ne peux pas modifier la table ni la clé de la Feuil1 (ces données me sont transmises par d'autres services), la rechercheV ne peut pas m'aider ici. J'avais essayé de retraiter ces données pour obtenir des identifiants uniques, mais c'est une solution qui me prends encore plus de temps
@paritec
une question, a quoi servent les lignes que tu ajoutes à ta liste dans la feuil1 en ligne 6 et suivante???
c'est pour être supprimée après ????
Non elles ne seront pas supprimées : au final le fichier aura les X lignes de base + toutes celles qui auront été ajoutées via ce process.
Pour le contexte : la ligne 3 indique 2875 KG en stock du produit 114252, mais comme il y a une clé alors en réalité il faut lire "le produit 114252 indique 2875 KG en stock, dont 0,5% du produit 221478 et 2,75% du produit 225879.
Ainsi, j'ajoute en ligne 6 et 7 ces deux produits et je calcul la quantité de chacun contenue dans les 2875 KG.
Je retire ensuite des 2875 les quantités calculées en ligne 6 et 7 : de cette manière j'ai toujours en global 2875 KG mais éclaté sur 3 produits différents.
Tout ceci est indispensable pour l'étape suivante : calcul de la valeur du produit (pour une raison que j'ignore, il n'est pas possible de calculer directement la valeur d'un produit "composé" d'autres produits, d'où ce process d'éclatement).
N.B : tu auras peut-être remarqué qu'en procédant ainsi je me retrouve avec des lignes doublons de code, mais ça ne pose aucun problème pour la suite.
Ré subzero le forum
Ok je te ferai cela ce soir car là pas le temps je fais un programme d’automate
A plus
Papou
Bonjour Subzero le forum
la liste des données est très longue il faudra passer par des tableaux(vba)
autrement voilà par rapport au blabla que tu avais mis, voilà exactement ce que tu as demandé mais je trouve ton résultat nul, trois colonnes remplies pas de date bref cela ne me plait pas, mais c'est le résultat de ta demande
a+
Papou
re à vous
le fait de devoir traiter des fichiers mal conçus et qu'il est interdit de modifier est un handicap, une perte de temps et un risque de problèmes tôt ou tard
courage à vous
amitiés
Bonjour paritec
Mais c'est formidable !
Je viens de jeter un oeil et j'ai testé sur un fichier réel complet : résultat impeccable.
Pour moi ça représentait 7h de travail, le programme l'a fait en 2sec
Je trouve le code très intéressant, je vais potasser tout ça.
En tous cas merci beaucoup !!
Bonne journée