for next loop combobox

[Reggieneo]

Hi All,

I have been trying to use a name range as a combobox rowsource.

I need to loop all the name ranges in my range ("T2: T18") and pickup the matching name range that is provided by my other combobox "cmbDesc".

else if no match then use this "no name range" instead. however my loop constantly end up in the "no name range" and never get to see the match even I have the name range accurately inputted in my searching combobox.

please see

Dim ranges As Variant
Dim cel As Variant
  
  ranges = cmbDesc
  
  For Each cel In ThisWorkbook.Sheets("Sheet1").Range("T2:T18")
  
  If cel.Value = ranges Then
  Me.cmbItemParts.RowSource = cel
  
  Else
  
  Me.cmbItemParts.RowSource = "List_of_Items"
  End If
  Next cel

End Sub

much thanks,

reggieneo

 

[Kenneth Hobson]

Your code is only setting it if T18 is ranges.

You might want to loop and exit instead. e.g.

Sub Test()
  Dim ranges As String, r As Range
  On Error GoTo EndSub
  ranges = "Ken"
  Set r = Range(ranges)
  MsgBox "Range(""Ken"") was found."
  Exit Sub
EndSub:
  MsgBox "Range(""Ken"") was not found."
End Sub

 

[Reggieneo]

Hi Ken,
thanks for reply. sure it can say if the name range is in my name range column (T2:T28), but however I am at lost how to use this and refer to this to pickup or refer to the matching range provided by my combobox "cmbDesc".

 

[Reggieneo]

Hi,
I have sorted this issue although its not dynamic. I have tried getting into lastrow but I kept getting type mismatch error. for now I will stick to it until I master how to do this.
please see:

 For Each cel In ThisWorkbook.Sheets("Sheet1").Range("T2:T18")
 
  If cel.Value = cmbDesc.Value Then
  Me.cmbItemParts.RowSource = cel
 
 
  Else
 
  Me.cmbItemParts.RowSource = "List_of_Items"
  End If
  Next cel


thanks

 

[Kenneth Hobson]

I don't know what your issue is with lastrow.

Normally, I just use List. Maybe this will get you on track.

Private Sub UserForm_Initialize()
  Dim r As Range
  With Sheets("Sheet1")
    Set r = .Range("T2", .Cells(Rows.Count, "T").End(xlUp))
  End With
  cmbDesc.List = r.Value
End Sub
Private Sub cmbDesc_Change()
  On Error Resume Next
  cmbItemParts.RowSource = Range(cmbDesc.Value).Address
  If Err.Number = 1004 Then cmbItemParts.RowSource = "List_of_Items"
End Sub