VBA looping names from the list

[Melkor]

Hi guys,

Can you please help me with this issue?
I have a list with fruits (I2:I13), and I want to create VBA form with one lable and one button, where on the lable it will be written the name of the fruit (first on the list), and by clicking on the button it should change the name of the fruit which is next on the list, when it reach the end of the list it shoud start with first name on the list.

Just to mention, the list with fruits will be dinamic according to availability.

 

[mse330]

Welcome to the forum

Try below code for the command button

Private Sub CommandButton1_Click()

Dim Rg As Range

Set Rg = Columns("i").Find(Label1.Caption, lookat:=xlWhole)
If Rg Is Nothing Then Label1.Caption = [i2] Else Label1.Caption = IIf(Rg.Offset(1) = "", [i2], Rg.Offset(1))

End Sub

 

[Melkor]

Hi mse330, than you for the warm welcome

Tried the code above, but on click it's showing just first article(name).

 

[mse330]

Is your data in column i ?

 

[Melkor]

That is right, the list is in column i.

 

[Melkor]

It didn't work, because the data in I column was formula, so when I pasted just values, it was fixed.

Thank you, one more time

 

[mse330]

If you need to retain your formulas & the code to work, you may add LookIn:=xlValues as shown in the below code

Private Sub CommandButton1_Click()

Dim Rg As Range

Set Rg = Columns("i").Find(Label1.Caption, lookat:=xlWhole, LookIn:=xlValues)
If Rg Is Nothing Then Label1.Caption = [i2] Else Label1.Caption = IIf(Rg.Offset(1) = "", [i2], Rg.Offset(1))

End Sub