Formules volatiles pour raccourcis

[ccdouble6]

Bonjour tout le monde,

J'utilise Excel 2000 sous XP et il me faut créer une macro pour un tableau de saisie comptable. J'ai commencé à taper cette macro d'après ce que j'ai déjà appris mais je rame pas mal... Vous verrez donc que ce n'est pas encore ça.

J'essaie de combiner macros et formules de RechercheV, en utilisant le principe de "formule volatile" que l'on m'a appris ici d'ailleurs

Cette macro n'est là que pour permettre d'accélérer la saisie :
Il y a deux feuilles dans mon fichier (joint au message) :
- sur la feuille Saisie il y a le tableau de saisie lui-même, donc aucune formule nulle part. La colonne C3 est importante, on peut éventuellement y taper une lettre ou un 1.
- sur la feuille Raccourcis, il y a un tableau rempli de lignes pré-tapées, ou à taper soi-même. Dans la colonne C3 on peut taper une lettre ou un chiffre.
La macro doit agir lorsque une lettre ou un 1 est tapé dans la colonne LC3 de la feuille Saisie.

Le but est que lorsque l'utilisateur tape une lettre ou un 1 dans la colonne C3 de la feuille Saisie, cela fasse apparaître dans CERTAINES cellules de la ligne une formule de RechercheV qui va chercher cette même lettre dans la feuille Raccourcis et ramène les infos pré-tapées qui correspondent.

Mais ce n'est pas si simple, il y a différents cas selon où la lettre figure dans la feuille Raccourcis : il y a par exemple des lignes où le montant doit rester en saisie libre et d'autres où il est en Débit et d'autres en Crédit.
J'ai donc prévu 3 cas selon le résultat indiqué par un tableau dans 'Saisie' (à droite du principal) : une formule NB.SI() dans chaque colonne détecte où apparaît la lettre dans 'Raccourcis' et la macro devra se baser dessus pour :
- mettre dans certaines cellules une formule de Recherche,
- ne rien faire pour les autres cellules.

Voici donc ci-joint (fichier en .txt dans un .zip) l'ébauche de macro.
J'éprouve plus particulièrement des difficultés sur les points suivants :
- l'incrémentation des lignes (la macro doit s'appliquer pour toutes les lignes),
- La fonction If,
- L'appel d'une "sub",
- Et les imprévus que les calculs peuvent générer : l'insertion de lignes notamment.

Je vous remercie grandement pour votre aide, celle-ci me permettra en même temps de faire un grand bond en avant dans ma pratique, vu que ce genre de macro regroupe pas mal de notions qui me sont inconnues mais que je serai sûrement amené à réutiliser...


Regarde la pièce jointe Exemple racc1.xls

Regarde la pièce jointe Texte MACRO.zip

 

[ccdouble6]

Re : Formules volatiles pour raccourcis

Bonsoir,
Je n'y ai pas pensé mais c'est plus simple ainsi.
Voici le texte de la macro que j'ai commencée. Si qq'un pouvait me dire si je me plante, dans la syntaxe ou la logique...
Comme vous verrez, je ne sais pas du tout comment exprimer l'incrémentation des lignes (est-ce nécessaire seulement? on doit pouvoir taper la nouvelle lettre n'importe où dans la colonne, pas forcément à la fin... )
Et j'ignore complètement comment va réagir cette macro si l'on insère des lignes dans le tableau, sachant que grâce à une autre macro la nouvelle ligne conserve les formules de celle au-dessous... dur dur...
Merci d'avance pour votre précieuse aide et bonne soirée !!

----------------------------

'Macro à placer dans la feuille Saisie du ficher d'exemple.


Sub RaccSansMontant
'Cette partie est appelée si la lettre tapée est trouvée dans la zone
'RACSANSM (LC15=1 par formule NB.SI),
'Les formules sont insérées dans les cellules de la présente ligne, soit
'pour le Compte, la Pièce, le Libellé :

Range("LC8").FormulalocalR1C1 = "=RECHERCHEV(LC3;RACSANSM;3;FAUX)"
Range("LC9").FormulalocalR1C1 = "=RECHERCHEV(LC3;RACSANSM;4;FAUX)"
Range("LC10").FormulalocalR1C1 = "=RECHERCHEV(LC3;RACSANSM;5;FAUX)"
End Sub
_____________________________________________________________

