CODEWITHSUNDEEP
vbaif-statement
Krusing
Krusing

Reputation: 307

How to avoid using too many if statements in VBA?

I want to exclude blank rows from all my ComboBoxes in a form, but can't find a practical way of doing so. I came up with this example:

Dim Foo as Worksheet: Foo = Sheets("Foo")

With UserForm
    'The ComboBoxes have unique names, this code is just an example
    For i = 0 To Foo.UsedRange.Rows.Count
        If Foo.Cells(i + 1, 1) <> "" Then
            .ComboBoxFoo.AddItem (Foo.Cells(i + 1, 1))
        End If
        If Foo.Cells(i + 1, 2) <> "" Then
            .ComboBoxBar.AddItem (Foo.Cells(i + 1, 2))
        End If
        If Foo.Cells(i + 1, 3) <> "" Then
            .ComboBoxBaz.AddItem (Foo.Cells(i + 1, 3))
        End If

        '[etc. etc.]

    Next

End With

I have plenty of ComboBoxes in my form, and all above statements look the same. There must be a more efficient way?

Solved the blank and unique issue with

Dim Foo As Worksheet: Foo = Sheet("Foo")
Dim Unique As Boolean

With UserForm
    'The ComboBoxes have unique names, this code is just an example
    For i = 0 To Foo.UsedRange.Rows.Count
        If Not IsEmpty(Foo.Cells(i + 1, 1) Then
            Unique = True
            For j = 0 To .ComboBoxFoo.ListCount - 1
                If .ComboBoxFoo.List(j) = Foo.Cells(i + 1, 1) Then
                    Unique = False
                Else: Unique = True
                End If
            Next j
            If Unique Then .ComboBoxFoo.AddItem (Foo.Cells(i + 1, 1))
        End If


        '[etc. etc.]

    Next i

End With

Upvotes: 0

Views: 1247

Answers (2)

SierraOscar
SierraOscar

Reputation: 17637

How About:

Sub SO()
    For Each cell In Intersect(ActiveSheet.UsedRange, Range("A:C")).Cells
        If Not cell.Value = "" Then AssignToCB cell.Column, cell.Value
    Next
End Sub

Private Sub AssignToCB(cbNumber As Integer, value As String)
    Select Case cbNumber
        Case 1: ComboBoxFoo.AddItem value
        Case 2: ComboBoxBar.AddItem value
        Case 3: ComboBoxBaz.AddItem value
    End Select
End Sub

Upvotes: 1

mielk
mielk

Reputation: 3940

You can try the code below:

Dim Foo As Worksheet: Foo = Sheets("Foo")

With UserForm
    For i = 0 To Foo.UsedRange.Rows.Count
        For j = 1 To 10
            If Foo.Cells(i + 1, j) <> "" Then
                Call .Controls("ComboBox" & j).AddItem(Foo.Cells(i + 1, j))
            End If
        Next j
    Next

End With

It uses second For ... Next loop nested inside the first one. Combo boxes are referred by name, using .Controls collection of UserForm.

Upvotes: 4

Related Questions