Help in simplifying code

**RaquelAR** · 11-16-2013, 09:02 PM

Hi!

I don't how to make some code of my macro more compact.

Here's and example of the code (it's too long to paste it all here):



Private Sub ParaUnidades()

With Application.WorksheetFunction

If ComboBox34.Value = "Unidades" And TextBox38.Value <> "" Then
    If DistribsOB.Value = True Then
    TextBox38.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Distribuitors", Sheet9.Range("H:H"), ComboBox13.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If FarmaciasIOB.Value = True Then
    TextBox38.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Pharmacy Distribuitors", Sheet9.Range("H:H"), ComboBox13.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If WHDOB.Value = True Then
    TextBox38.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Sales Force Wholesalers", Sheet9.Range("H:H"), ComboBox13.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If DepDentOB.Value = True Then
    TextBox38.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "DEPOSITOS DENTALES", Sheet9.Range("H:H"), ComboBox13.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If FarmaciasDOB.Value = True Then
    TextBox38.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Drugstores", Sheet9.Range("H:H"), ComboBox13.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If HiperOB.Value = True Then
    TextBox38.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Hypermarkets", Sheet9.Range("H:H"), ComboBox13.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If SuperOB.Value = True Then
    TextBox38.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Supermarkets", Sheet9.Range("H:H"), ComboBox13.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If ClubsOB.Value = True Then
    TextBox38.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Clubs", Sheet9.Range("H:H"), ComboBox13.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If TiendasDepOB.Value = True Then
    TextBox38.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Department Stores", Sheet9.Range("H:H"), ComboBox13.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox13.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
End If

If ComboBox34.Value = "Unidades" And TextBox39.Value <> "" Then
    If DistribsOB.Value = True Then
    TextBox39.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Distribuitors", Sheet9.Range("H:H"), ComboBox14.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If FarmaciasIOB.Value = True Then
    TextBox39.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Pharmacy Distribuitors", Sheet9.Range("H:H"), ComboBox14.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If WHDOB.Value = True Then
    TextBox39.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Sales Force Wholesalers", Sheet9.Range("H:H"), ComboBox14.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If DepDentOB.Value = True Then
    TextBox39.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "DEPOSITOS DENTALES", Sheet9.Range("H:H"), ComboBox14.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If FarmaciasDOB.Value = True Then
    TextBox39.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Drugstores", Sheet9.Range("H:H"), ComboBox14.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If HiperOB.Value = True Then
    TextBox39.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Hypermarkets", Sheet9.Range("H:H"), ComboBox14.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If SuperOB.Value = True Then
    TextBox39.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Supermarkets", Sheet9.Range("H:H"), ComboBox14.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If ClubsOB.Value = True Then
    TextBox39.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Clubs", Sheet9.Range("H:H"), ComboBox14.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
    If TiendasDepOB.Value = True Then
    TextBox39.Value = Format(.Round((.SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")) / .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("E:E"))) - (.SumIfs(Sheet9.Range("I:I"), Sheet9.Range("E:E"), "Department Stores", Sheet9.Range("H:H"), ComboBox14.Value) / 100) * .SumIf(Sheet2.Range("A:A"), ComboBox14.Value, Sheet2.Range("D:D")), 2), "Currency")
    End If
End If

End With

There are a lot more comboboxes like those.

Thank you!