Search code examples
excelvbacombobox

VBA : the value from ComboBox in IF THEN ELSE function

I'm newer in VBA. I would like to compare the value from my ComboBox, for exemple :

enter image description here

With the value from my sheet :

enter image description here

Could you tell me if my code is correct. The result is right but I would like to be sure that my function are correctly indented. My code is :

Public Sub INFO_PROTO(ByRef strQ As String)
Num_Ligne = Range("Chapeau_Partenaire").Row + 1
Worksheets("1 - Feuille de Suivi Commercial").Range("Calcul_CMA_Origine") = "1"
Worksheets("1 - Feuille de Suivi Commercial").Range("Calcul_Perf_Contrat_et_Orient") = "0"
Worksheets("1 - Feuille de Suivi Commercial").Range("Calcul_CMA_Perf_An") = "0"
    While Worksheets("6 - Liste des Partenaires").Cells(Num_Ligne, Range("Chapeau_Partenaire").Column) <> ""
        Do While strQ = Worksheets("6 - Liste des Partenaires").Cells(Num_Ligne, Range("Chapeau_Partenaire").Column) And Worksheets("6 - Liste des Partenaires").Cells(Num_Ligne, Range("Parametrage").Column) = "1"
        Worksheets("1 - Feuille de Suivi Commercial").Range("Calcul_CMA_Origine") = "1"
        Worksheets("1 - Feuille de Suivi Commercial").Range("Calcul_Perf_Contrat_et_Orient") = "1"
        Worksheets("1 - Feuille de Suivi Commercial").Range("Calcul_CMA_Perf_An") = "1"
        Num_Ligne = Num_Ligne + 1
        Loop
    Num_Ligne = Num_Ligne + 1
    Wend
End Sub
Solution

  • For cleaner view, if you prefer and try use line break when a single line code is too long:

    Public Sub INFO_PROTO(ByRef strQ As String)
Num_Ligne = Range("Chapeau_Partenaire").Row + 1

With Worksheets("1 - Feuille de Suivi Commercial")

    .Range("Calcul_CMA_Origine") = "1"
    .Range("Calcul_Perf_Contrat_et_Orient") = "0"
    .Range("Calcul_CMA_Perf_An") = "0"
    
        While Worksheets("6 - Liste des Partenaires").Cells(Num_Ligne, Range("Chapeau_Partenaire").Column) <> ""
            Do While strQ = Worksheets("6 - Liste des Partenaires").Cells(Num_Ligne, Range("Chapeau_Partenaire").Column) And _
            Worksheets("6 - Liste des Partenaires").Cells(Num_Ligne, Range("Parametrage").Column) = "1"
            .Range("Calcul_CMA_Origine") = "1"
            .Range("Calcul_Perf_Contrat_et_Orient") = "1"
            .Range("Calcul_CMA_Perf_An") = "1"
            Num_Ligne = Num_Ligne + 1
            Loop
        Num_Ligne = Num_Ligne + 1
        Wend
    
End With
End Sub