XL 2016 Simplification code et boucle

[Michel_ja]

Bonjour à tous,
j'ai écrit un code vba qui fonctionne mais.... il est vraiment moche et je pense qu'il doit exister une version bien plus rapide et performante en temps (et mémoire) car je dois appliquer celui-ci sur un fichier de 30,000 lignes.
J'ai joint un fichier pour expliquer. Dans les lignes de la colonne B j'ai des marques et modèles de voitures et dans les colonnes C à H les types de moteurs disponibles pour chacun d'eux. Je souhaiterai que la valeur de cellule I11 et celles au-dessous, il y ait une concaténation des en-tête de colonnes pour les moteurs disponibles (c'est à dire les cellules > 0).
Comment modifier le code en ajoutant une boucle de la ligne 11 à la dernière ligne non vide ?
Et est-ce que vous auriez une idée pour que le code prenne en compte toutes les combinaisons possibles ? Par exemple, une offre essence avec tous les autres, puis seulement 1 autre, 2 autres, etc. puis sans essence, etc. J'avoue que dans mon code, certaines ont dû être oubliées.

Je vous remercie. Ci-bas mon code actuel :-(
Michel


Sub Macro1()
Dim FinChecks As Long
Dim Propulsions As String

Sheets("CS4 & Propulsions").Select
ActiveWorkbook.Worksheets("CS4 & Propulsions").Sort.SortFields.Clear 'Supprime les tris automatiques potentiels
FinChecks = Range("B" & Rows.Count).End(xlUp).Row

Range("I11").Select
Propulsions = Range("I11").Text

'Gasoline
If Range("C11").Value > 0 And WorksheetFunction.Sum(Range("D11:H11")) = 0 Then Range("I11").Value = "100% Gas"
If Range("C11").Value > 0 And Range("D11").Value > 0 And WorksheetFunction.Sum(Range("E11:H11")) = 0 Then Range("I11").Value = "Gas / Diesel"
If Range("C11").Value > 0 And Range("D11").Value > 0 And WorksheetFunction.Sum(Range("E11:H11")) = 0 Then Range("I11").Value = "Gas / Diesel"
If Range("C11").Value > 0 And Range("D11").Value = 0 And Range("E11").Value > 0 And WorksheetFunction.Sum(Range("F11:H11")) = 0 Then Range("I11").Value = "Gas / CNG/LPG"
If Range("C11").Value > 0 And WorksheetFunction.Sum(Range("D11:E11")) = 0 And Range("F11").Value > 0 And WorksheetFunction.Sum(Range("G11:H11")) = 0 Then Range("I11").Value = "Gas / Hybrid Gas"
If Range("C11").Value > 0 And Range("D11").Value > 0 And Range("F11").Value > 0 And WorksheetFunction.Sum(Range("G11:H11")) = 0 Then Range("I11").Value = "Gas / Diesel / Hybrid Gas"
If Range("C11").Value > 0 And Range("D11").Value > 0 And Range("F11").Value = 0 And Range("G11").Value > 0 And Range("H11").Value = 0 Then Range("I11").Value = "Gas / Diesel / Hybrid Diesel"
If Range("C11").Value > 0 And Range("D11").Value > 0 And Range("F11").Value > 0 And Range("G11").Value > 0 And Range("H11").Value = 0 Then Range("I11").Value = "Gas / Diesel / Hybrid Gas / Hybrid Diesel"
If Range("C11").Value > 0 And WorksheetFunction.Sum(Range("D11:E11")) = 0 And Range("F11").Value > 0 And WorksheetFunction.Sum(Range("G11:H11")) = 0 Then Range("I11").Value = "Gas / Hybrid Gas"
If Range("C11").Value > 0 And Range("D11").Value = 0 And Range("E11").Value > 0 And Range("F11").Value > 0 And WorksheetFunction.Sum(Range("G11:H11")) = 0 Then Range("I11").Value = "Gas / CNG/LPG / Hybrid Gas"
If Range("C11").Value > 0 And Range("D11").Value > 0 And Range("E11").Value > 0 And Range("F11").Value > 0 And WorksheetFunction.Sum(Range("G11:H11")) = 0 Then Range("I11").Value = "Gas / Diesel / CNG/LPG / Hybrid Gas"
If Range("C11").Value > 0 And Range("D11").Value > 0 And Range("E11").Value > 0 And Range("F11").Value > 0 And WorksheetFunction.Sum(Range("G11:H11")) = 0 Then Range("I11").Value = "Gas / Diesel / CNG/LPG / Hybrid Gas"

If Range("C11").Value > 0 And WorksheetFunction.Sum(Range("D11:F11")) = 0 And Range("G11").Value > 0 And Range("H11").Value = 0 Then Range("I11").Value = "Gas / Hybrid Diesel"
If Range("C11").Value > 0 And Range("D11").Value > 0 And WorksheetFunction.Sum(Range("E11:F11")) = 0 And Range("G11").Value > 0 And Range("H11").Value = 0 Then Range("I11").Value = "Gas / Diesel / Hybrid Diesel"
If Range("C11").Value > 0 And Range("D11").Value > 0 And Range("E11").Value > 0 And Range("F11").Value = 0 And Range("G11").Value > 0 And Range("H11").Value = 0 Then Range("I11").Value = "Gas / Diesel / CNG/LPG / Hybrid Diesel"
If Range("C11").Value > 0 And Range("D11").Value > 0 And Range("E11").Value > 0 And Range("F11").Value > 0 And Range("G11").Value > 0 And Range("H11").Value = 0 Then Range("I11").Value = "Gas / Diesel / CNG/LPG / Hybrid Gas / Hybrid Diesel"
If Range("C11").Value > 0 And WorksheetFunction.Sum(Range("D11:G11")) = 0 And Range("H11").Value > 0 Then Range("I11").Value = "Gas / Electric"

If Range("C11").Value > 0 And Range("D11").Value = 0 And Range("F11").Value > 0 And Range("G11").Value = 0 And Range("H11").Value > 0 Then Range("I11").Value = "Gas / Hybrid Gas / Electric"
If Range("C11").Value > 0 And Range("D11").Value = 0 And Range("F11").Value = 0 And Range("G11").Value > 0 And Range("H11").Value > 0 Then Range("I11").Value = "Gas / Hybrid Diesel / Electric"
If Range("C11").Value > 0 And Range("D11").Value = 0 And Range("E11").Value > 0 And WorksheetFunction.Sum(Range("E11:G11")) = 0 And Range("H11").Value > 0 Then Range("I11").Value = "Gas / CNG/LPG / Electric"
If Range("C11").Value > 0 And Range("D11").Value = 0 And Range("E11").Value > 0 And Range("F11").Value > 0 And Range("G11").Value = 0 And Range("H11").Value > 0 Then Range("I11").Value = "Gas / CNG/LPG / Hybrid Gas / Electric"
If Range("C11").Value > 0 And Range("D11").Value = 0 And Range("E11").Value > 0 And Range("F11").Value = 0 And Range("G11").Value > 0 And Range("H11").Value > 0 Then Range("I11").Value = "Gas / CNG/LPG / Hybrid Diesel / Electric"

If Range("C11").Value > 0 And Range("D11").Value > 0 And WorksheetFunction.Sum(Range("E11:G11")) = 0 And Range("H11").Value > 0 Then Range("I11").Value = "Gas / Diesel / Electric"
If Range("C11").Value > 0 And Range("D11").Value > 0 And Range("F11").Value > 0 And Range("G11").Value = 0 And Range("H11").Value > 0 Then Range("I11").Value = "Gas / Diesel / Hybrid Gas / Electric"
If Range("C11").Value > 0 And Range("D11").Value > 0 And Range("F11").Value = 0 And Range("G11").Value > 0 And Range("H11").Value > 0 Then Range("I11").Value = "Gas / Diesel / Hybrid Diesel / Electric"
If Range("C11").Value > 0 And Range("D11").Value > 0 And Range("E11").Value > 0 And Range("F11").Value > 0 And Range("G11").Value = 0 And Range("H11").Value > 0 Then Range("I11").Value = "Gas / Diesel / CNG/LPG / Hybrid Gas / Electric"
If Range("C11").Value > 0 And Range("D11").Value > 0 And Range("E11").Value > 0 And Range("F11").Value = 0 And Range("G11").Value > 0 And Range("H11").Value > 0 Then Range("I11").Value = "Gas / Diesel / CNG/LPG / Hybrid Diesel / Electric"


'Diesel
If Range("C11").Value = 0 And Range("D11").Value > 0 And WorksheetFunction.Sum(Range("E11:H11")) = 0 Then Range("I11").Value = "100% Diesel"
If Range("C11").Value = 0 And Range("D11").Value > 0 And Range("E11").Value > 0 And WorksheetFunction.Sum(Range("F11:H11")) = 0 Then Range("I11").Value = "Diesel / CNG/LPG"
If Range("C11").Value = 0 And Range("D11").Value > 0 And Range("F11").Value > 0 And WorksheetFunction.Sum(Range("G11:H11")) = 0 Then Range("I11").Value = "Diesel / Hybrid Gas"
If Range("C11").Value = 0 And Range("D11").Value > 0 And Range("F11").Value = 0 And Range("G11").Value > 0 And Range("H11").Value = 0 Then Range("I11").Value = "Diesel / Hybrid Diesel"
If Range("C11").Value = 0 And Range("D11").Value > 0 And Range("F11").Value > 0 And Range("G11").Value > 0 And Range("H11").Value = 0 Then Range("I11").Value = "Diesel / Hybrid Gas / Hybrid Diesel"
If Range("C11").Value = 0 And Range("D11").Value > 0 And Range("F11").Value > 0 And WorksheetFunction.Sum(Range("E11:G11")) = 0 And Range("H11").Value = 0 Then Range("I11").Value = "Diesel / Electric"

'Hybrid Gas
If WorksheetFunction.Sum(Range("C11:E11")) = 0 And Range("F11").Value > 0 And WorksheetFunction.Sum(Range("G11:H11")) = 0 Then Range("I11").Value = "100% Hybrid Gas"
If WorksheetFunction.Sum(Range("C11:E11")) = 0 And Range("F11").Value > 0 And Range("G11").Value > 0 And Range("H11").Value = 0 Then Range("I11").Value = "Hybrid Gas / Hybrid Diesel"
If WorksheetFunction.Sum(Range("C11:E11")) = 0 And Range("F11").Value > 0 And Range("G11").Value > 0 And Range("H11").Value > 0 Then Range("I11").Value = "Hybrid Gas / Hybrid Diesel / Electric"
If WorksheetFunction.Sum(Range("C11:E11")) = 0 And Range("F11").Value > 0 And Range("G11").Value = 0 And Range("H11").Value > 0 Then Range("I11").Value = "Hybrid Gas / Electric"

'Hybrid Diesel
If WorksheetFunction.Sum(Range("C11:F11")) = 0 And Range("G11").Value > 0 And Range("H11").Value = 0 Then Range("I11").Value = "100% Hybrid Diesel"
If WorksheetFunction.Sum(Range("C11:F11")) = 0 And Range("G11").Value > 0 And Range("H11").Value > 0 Then Range("I11").Value = "Hybrid Diesel / Electric"

'Electric
If WorksheetFunction.Sum(Range("C11:G11")) = 0 And Range("H11").Value > 0 Then Range("I11").Value = "100% Electric"


End Sub

 

[sylvanu]

Bonjour Michel,
Un essai avec une fonction perso. Le code est un peu plus simple.
A voir si cela remplit toutes les fonctions demandées.
A+

 

[mapomme]

Bonsoir @Michel_ja, @sylvanu ,

Un code avec une seule instruction (voire deux):

Sub TYPER()
With Range("Tableau1[type]")
   .FormulaR1C1 = _
        "=MID(REPT(""; "" & R10C[-6],RC[-6]>0) & REPT(""; "" & R10C[-5],RC[-5]>0) & " & _
        "REPT(""; "" & R10C[-4],RC[-4]>0) & REPT(""; "" & R10C[-3],RC[-3]>0) & " & _
        "REPT(""; "" & R10C[-2],RC[-2]>0) & REPT(""; "" & R10C[-1],RC[-1]>0),3,999)"
        '.Value = .Value   ' (activer cette instruction en retirant l'apostrophe de tête)
End With
End Sub

Si vous ne désirez en résultat que les valeurs résultant des formules (et non les formules), retirez la première apostrophe de l'instruction:
'.Value = .Value ' (activer cette instruction en retirant l'apostrophe de tête)


edit: v2 (simplifiée / v1)

 

[sylvanu]

MaPomme, ma fonction est plus simple. Mais Michel n'a toujours pas répondu. Donc toujours en attente, je ne sais pas si ça marche ou pas.

 

[mapomme]

Bonjour @sylvanu

MaPomme, ma fonction est plus simple

Je suis d'accord (j'ai simplifié la formule par rapport à la v2 ). La méthode par formule (directe ou appliquée par VBA) est plus rapide.
C'était pour montrer à @Michel_ja que c'était faisable aussi par formule sans VBA.
C'est d'autant plus intéressant qu'il utilise un tableau structuré. Il suffit de mettre la formule ci-dessous en I11 et la colonne se remplit automatiquement.
=STXT(REPT("; " & C$10;C11>0) & REPT("; " & D$10;D11>0) & REPT("; " & E$10;E11>0) & REPT("; " & F$10;F11>0) & REPT("; " & G$10;G11>0) & REPT("; " & H$10;H11>0);3;999)

Remarque : perso et d'emblée, j'aurais aussi fait par macro

 

[Michel_ja]

MaPomme, ma fonction est plus simple. Mais Michel n'a toujours pas répondu. Donc toujours en attente, je ne sais pas si ça marche ou pas.

Bonsoir Sylvanu. Merci beaucoup pour ta réponse. J'étais en déplacement vendredi aprem mais je compte bien tester ta solution. Je reviens vers toi aussi vite que possible. Michel

 

[mapomme]

Bonjour à tous,

Manifestement, je sens le pâté...

 

[Staple1600]

Bonsoir le fil,

mapomme
Qu'est-ce que tu as contre le pâté ?
Personnellement, j'adore le pâté, et j'aime bien les pommes
Mais j'ai jamais goûté de pâté aux pommes.

 

[mapomme]

Bonjour @Staple1600

En Anjou, nous avons le pâté aux prunes (variété reine-claude)

 

[Michel_ja]

Bonjour Sylvanu, mapomme et Staple1600. Je n'ai moi rien contre les pommes, et encore moins lorsqu'elles cherchent à me sortir du pâté !!
J'ai testé ta solution Sylvanu et ça marche très bien, et ça prend un rien de temps. J'ai mis plus de temps moi à comprendre qu'il ne fallait pas lancer la fonction depuis la fenêtre VBA mais tout juste entrer la formule dans la 1ère cellule et copier coller vers le bas.
Mapomme, ton fichier joint est aussi une bonne solution. Je découvre la formule REPT.
Merci à vous les gars, bon dimanche.
Michel

 

[sylvanu]

Bonjour le fil,
Une fonction VBA est appelée dans une feuille exactement comme n'importe quelle fonction Excel. On peut donc s'inventer ses propres formules d'où la puissance. Une fois écrite, on ne touche plus au VBA.
Un "bon" tuto sur le sujet : https://www.excel-downloads.com/resources/fonctions-personnelles-utilite-simplicite-et-methode.1135/