Etendre une formule sur lignes et colonnes multiples

Bonjour à toutes et à tous,

Voici mon problème :

J'ai une formule qui me permet de calculer la rémunération d'un salarié en fonction de son chiffre d'affaire et de différents critères.

Public Function Rémunération(CA, Smin, Smax, Hmin, Hmax, Taux As Single) As Single
Dim Rému As Single

If CA <= Hmin Then
    Rému = Smin
ElseIf ((CA >= Hmin) And (CA < Hmax)) Then
    Rému = Smin + Taux * (CA - Hmin)
Else:
    Rému = Smax
End If

    Rémunération = Rému

End Function

Avec :

Ca = Chiffre d'Affaire

Smin = salaire min

Smax = salaire max

Hmin = Chiffre d'affaire min de déclenchement de la part variable du salaire

Mmax = Chiffre d'affaire max à partir duquel on atteint le plafond de rémunération

Taux = coefficient de la part variable.

La fonction fonctionne, le problème provient de l'application automatique de cette fonction à X cellules et Y colonnes.

J'ai X salariés, chacun ayant des Smin, Smax, Hmin... différents.

Pour chaque salarié, je souhaite avoir l'évolution de sa rémunération en fonction de son Chiffre d'affaire.

Sur la feuille "Catégorie1" son listés en lignes chaque salarié (cellules vides) avec leurs coefficients respectifs.

Sur la feuille "TabCatégorie1" son listé en colonnes chaque salarié avec l'évolution de leur rémunération calculée avec la fonction "Rémunération"

Mon souci : je n'arrive pas à automatiser ce process

(Sur le document, j'ai fait à la main les trois premiers)

De façon pratique ça donne ceci (sur le fichier Test) :

En B4 j'ai :

=Rémunération($A4;Catégorie1!D$4;Catégorie1!E$4;Catégorie1!F$4;Catégorie1!G$4;Catégorie1!H$4)

En B5 :

=Rémunération($A5;Catégorie1!D$4;Catégorie1!E$4;Catégorie1!F$4;Catégorie1!G$4;Catégorie1!H$4)

En B6 :

=Rémunération($A6;Catégorie1!D$4;Catégorie1!E$4;Catégorie1!F$4;Catégorie1!G$4;Catégorie1!H$4)

etc...

En C4 :

=Rémunération($A4;Catégorie1!D$5;Catégorie1!E$5;Catégorie1!F$5;Catégorie1!G$5;Catégorie1!H$5)

etc...

En D4 :

=Rémunération($A4;Catégorie1!D$6;Catégorie1!E$6;Catégorie1!F$6;Catégorie1!G$6;Catégorie1!H$6)

etc...

Donc je veux en E4 :

=Rémunération($A4;Catégorie1!D$7;Catégorie1!E$7;Catégorie1!F$7;Catégorie1!G$7;Catégorie1!H$7)

En E5 :

=Rémunération($A5;Catégorie1!D$7;Catégorie1!E$7;Catégorie1!F$7;Catégorie1!G$7;Catégorie1!H$7)

En E6 :

=Rémunération($A6;Catégorie1!D$7;Catégorie1!E$7;Catégorie1!F$7;Catégorie1!G$7;Catégorie1!H$7)

Anyone help please ?

(j'ai déjà tenté l'enregistrement de macro, sans succès)

Bonjour

Quitte à écrire une formule par cellule, pourquoi passer par une fonction personnalisée alors que tu peux avoir le résultat avec une formule unique

En B4, incrémentée sur tout le tableau de la Feuille TabCatégorie1

=SI($A4<=INDIRECT("Catégorie1!$F"&COLONNE()+2);INDIRECT("Catégorie1!$D"&COLONNE()+2);SI($A4>INDIRECT("Catégorie1!$F"&COLONNE()+2);MIN(INDIRECT("Catégorie1!$E"&COLONNE()+2);INDIRECT("Catégorie1!$D"&COLONNE()+2)+(($A4-INDIRECT("Catégorie1!$F"&COLONNE()+2))*INDIRECT("Catégorie1!$H"&COLONNE()+2)))))

Cordialement

Bonsoir Amadéus et le forum,

J'ai essayé d'appliquer cette formule (sans réussir à la comprendre, je l'avoue) à ma feuille Excel,

mais je n'ai qu'une erreur comme résultat.

Avez-vous essayé avec le document que j'avais joint ?

Bien cordialement

Bonjour

Avez-vous essayé avec le document que j'avais joint ?

Oui, évidemment

Voir fichier

Cordialement

Merci Amadeus cela fonctionne !

Maintenant je bloque à l'étape d'après qui est d'étendre le calcul à toutes les lignes et colonnes (sauf quand il n'y a plus de données)

Première étape ok : j'étends la formule sur toute la colonne B dont le nombre de lignes est fixe

            Sheets("TabCat1").Select
    Range("B4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC1<=INDIRECT(""Cat1!$F""&COLUMN()+2),INDIRECT(""Cat1!$D""&COLUMN()+2),IF(RC1>INDIRECT(""Cat1!$F""&COLUMN()+2),MIN(INDIRECT(""Cat1!$E""&COLUMN()+2),INDIRECT(""Cat1!$D""&COLUMN()+2)+((RC1-INDIRECT(""Cat1!$F""&COLUMN()+2))*INDIRECT(""Cat1!$H""&COLUMN()+2)))))"
    Range("B4").Select
    Selection.AutoFill Destination:=Range("B4:B404")

Deuxième étape pas ok : étendre la formule sur toutes les colonnes jusqu'au numéro de colonne Nbr variable

              Nbr = Range("Cat1!B4").End(xlUp).Offset(1, 0).Column
    Range("B4:B404").Select
    Selection.AutoFill Destination:=Range("C4", Range(Nbr))

Il s'agit probablement juste de la syntaxe de la dernière ligne de code...

Problème résolu.

Ci-dessous le code pour ceux qui pourraient être intéressés :

With Sheets("TabCat1")
    nbr = Sheets("Cat1").Cells(Rows.Count, 1).End(xlUp).Row - 2
    .Range("B4").FormulaR1C1 = _
        "=IF(RC1<=INDIRECT(""Cat1!$F""&COLUMN()+2),INDIRECT(""Cat1!$D""&COLUMN()+2),IF(RC1>INDIRECT(""Cat1!$F""&COLUMN()+2),MIN(INDIRECT(""Cat1!$E""&COLUMN()+2),INDIRECT(""Cat1!$D""&COLUMN()+2)+((RC1-INDIRECT(""Cat1!$F""&COLUMN()+2))*INDIRECT(""Cat1!$H""&COLUMN()+2)))))"
    .Range("B4").AutoFill Destination:=.Range("B4:B404")
    .Range("B4:B404").AutoFill Destination:=.Range("B4:B404").Resize(, nbr)
End With

Merci !