[Résolu] Macro pour placer des références absolues dans mes formules

[Webperegrino]

Bonjour Le Frum,
Dans la formule ci-après j'aimerais passer par macrocommande les références normales en références absolues sans avoir à faire une succession de doubles appuis sur la touche F4.

Un premier essai de macro (ci-dessous) n'a pas réussi.

De la cellule D96 à la cellule D606 j'ai cette formule :
=SI($D$3="ESPÈCES";'Référentiel des Saisies ENTRÉES'!$D100;SI($D$3="CHQ BANCAIRES";'Référentiel des Saisies ENTRÉES'!$E100;SI($D$3="CHQ VACANCES";'Référentiel des Saisies ENTRÉES'!$F100;SI($D$3="CHQ CULTURE";'Référentiel des Saisies ENTRÉES'!$G100;SI($D$3="CARTE BANCAIRE";'Référentiel des Saisies ENTRÉES'!$J100;0)))))

Ce sont les références $D100, $E100, $F100, $G100 et $J100 qui m'intéressent.

En cellule D606 j'ai donc ceci :
=SI($D$3="ESPÈCES";'Référentiel des Saisies ENTRÉES'!$D610;SI($D$3="CHQ BANCAIRES";'Référentiel des Saisies ENTRÉES'!$E610;SI($D$3="CHQ VACANCES";'Référentiel des Saisies ENTRÉES'!$F610;SI($D$3="CHQ CULTURE";'Référentiel des Saisies ENTRÉES'!$G610;SI($D$3="CARTE BANCAIRE";'Référentiel des Saisies ENTRÉES'!$J610;0)))))

J'ai commencé une macro :

Sub ABSOLU()
'For lig = 96 To 606
lig = ActiveCell.Row 'en attendant d'avoir mieux je le fais sur la cellule sélectionnée
    ActiveCell.FormulaR1C1 = _
        "=IF(R3C4=""ESPÈCES"",'Référentiel des Saisies ENTRÉES'!D&lig+4,IF(R3C4=""CHQ BANCAIRES"",'Référentiel des Saisies ENTRÉES'!R100C5,IF(R3C4=""CHQ VACANCES"",'Référentiel des Saisies ENTRÉES'!R100C6,IF(R3C4=""CHQ CULTURE"",'Référentiel des Saisies ENTRÉES'!R100C7,IF(R3C4=""CARTE BANCAIRE"",'Référentiel des Saisies ENTRÉES'!R100C10,0)))))"
'Next lig
End Sub

Le résultat sur la cellule D96 donne ceci :
=SI($D$3="ESPÈCES";'Référentiel des Saisies ENTRÉES'!D&lig+4;SI...

Alors que j'aimerais obtenir...
=SI($D$3="ESPÈCES";'Référentiel des Saisies ENTRÉES'!$D$100;SI...
et ainsi de suite.

Je pourrai alors automatiser la chose entre 96 et 606.
Merci pour votre aide précieuse.
Webperegrino

 

[haonv]

Re : Macro pour placer des références absolues dans mes formules

Bonjour Webperegrino, forum,

Un essai avec:

Sub ABSOLU()
Dim lig As Integer
For lig = 96 To 606

'' "Feuil1" étant le nom de la feuil où doivent être reportées les formules

Worksheets("Feuil1").Cells(lig, 4).FormulaR1C1 = _
"=IF(R3C4=""ESPÈCES"",'Référentiel des Saisies ENTRÉES'!R" & lig + 4 & _
"C4,IF(R3C4=""CHQ BANCAIRES"",'Référentiel des Saisies ENTRÉES'!R" & lig + 4 & _
"C5,IF(R3C4=""CHQ VACANCES"",'Référentiel des Saisies ENTRÉES'!R" & lig + 4 & _
"C6,IF(R3C4=""CHQ CULTURE"",'Référentiel des Saisies ENTRÉES'!R" & lig + 4 & _
"C7,IF(R3C4=""CARTE BANCAIRE"",'Référentiel des Saisies ENTRÉES'!R" & lig + 4 & "C10,0)))))"
Next lig
End Sub

Amicalement

 

[Fred0o]

Re : Macro pour placer des références absolues dans mes formules

Bonjour Webperegrino,

La plus simple des solutions est de corriger en D96 puis de tirer la formule vers le bas.

Au cas où pour des raisons qui me sont inconnues cette solution ne fonctionnerait pas, tu peux toujours lancer cette macro :

Sub ABSOLU()
    Dim lig As Integer
    For lig = 96 To 126
       Cells(lig, 4).Formula = WorksheetFunction.Substitute(ActiveCell.Formula, "$D" & lig + 4, "$D$100")
       Cells(lig, 4).Formula = WorksheetFunction.Substitute(ActiveCell.Formula, "$E" & lig + 4, "$E$100")
       Cells(lig, 4).Formula = WorksheetFunction.Substitute(ActiveCell.Formula, "$F" & lig + 4, "$F$100")
       Cells(lig, 4).Formula = WorksheetFunction.Substitute(ActiveCell.Formula, "$G" & lig + 4, "$G$100")
       Cells(lig, 4).Formula = WorksheetFunction.Substitute(ActiveCell.Formula, "$J" & lig + 4, "$J$100")
    Next lig
End Sub

A+

 

[Webperegrino]

Re : Macro pour placer des références absolues dans mes formules

Bonjour Le Forum,
Bonjour Haonv et FredOo,
Vos deux solutions ont été appliquées : c'est Haonov qui gagne.
La proposition de "tirer" La formule en D96 ($D$96) vers le bas fonctionne tant que les références ne sont pas absolues.
Tirer vers le bas la formule déjà en réf. absolue bloque les affichages en $D$96 et non $D$97, $D$98... sur les lignes suivantes.
La codification proposée par Haonov fonctionne à merveille.

Par contre la formulation de Fredoo ne fonctionne pas bien.
Mercic à vous deux pour ces deux solutions que je vais étudier.
Webperegrino

 

[Webperegrino]

Re : Macro pour placer des références absolues dans mes formules

Bonsoir Le Forum,
Bonsoir Haonv et FredOo,
Je reviens dans cette conversation pour à nouveau vous remercier pour votre intervention qui m'a été d'un très grand secours.
En effet, sur cette feuille où s'est appliquée la macrocommande, des opérations de tris de lignes doivent se faire avec à chaque renouvellement de tri sur une nouvelle catégorie de recherche, l'obligation de retrouver les lignes replacées comme à l'origine (d'où la mise en place des références absolues).
Il m'aurait fallu faire 5 990 appuis sur la touche F4 pour effectuer le travail de mise en place, avec des risques d'oublis de double-frappe (c'est arrivé l'anné dernière) !
Pas plus de 2 secondes avec votre macro, et sans oubli, avec un résultat impeccable : c'est vraiment époustouflant !
Encore merci à vous.
Cordialement,
Webperegrino

 

[haonv]

Re : Macro pour placer des références absolues dans mes formules

Re,

Juste pour te remercier d'être repassé par là.
Beaucoup "oublient" de le faire...au moins une fois.
Alors 2 fois, c'est très rare!

Amicalement