Sub RaccTotalD
'Cette partie est appelée si la lettre tapée est trouvée dans la zone
'RACDEBIT (LC16=1 par formule NB.SI),
'Les formules sont insérées dans les cellules de la présente ligne, soit
'pour les colonnes Compte, Pièce, Libellé, Débit :

Range("LC8").FormulalocalR1C1 = "=RECHERCHEV(LC3;RACDEBIT;3;FAUX)"
Range("LC9").FormulalocalR1C1 = "=RECHERCHEV(LC3;RACDEBIT;4;FAUX)"
Range("LC10").FormulalocalR1C1 = "=RECHERCHEV(LC3;RACDEBIT;5;FAUX)"
Range("LC11").FormulalocalR1C1 = "=RECHERCHEV(LC3;RACDEBIT;6;FAUX)"
End Sub
_____________________________________________________________

Sub RaccTotalC
'Cette partie est appelée si la lettre tapée est trouvée dans la zone
'RACCREDIT (LC17 =1 par formule NB.SI),
'Les formules sont insérées dans les cellules de la présente ligne, soit
'pour les colonnes Compte, Pièce, Libellé, Crédit :

Range("LC8").FormulalocalR1C1 = "=RECHERCHEV(LC3;RACCREDIT;3;FAUX)"
Range("LC9").FormulalocalR1C1 = "=RECHERCHEV(LC3;RACCREDIT;4;FAUX)"
Range("LC10").FormulalocalR1C1 = "=RECHERCHEV(LC3;RACCREDIT;5;FAUX)"
Range("LC12").FormulalocalR1C1 = "=RECHERCHEV(LC3;RACCREDIT;7;FAUX)"
End Sub
_____________________________________________________________

Sub RaccSpé1
'Cette partie est appelée si le raccourci tapé est 1.
'Les formules sont insérées dans les cellules de la présente ligne.

Range("LC5").FormulalocalR1C1 = "=DECALER(LC;-1;0)"
Range("LC6").FormulalocalR1C1 = "=DECALER(LC;-1;0)"
Range("LC7").FormulalocalR1C1 = "=DECALER(LC;-1;0)"
Range("LC8").FormulalocalR1C1 = "=DECALER(LC;-1;0)"
Range("LC9").FormulalocalR1C1 = "=DECALER(LC;-1;0)"
Range("LC10").FormulalocalR1C1 = "=DECALER(LC;-1;0)"
'Montant en saisie libre !!
End Sub

_________________________________________________________________

Private Sub Worksheet_Change(ByVal Target As Range)

'RAPPEL : Colonne des Raccourcis : C3
'RAPPEL : Colonne du comptage par NB.SI() : C15 à C17.
'Pour chaque ligne de la feuille à partir de L10 jusqu'à Omega la
'dernière ligne du tableau :

Dim i
'Exprimer qu'il s'agit de lignes?

For i = 10 to Omega

'Exprimer la ligne actuelle?

If Range("LC3") = ""
Then Next i
Else 'ventilation d'après le résultat des NB.SI()
If Range ("LC15") = 1 Then
Range("LC5:LC12").ClearContents
Call RaccSansMontant
End If

If Range ("LC16") = 1 Then
Range("LC5:LC12").ClearContents
Call RaccTotalD
End If

If Range ("LC17") = 1 Then
Range("LC5:LC12").ClearContents
Call RaccTotalC
End If

If Range ("LC3") = 1 Then
Range("LC5:LC12").ClearContents
Call RaccSpé1
End If
Next i

End If
End Sub
_____________________________________________________________

 

[ccdouble6]

Re : Formules volatiles pour raccourcis

Bonsoir,

Je ne sais pas si ce que j'ai tapé est assez clair, voici mes principaux soucis :
1°) J'utilise le terme formulalocalR1C1 afin d'avoir à la fois le droit d'utiliser les coordonnées relatives et celui de mettre les formules en français. Est-ce que ça se dit?? Comment doit-on le dire sinon?

2°) Comment dois-je faire pour passer d'une ligne à l'autre? est-ce la meilleure solution d'utiliser des "i" et "next i"??

Je suis largué et certain que le compileur va péter un plomb, j'ai un peu peur à l'idée de tester cette macro, j'en ai jamais fait qui prenne en compte autant de choses et notamment des variables.

Est-ce que qq'un veut bien lire ce que j'ai fait et me dire s'il y a une ou plusieurs abérrations s'il vous plaît???
Merci d'avance !!!

 

[Pierrot93]

Re : Formules volatiles pour raccourcis

Bonsoir Ccdouble6

