Range for Column Index

[isaacv22]

Hello,

I'm currently trying to put in a range in the columnindex but keep getting an error when trying to reference columns F-J in the code below.

thanks,

Private Sub ListBox1_Click()

'Find last row

LastRow = form.Cells(Rows.Count, 1).End(xlUp).Row

Me.ListBox2.Clear

CurVal = Me.ListBox1.Value

For X = 2 To LastRow
    If form.Cells(X, "k") = CurVal Then
        'Found a match; populate listbox2
        Me.ListBox2.AddItem form.Cells(X, "F:J")
    End If

Next X
      
End Sub

 

[NdNoviceHlp]

Not real sure what you want for an outcome but you can trial this untested code. HTH. Dave
note: adjust "userform1" as needed. Also, it's unclear if "form" is a sheet name. Adjust to suit

Private Sub ListBox1_Click()
Dim LastRow As Integer, CurVal As Variant, X As Integer
'Find last row
LastRow = Sheets("form").Cells(Rows.Count, "k").End(xlUp).Row

CurVal = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex)

UserForm1.ListBox2.Clear

For X = 2 To LastRow
With Sheets("form")
If .Cells(X, "k") = CurVal Then
'Found a match; populate listbox2
UserForm1.ListBox2.RowSource = .Range(.Cells(X, "F"), Cells(X, "J")).Address
Exit For
End If
End With
Next X
End Sub

 

[isaacv22]

Not real sure what you want for an outcome but you can trial this untested code. HTH. Dave
note: adjust "userform1" as needed. Also, it's unclear if "form" is a sheet name. Adjust to suit

Private Sub ListBox1_Click()
Dim LastRow As Integer, CurVal As Variant, X As Integer
'Find last row
LastRow = Sheets("form").Cells(Rows.Count, "k").End(xlUp).Row

CurVal = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex)

UserForm1.ListBox2.Clear

For X = 2 To LastRow
With Sheets("form")
If .Cells(X, "k") = CurVal Then
'Found a match; populate listbox2
UserForm1.ListBox2.RowSource = .Range(.Cells(X, "F"), Cells(X, "J")).Address
Exit For
End If
End With
Next X
End Sub

Thank you very much this worked out perfect!

Though now I'm getting a bug error on the UserForm2.ListBox2.Clear code. I Would like the entries of Listbox2 be cleared after each selection in Listbox1

Thanks,

 

[NdNoviceHlp]

Hmm... are you sure that it is "userform2" (the code is for Userform1) and that it is listbox2? The syntax is correct so I'm not sure why there would be an error? If the rest of this code is not for userform2, then userform2 would have to be hidden or the code would fail. Dave

 

[isaacv22]

Hmm... are you sure that it is "userform2" (the code is for Userform1) and that it is listbox2? The syntax is correct so I'm not sure why there would be an error? If the rest of this code is not for userform2, then userform2 would have to be hidden or the code would fail. Dave

It seems that I had to remove that part of the code and adjust some property values for listbox2 and it's now working. Thank you for all your help I greatly appreciate it!

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave