XL 2016 Renvoyer la valeur de la cellule du dessous

[Lemontine]

Bonjour !

Tout d'abord, je tiens à préciser que je suis d'une totale crasse concernant macro et VBA, j'arrive à adapter certains codes simples mais lorsqu'il s'agit de créer mes propres outils, je galère ! Par ailleurs, je dois aussi tenir compte des utilisateurs de l'outil. Il faut que le fichier reste simple... et solide.

Voici ce que j'essaye de de fabriquer : un fichier qui stocke les données des factures émises par la société.

Pour ce faire, j'ai commencé par créer un modèle de facture.
Les utilisateurs ont l'habitude de commencer par donner un numéro de facture basée sur la date du jour, donc j'ai préféré en faire une donnée à ajouter manuellement.
Ensuite, ce que je cherche à faire, c'est reporter les données "Date facture";"Client";"N° facture";"Imputation projet";"Montant HT";"Montant TTC" dans l'onglet "Facturier FX".

J'ai créé une macro, mais j'ai oublié que les utilisateurs vont probablement rajouter/enlever des lignes selon les échéances à indiquer, selon qu'il y aura ou non une remise, etc. Nous sommes une toute petite structure, et chaque facture est une œuvre d'art :-D (je ne vous parle pas de nos devis...)

Bref, du coup, je me suis dit que le haut de la feuille n'allait pas poser de problème, car j'ai prévu suffisamment de "champs", par contre, pour récupérer le montant HT et TTC, c'est une autre paire de manches.

C'est là où j'ai besoin d'aide : je voudrais pourvoir dire à excel, dans la plage de valeur B28:B50 (c'est la plage qui va probablement avoir des lignes insérées, ou supprimées selon moi) , si tu trouve "TOTAL HT", renvoie moi la valeur de la cellule du dessous, et idem pour "TOTAL TTC".

Merci pour vos conseils, aide,

 

[GALOUGALOU]

bonjour lemontine bonjour le forum
avant toute reflexion sur votre classeur
par macro, ne voulez vous pas interdire à vos collaborateurs de supprimer des lignes ou d'en rajouter ?
cordialement
galougalou

 

[xUpsilon]

Bonjour

Essaye

=INDEX(B28:B50;EQUIV("TOTAL HT";B28:B50)+1;1)

Bonne continuation

 

[Lemontine]

@GALOUGALOU : merci beaucoup pour votre réponse. Malheureusement, je dois prendre en compte le fait qu'ils vont vouloir ajouter ou supprimer des lignes. Mon outil doit s'adapter à ses utilisateurs si je veux qu'ils s'en servent

@xUpsilon : merci ! je vais essayer tout de suite !

 

[GALOUGALOU]

ok je prend en compte l'ensemble de vos problématique, je n'ai pas de réponse immédiate, je m'en occupe ce soir

 

[Lemontine]

@GALOUGALOU
Oh merci, c'est adorable !
Je continue les recherches de mon côté

 

[Lemontine]

(re) bonjour,

J'ai un peu avancé, et je me rend compte qu'en fait il faut vraiment que je recherche et copie colle les données ensuite.

Sinon, lorsque je créé une 2ème nouvelle facture, les montants se remettent à jour. Je mets mon code ci-après pour plus de compréhension :

Sub Infos_facturier()

'Insertion d'une nouvelle ligne
Sheets("Facturier FX").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With

'copie et mise en forme de la date de la facture
Range("A2").Select
Sheets("nouvelle facture FX").Select
Range("H16:L16").Select
Selection.Copy
Sheets("Facturier FX").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
With Selection.Font
.Name = "Calibri"
.FontStyle = "Normal"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
'copie du nom de client
Range("F2").Select
Sheets("nouvelle facture FX").Select
Range("E3").Select
Selection.Copy
Sheets("Facturier FX").Select
ActiveSheet.Paste

'copie du n° de facture et mise en forme
Range("G2").Select
Sheets("Nouvelle facture FX").Select
Range("F16").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Facturier FX").Select
ActiveSheet.Paste
Range("G2").Select
Application.CutCopyMode = False
Selection.NumberFormat = "0"
With Selection.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Font.Bold = False
With Selection.Font
.Name = "Calibri"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'copie du nom du projet
Range("H2").Select
Sheets("Nouvelle facture FX").Select
Range("J2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Facturier FX").Select
ActiveSheet.Paste

' récupère le montant HT et mise en forme
Range("I2").FormulaR1C1 = _
"=INDEX('nouvelle facture FX'!R[22]C[-7]:R50C2,MATCH(""TOTAL HT"",'nouvelle facture FX'!R[22]C[-7]:R50C2)+1,1)"
Range("I2").Select
Application.CutCopyMode = False
Selection.NumberFormat = "#,##0.00"

' récupère le montant TTC et et mise en forme
Range("J2").FormulaR1C1 = _
"=INDEX('nouvelle facture FX'!R28C11:R50C11,MATCH(""TOTAL TTC"",'nouvelle facture FX'!R28C11:R50C11)+1,1)"
Range("J2").Select
Application.CutCopyMode = False
Selection.NumberFormat = "#,##0.00"
Rows("2:2").Select
With Selection.Font
.Name = "Calibri"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With Selection.Font
.Name = "Calibri"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With

End Sub

 

[GALOUGALOU]