petite question idiote, pourqoui les macros ne sont elles pas dans le classeur ?

Sinon n'ais pas peur que "le compileur va péter un plomb", exécutes tes macros pas pas, action sur la touche de fonction F8, après avoir cliqué dans la macro que tu veux tester. Et il te dira si il y a des erreurs par un message.

Sur les formules, je ne suis pas un formuliste, mais d un point de vue du code ca à l'air de coller.

Par contre après un "Then" je vois un "Next i" qui va te creer des soucis, enlève le a priori il est de trop.

N'hésites pas a revenir pour infos complémentaires.

Bon courage et bonne soirée.
@+

 

[ccdouble6]

Re : Formules volatiles pour raccourcis

Bonjour Pierrot73 et merci !!

Me voilà déjà rassuré.
En fait je n'ai pas tapé la macro pour ne pas qu'elle s'execute vu que je la pensais inachevée...
Je vais essayer ce que tu m'as dit, mais pour le "next i" je comprends pas comment la macro va passer à la ligne suivante si je ne le mets pas
Merci en tout cas !!
A+!

 

[Pierrot93]

Re : Formules volatiles pour raccourcis

Bonjour Ccdouble6

Un autre next i est positionné tout à la fin, juste avant le "end sub", à mon avis c'est celui ci qui est utilie.

Bon après midi
@+

 

[ccdouble6]

Re : Formules volatiles pour raccourcis

Rebonjour Pierrot93,

Merci pour cette précision, j'ai pu retravailler le texte principal de la macro (les sub annexes sont prêtes je pense)

J'ai encore deux soucis :
- comment exprime-t-on "ligne i" en VBA? j'ai mis Range("LiC3") dans la macro je sais pas si ça se dit comme ça (le texte n'est pas rouge dans VBA)
- Il y a du texte pur dans les formules que je veux insérer par VBA (à travers une des sub appelées), mais les "" avec du texte dedans il n'aime pas, il prend ça pour une instruction dont il attend la fin, pourtant les "" sans rien dedans il accepte !!
Y a-t-il un autre moyen que les "" pour exprimer l'affichage d'un texte?


Voici donc le texte principal final retravaillé :
------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

'RAPPEL : Colonne des Raccourcis à saisir : C3.
'RAPPEL : Colonne du comptage par NB.SI() : C26 à C29.
'Pour chaque ligne de la feuille à partir de L10 jusqu'à Omega la
'dernière ligne du tableau :

Dim i As Integer
Dim Omega As Integer
Omega = Range("L1C15") 'cette cellule contient le n° de la dernière ligne.

For i = 10 To Omega
If Range("LiC3") = "" Then

If Range("LiC26") = 1 Then
Range("LiC5:LiC12").ClearContents
Call RaccSansMontant
End If

If Range("LiC27") = 1 Then
Range("LiC5:LiC12").ClearContents
Call RaccTotalD
End If

If Range("LiC28") = 1 Then
Range("LiC5:LiC12").ClearContents
Call RaccTotalC
End If

If Range("LiC3") = 1 Then
Range("LiC5:LiC12").ClearContents
Call RaccSpé1
End If

If Range("LiC3") = 4 Then
Range("LiC5:LiC12").ClearContents
Call RaccSpé4
End If

If Range("LiC3") = 5 Then
Range("LiC5:LiC12").ClearContents
Call RaccSpé5
End If

If Range("LiC3") = 6 Then
Range("LiC5:LiC12").ClearContents
Call RaccSpé6
End If

If Range("LiC3") = 8 Then
Range("LiC5:LiC7").ClearContents
Range("LiC9:LiC12").ClearContents
Call RaccSpé8
End If

If Range("LiC3") = 9 Then
Range("LiC5:LiC12").ClearContents
Call RaccSpé9
End If
Next i

End If
End Sub

--------------------------------------------------

Et je reste tout à fait ouvert à toutes suggestions, merci d'avance
Bon aprem !!

 

[Pierrot93]

Re : Formules volatiles pour raccourcis

Bonsoir ccdouble6

Regarge l'exemple ci dessous, j'ai essayé de commenter un peu le code, cet exemple s'applique à la colonne A

Private Sub test()
Dim Omega As Integer, i As Integer
'derniere ligne de la colonne A renseignée
Omega = Range("A65536").End(xlUp).Row
'boucler sur la colonne A de A1 a dernière
For i = 1 To Omega
    'ton test
    If Range("A" & i) = "" Then
    'action  si vrai
    Else
    'action  si faux
    End If
