Need a trim

Question

ImZick 0 Posting Whiz in Training

11 Years Ago

Please can someone know how to trim this code. Any help will be appreciated

If Range("D8") = "EBU 2" Then
    If ComboBox1.Text = "Level 4" Then
        Sheets("Main").Range("C9") = Sheets("Main").Range("C9") + 1
        Sheets("Main").Range("D9") = Sheets("Main").Range("D9") + Range("K8")
    ElseIf ComboBox1.Text = "Level 5" Then
        Sheets("Main").Range("E9") = Sheets("Main").Range("E9") + 1
        Sheets("Main").Range("F9") = Sheets("Main").Range("F9") + Range("K8")
    ElseIf ComboBox1.Text = "Level 6" Then
        Sheets("Main").Range("G9") = Sheets("Main").Range("G9") + 1
        Sheets("Main").Range("H9") = Sheets("Main").Range("H9") + Range("K8")
    ElseIf ComboBox1.Text = "Level 7" Then
        Sheets("Main").Range("I9") = Sheets("Main").Range("I9") + 1
        Sheets("Main").Range("J9") = Sheets("Main").Range("J9") + Range("K8")
    ElseIf ComboBox1.Text = "Lost" Then
        Sheets("Main").Range("K9") = Sheets("Main").Range("K9") + 1
        Sheets("Main").Range("L9") = Sheets("Main").Range("L9") + Range("K8")
    End If

ElseIf Range("D8") = "EBU 5" Then
    If ComboBox1.Text = "Level 4" Then
        Sheets("Main").Range("C10") = Sheets("Main").Range("C10") + 1
        Sheets("Main").Range("D10") = Sheets("Main").Range("D10") + Range("K8")
    ElseIf ComboBox1.Text = "Level 5" Then
        Sheets("Main").Range("E10") = Sheets("Main").Range("E10") + 1
        Sheets("Main").Range("F10") = Sheets("Main").Range("F10") + Range("K8")
    ElseIf ComboBox1.Text = "Level 6" Then
        Sheets("Main").Range("G10") = Sheets("Main").Range("G10") + 1
        Sheets("Main").Range("H10") = Sheets("Main").Range("H10") + Range("K8")
    ElseIf ComboBox1.Text = "Level 7" Then
        Sheets("Main").Range("I10") = Sheets("Main").Range("I10") + 1
        Sheets("Main").Range("J10") = Sheets("Main").Range("J10") + Range("K8")
    ElseIf ComboBox1.Text = "Lost" Then
        Sheets("Main").Range("K10") = Sheets("Main").Range("K10") + 1
        Sheets("Main").Range("L10") = Sheets("Main").Range("L10") + Range("K8")
    End If

ElseIf Range("D8") = "EBU 6" Then
    If ComboBox1.Text = "Level 4" Then
        Sheets("Main").Range("C11") = Sheets("Main").Range("C11") + 1
        Sheets("Main").Range("D11") = Sheets("Main").Range("D11") + Range("K8")
    ElseIf ComboBox1.Text = "Level 5" Then
        Sheets("Main").Range("E11") = Sheets("Main").Range("E11") + 1
        Sheets("Main").Range("F11") = Sheets("Main").Range("F11") + Range("K8")
    ElseIf ComboBox1.Text = "Level 6" Then
        Sheets("Main").Range("G11") = Sheets("Main").Range("G11") + 1
        Sheets("Main").Range("H11") = Sheets("Main").Range("H11") + Range("K8")
    ElseIf ComboBox1.Text = "Level 7" Then
        Sheets("Main").Range("I11") = Sheets("Main").Range("I11") + 1
        Sheets("Main").Range("J11") = Sheets("Main").Range("J11") + Range("K8")
    ElseIf ComboBox1.Text = "Lost" Then
        Sheets("Main").Range("K11") = Sheets("Main").Range("K11") + 1
        Sheets("Main").Range("L11") = Sheets("Main").Range("L11") + Range("K8")
    End If

