Use of loop with if function in a VBA Userform

Question

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

If isn't a function. Also, the cR = "" in the original code is pointless. You don't have to reset a variable before reusing it. — John Coleman
– John Coleman, Commented Oct 13, 2017 at 10:29
I was not sure about that cR that's why I put it there. I was trying to find something about it if I had to reset it but didn't find anything.. thanks for the input. if "IF" is not a function what do I call it? — Anker
– Anker, Commented Oct 13, 2017 at 10:34

SJR · Accepted Answer · 2017-10-13 10:46:03Z

1

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

answered Oct 13, 2017 at 10:46

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anker Over a year ago

that was a clever way to take care of the cell number. didn't think of that. Thanks for the help SJR

AjW-Dred · Accepted Answer · 2017-10-13 11:16:28Z

1

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.

answered Oct 13, 2017 at 11:16

AjW-Dred

235 bronze badges

Collectives™ on Stack Overflow

Use of loop with if function in a VBA Userform

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related