'passe a la ligne suivante
Next i
End Sub

Bonne soirée
@+

 

[ccdouble6]

Re : Formules volatiles pour raccourcis

merci Pierrot93 !!

Je commence à y voir plus clair ça fait du bien
Si on prend des coordonnées type L1C1,
et si on considère la ligne 45 comme la dernière (mettons qu'il y a dans la cellule L45C1 la formule LIGNE() qui renverrait donc 45),
ça donne donc ça je pense :
----------------------------------

Private Sub test()

Dim Omega As Integer, i As Integer

'derniere ligne de la colonne A renseignée :
Omega = Range("L45C1").End(xlUp).Row
'Que veut dire ce "End(XlUp).Row"? le Range tout seul ne suffit pas?

'boucler sur la colonne 1 de L1 a la dernière :
For i = 1 To Omega
'ton test
If Range("L" & i & "C1") = "" Then
'action si vrai
Else
'le Else est vraiment nécessaire s'il n'y a pas d'action?
End If

'passe a la ligne suivante
Next i
End Sub

C'est géant un échange comme ça, si j'arrive à comprendre le système de boucle (et ce "xlUp"), avec les If et For et tout je vais arriver à faire de ces trucs sur Excel c'est merveilleux !!
Merci encore et bonne soirée !!

 

[ccdouble6]

Re : Formules volatiles pour raccourcis

PS : Ah oui juste un détail, enfin pas si négligeable que ça
dans tous les "Range()" qui vont suivre (dans le then) il faudra que je mette toujours les i je suppose non? et donc ça ressemblera partout à
Range("L" & i &"C12345") je pense c'est bien ça?
Waow c'est costaud quand-même

 

[Pierrot93]

Re : Formules volatiles pour raccourcis

Re bonsoir ccdouble6,

Déjà 1ère chose, je ne travaille que très très rarement avec des coordonnées type L1C1 !!! Quand tu codes direct, a priori, pas besoin.

D'ailleurs si tu codes "Omega = Range("L45C1").End(xlUp).Row" tu vas avoir un message d'erreur.

Que veut dire ce "End(XlUp).Row"? le Range tout seul ne suffit pas?

Ca veut dire que je pars du bas de la colonne A ? la cellule A65536 est la dernière possible, je vais a la fin ".end" en remontant "xlUp", et je détermine le numéro de la ligne de la premiere cellule non vide ".row" afin d'alimenter la variable "oméga" , par 45 donc pour ton cas.

Le "else" n'est pas obligatoire, il est facultatif.

Dans ta boucle ca devra surtout ressembler à ce que moi j'ai mis dans l'exemple.

N'hésite pas non plus à consulter l'aide VBA, tu cliques sur un mot dont tu veux plus de détail et tu fais touche de fonction "F1", moi j'ai commencé avec ca !!!!

Reviens sur le fil si tu as d'autres questions.

Bon courage et bonne soirée.
@+

 

[ccdouble6]

Re : Formules volatiles pour raccourcis

Bonjour Pierrot93,

Et un grand grand merci !!!!
c'est super, je dispose de quoi terminer la bête

Je vais tester tout ça et je te tiens au courant.
A+ !!

 

[ccdouble6]

Re : Formules volatiles pour raccourcis

Bonjour Pierrot93,

Le texte de la macro est prêt, j'ai plus qu'à tester
Toutefois une chose à laquelle je n'avais pas pensé : j'ai tapé la macro dans un module car il y a en tout six feuilles qui vont l'appeler.
Y a-t-il un moyen textuel ou autre de relier le module à la feuille concernée? ou est-ce que je devrai copier le texte dans six fenêtres (ce qui sera assez lourd au final) ?
D'avance merci, et bonne journée !!

 

[Pierrot93]

Re : Formules volatiles pour raccourcis

Bonjour ccdouble6,

non, a priori c'est bien dans un module, si c'est un module standard.

Par contre attention, dans ce cas il va falloir nommer les feuilles pour les actions que la macro effectuera sur une autre feuille que la feuille active. Exemple de code ci dessous :

Omega = Sheets("NOM DE TA FEUILLE").Range("A65536").End(xlUp).Row

Bon courage et bon après midi
@+

 

[G.David]

Re : Formules volatiles pour raccourcis

Range("L" & i &"C12345")

C256 maximum (sur les versions d'excel que je connait ..à moins que 2007?)