question ?
dans la feuille d'origine ou prends tu client et ou prends tu imputation projet ?

 

[GALOUGALOU]

bonjour lemontine bonjour le forum, belle prise de tete sur ce classeur
merci à upsilon pour sa formule réutilisé dans ce classeur
un essai en pièce jointe
si des lignes vides ou des colonnes vides sont supprimées, je répète vide, les formules s'adaptent
si des lignes ou colonnes sont rajoutés les formules s'adaptent
tes boutons de macros sont pour moi destructeur de mon travail
j'ai donc ajouté deux onglets et un bouton de macro pour que tu compare ta solution avec la mienne.
je n'ai certainement pas fait le tour complet de tes besoins mais tu as là un début de solution.
pour la feuille admin le mot de passe pour déverrouiller (inutile de déverrouiller la feuille doit être protégé) est "MDP"
je ne saurais trop te conseiller de sauvegarder un classeur car si tes collaborateurs modifie la structure du classeur le risque est grand de rendre obsolète le travail que je te propose

ci dessous la macro avec la mise en évidence des noms à modifier si éventuellement il y a une modification du nom de l'onglet facturier FXessai
inutile de modifier le nom de l'onglet ADMIN il doit rester verrouillé.

Sub ventilation()
Sheets("Facturier FXessai ").Select
li = Sheets("ADMIN").Cells(5000, 1).End(xlUp).Row ' a partir de la ligne 500 TROUVE LA 1ER CELLULE VIDE EN REMONTANT DANS LA COLONNE 7 (G)
ligne = Sheets("Facturier FXessai").Cells(5000, 1).End(xlUp).Row + 1
For i = 1 To li
If UCase(Sheets("ADMIN").Range("I" & i)) = "VRAI" Then
Cells(ligne, 1) = Sheets("ADMIN").Cells(i, 1)
Cells(ligne, 2) = Sheets("ADMIN").Cells(i, 2)
Cells(ligne, 3) = Sheets("ADMIN").Cells(i, 3)
Cells(ligne, 4) = Sheets("ADMIN").Cells(i, 4)
Cells(ligne, 5) = Sheets("ADMIN").Cells(i, 5)
Cells(ligne, 6) = Sheets("ADMIN").Cells(i, 6)
ligne = ligne + 1
End If
Next
MsgBox ("modifications effectuées")
Sheets("Facturier FXessai ").Select
Range("A1").Select
End Sub


cordialement
galougalou

 

[Lemontine]

@GALOUGALOU
Merci !!
Je suis sur une autre prod aujourd'hui, mais je vais regarder ce soir.
Mille mercis d'avance en tout cas.

 

[Lemontine]

Bonsoir à nouveau, et pardon de ne revenir vers vous que ce soir, mais j'ai entre-temps dû faire un autre tableau qui piquait les yeux...

@GALOUGALOU
J'ai regardé le fichier que tu m'as envoyé, et je n'arrive pas à le faire fonctionner.
1°) Tout d'abord, mon idée est que l'utilisateur copie-colle le modèle de facture (le premier onglet), et c'est à partir de ce second onglet qu'il établit sa facture, et que les infos vont se "ranger" dans le facturier (ou l'onglet ADMIN).
C'est pourquoi j'ai fabriqué ce bouton "copier-déplacer" (cf Onglet 1.jpg), afin que le modèle "survive" dans le temps (normalement, j'avais verrouillé certaines cellules, comme le nom du client, etc, pour que l'utilisateur soit obligé de dupliquer l'onglet)
2°) Ensuite, à partir de l'onglet dupliqué, l'utilisateur peut faire sa tambouille (ajouter des lignes, etc.) mais certaines données seront copiées pour nourrir le facturier (ou onglet ADMIN).
Là je vois à peine le bouton "sauvegarde facture" car, dans ma macro "copier-déplacer" j'ai demandé à Excel qu'il supprime les lignes 1 à 3. Est-ce que je peux déplacer le bouton pour le mettre ailleurs sur la feuille ?
3°) Et même quand je clique sur le bouton "sauvegarde facture" (que je peux quand même actionner, cf Onglet 1 copie.jpg), j'ai le message d'erreur suivant "Impossible d'exécuter la macro [...] il est possible qu'elle ne soit pas disponible dans ce classeur ou que toutes les macros soient désactivées" (cf message erreur.jpg).
Pourtant, les macros sont activées, et je vois bien dans le module 2 la macro que tu as élaborée...

HELP !

Merci beaucoup,

 

[GALOUGALOU]

ok je vais regarder ça au plus vite
pour la macro le bouton n'est pas affecté
procédure
clic droit sur le bouton
affecter une macro
choisir ventilation et ça devrait fonctionner
pour le reste je regarde ça au plus vite.
je vous fais le retour dès que possible
cordialement
galougalou

 

[GALOUGALOU]

bonjour lemontine bonjour le forum
je vous poste une réponse vendredi
cordialement
galougalou

 

[Lemontine]

Merci beaucoup
Et merci pour la manip pour le bouton, je vais tester de suite.
Belle fin de semaine à vous,

 

[Lemontine]

@GALOUGALOU
Ta macro est formidable !! C'est juste que j'aurais besoin qu'elle fonctionne sur l'autre onglet. Je ne sais pas si c'est possible.
J'ai fait une capture video, peut-être que ce sera plus parlant :