CODEWITHSUNDEEP
vbaexcel
Anker
Anker

Reputation: 86

Use of loop with if function in a VBA Userform

I'm working my way though the various ways to use IF function and now I'm a little stuck with loops.

I know how to just loop if I have to put it in to cells, but I don't know how to loop it when there is a IF and when I have to take the value from a combobox.

This is the code without loop

cR = ComboBox2.Value
If IsNull(cR) = False Then
tsheet.Range("B1").Value = cR
End If
cR = ""
cR = ComboBox3.Value
If IsNull(cR) = False Then
tsheet.Range("B2").Value = cR
End If
cR = ""
cR = ComboBox4.Value
If IsNull(cR) = False Then
tsheet.Range("B3").Value = cR
End If

So I can imagine the loop would look something like:

For i = 2 To 4
For k = 1 To 3
If Not ComboBox(i).Value = vbNullString
Range("B", k).Value = ComboBox(i).Value
next
End Sub

Upvotes: 0

Views: 188

Answers (2)

AjW-Dred
AjW-Dred

Reputation: 23

If you don't have many comboboxes you could link the comboboxes to a range of cells and then use Excel formulas; e.g. B2.formula =IF(Combo2Val)<>"",Combo2Val,"") No need for VBA.

Upvotes: 1

SJR
SJR

Reputation: 23081

You can do it thus (assuming the code is in a userform).

Dim i As Long

For i = 2 To 4
    If Me.Controls("ComboBox" & i).Value <> vbNullString Then
        Range("B" & i - 1).Value = Me.Controls("ComboBox" & i).Value
    End If
Next

Upvotes: 1

Related Questions