ElseIf Range("D8") = "EBU 7" Then
    If ComboBox1.Text = "Level 4" Then
        Sheets("Main").Range("C12") = Sheets("Main").Range("C12") + 1
        Sheets("Main").Range("D12") = Sheets("Main").Range("D12") + Range("K8")
    ElseIf ComboBox1.Text = "Level 5" Then
        Sheets("Main").Range("E12") = Sheets("Main").Range("E12") + 1
        Sheets("Main").Range("F12") = Sheets("Main").Range("F12") + Range("K8")
    ElseIf ComboBox1.Text = "Level 6" Then
        Sheets("Main").Range("G12") = Sheets("Main").Range("G12") + 1
        Sheets("Main").Range("H12") = Sheets("Main").Range("G12") + Range("K8")
    ElseIf ComboBox1.Text = "Level 7" Then
        Sheets("Main").Range("I12") = Sheets("Main").Range("I12") + 1
        Sheets("Main").Range("J12") = Sheets("Main").Range("H12") + Range("K8")
    ElseIf ComboBox1.Text = "Lost" Then
        Sheets("Main").Range("K12") = Sheets("Main").Range("K12") + 1
        Sheets("Main").Range("L12") = Sheets("Main").Range("L12") + Range("K8")
    End If

End If

and also this one

    With ComboBox1
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox2
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox3
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox4
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox5
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox6
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox7
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox8
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox9
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox10
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox11
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox12
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox13
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox14
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox15
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox16
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox17
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox18
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox19
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox20
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox21
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox22
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox23
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox24
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox25
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox26
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox27
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With
With ComboBox28
    .AddItem ("")
    .AddItem ("Level 4")
    .AddItem ("Level 5")
    .AddItem ("Level 6")
    .AddItem ("Level 7")
    .AddItem ("Lost")
End With

visual-basic

3 Contributors
5 Replies
145 Views
1 Day Discussion Span
Latest Post 11 Years Ago Latest Post by AleMonteiro

AleMonteiro 238 Can I pick my title?

11 Years Ago

The second code block could be something like this:

Function AddLevels(ComboBox combo) As Void
    With combo
        .AddItem ("")
        .AddItem ("Level 4")
        .AddItem ("Level 5")
        .AddItem ("Level 6")
        .AddItem ("Level 7")
        .AddItem ("Lost")
    End With
End Function

AddLevels(ComboBox21)
AddLevels(ComboBox22)
...

Obs.: I don't know if the Class name is really ComboBox, you need to verify it

The first could be something like this:

Dim Row As String
Dim Column1 As String
Dim Column2 As String

If Range("D8") = "EBU 2" Then
    Row = "9"
ElseIf Range("D8") = "EBU 5" Then
    Row = "10"
ElseIf Range("D8") = "EBU 7" Then
    Row = "11"
... ' Finish Else Ifs
End If

If ComboBox1.Text = "Level 4" Then
    Column1 = "C"
    Column2 = "D"
ElseIf ComboBox1.Text = "Level 5" Then
    Column1 = "E"
    Column2 = "F"
ElseIf ComboBox1.Text = "Level 6" Then
    Column1 = "G"
    Column2 = "H"
ElseIf ComboBox1.Text = "Level 7" Then
    Column1 = "I"
    Column2 = "J"
ElseIf ComboBox1.Text = "Lost" Then
    Column1 = "K"
    Column2 = "L"
End If

Sheets("Main").Range(Column1 & Row) = Sheets("Main").Range(Column1 & Row) + 1
Sheets("Main").Range(Column2 & Row) = Sheets("Main").Range(Column2 & Row) + Range("K8")

AleMonteiro 238 Can I pick my title?

11 Years Ago

Sorry, but I don't really use VB. But the logic should be enough, shouldn't it?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

ImZick 0 Posting Whiz in Training · Answer 1 · 2013-01-04T00:39:10+00:00

Nice great logic :) this could help me a lot thanks!

ImZick 0 Posting Whiz in Training · Answer 2 · 2013-01-04T00:47:37+00:00

I forgot to tell you my code is under in VBA...

so this code does not work

Function AddLevels(ComboBox combo) As Void

AndreRet 526 Senior Poster · Answer 3 · 2013-01-04T08:39:37+00:00

AndreRet 526 Senior Poster

11 Years Ago

Function AddLevels(combo As ComboBox)