To check the Textbox Value in combobox. Split its value from combobox and Range address to display in Another Texbox

[NimishK]

Hello I get the Names and its range displayed in combobox with below code, want to check if the Name typed in textbox1 should however match the name in combobox with msgbox displayed "Exists" and its range displayed in another Textbox eg. Textbox2
I am unable to get the Range Address in Textbox2

The List displayed in ComboBox1 as follows ie with Name and Range
596501 A2:AI6
102603 A7:AI9
NIM-K-102 556304 A10:AI15 ' The Name here is different with space in between

Sub GetNames()
  Dim Ray() As String
  Dim c As Range, LastA As Range
  Dim rws As Long, k As Long
  
  Set LastA = Range("A" & Range("AC" & Rows.Count).End(xlUp).Row)
  For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
    rws = 1
    If IsEmpty(c.Offset(1).Value) And c.Address <> LastA.Address Then rws = rws + Range(c, LastA).SpecialCells(xlBlanks).Areas(1).Rows.Count
    k = k + 1
    ReDim Preserve Ray(1 To k)
    Ray(k) = c.Value & " " & c.Resize(rws, 29).Address(0, 0)
  Next c
  ComboBox1.List = Ray
End Sub


Private Sub cmdBtnGetNames_Click()
Dim i as integer
Call GetNames


For i = 0 To ComboBox1.ListCount - 1
    If  ComboBox1.List(i) =Textbox1.Text   Then
       MsgBox ComboBox1.List(i) & " Exists"
       Textbox2.text = Split(ComboBox1.List(i) , " ")(1)
     End If
   Exit For
Next i
End Sub

Thanks NimishK

 

[NimishK]

How can i get the Range Address in Textbox2

May be Attached file will have more clarity

https://www.dropbox.com/s/hp5pp8ji336wfud/ComboSrch-TxtbxValue-XLMR.xlsm?dl=0

FYI in Post #1
Set LastA = Range("A" & Range("AC" & Rows.Count).End(xlUp).Row)
is changed to
Set LastA = RgsWs.Range("A" & Range("E" & Rows.Count).End(xlUp).Row)
and
Ray(k) = c.Value & " " & c.Resize(rws, 29).Address(0, 0)
to
Ray(k) = c.Value & " " & c.Resize(rws, 5).Address(0, 0)



Thanks
NimishK

 

[Yongle]

Using Split is made complicated because some of your names contain spaces and others do not
- what is consistent is that the LAST space in value in ComboBox1 is ALWAYS followed by the range that you want in Textbox2

One way to arrive at the values you want
- place in a standard module to test and then apply the method inside code in the userform

Sub SplitMyValue()
    Const v = vbCr & vbCr
    Dim cb As String, Nm As String, Addr As String
    
    cb = "NIM-K-102 556304 A10:E15"
    Addr = Split(cb, " ")(UBound(Split(cb, " ")))
    Nm = Replace(cb, " " & Addr, "")
    MsgBox cb & v & Nm & v & Addr
    
    cb = "596501 A2:E6"
    Addr = Split(cb, " ")(UBound(Split(cb, " ")))
    Nm = Replace(cb, " " & Addr, "")
    MsgBox cb & v & Nm & v & Addr
End Sub

 

[NimishK]

Hi Yongle
Indeed this was really great one. . Thanks a tonne
Using Split command was the only way i had thought of though complicated. But now charmingly Easy.
and Replace command is something new to me which i was never aware of.

so now having known the values of cb, Nm and Addr. How could i incorporate the same i below code
i.e in Textbox1 will be always typing the values of Nm
combobox1.List will be displayed as cb Values
and to get addr in textbox2

So as per below code when typing Nm value in textbox1.text and when pressing enter to match with Cb ie Combobox1.List.
msgbox 'Exists' and textbox2.Text to display the respective range address

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim i As Integer


If KeyCode = 13 Then


For i = 0 To ComboBox1.ListCount - 1
    If ComboBox1.List(i) = TextBox1.Text Then
       MsgBox ComboBox1.List(i) & " Exists"
       TextBox2.Text = Split(ComboBox1.List(i), " ")(1)
     End If
   'Exit For
Next i
End If


End Sub

NimishK

 

[Yongle]

Is this a valid test?

If ComboBox1.List(i) = TextBox1.Text Then

ComboBox.List(i) looks like this NIM-K-102 556304 A10:E15
TextBox1.Text looks like this TextBox1.Text
So the test will always return False

Is this the correct test?

    cb = ComboBox.List(i)
    Addr = Split(cb, " ")(UBound(Split(cb, " ")))
    Nm = Replace(cb, " " & Addr, "")
   [COLOR=#ff0000] If Nm = TextBox1.Text[/COLOR] Then

Or have I misunderstood what goes in TextBox1?

 

[NimishK]

Is this the correct test?

    cb = ComboBox.List(i)
    Addr = Split(cb, " ")(UBound(Split(cb, " ")))
    Nm = Replace(cb, " " & Addr, "")
    [B][COLOR=#ff0000]If Nm = TextBox1.Text Then[/COLOR][/B]

Or have I misunderstood what goes in TextBox1?

This was indeed the Correct Test Dear
Thanks a Tonne:wink: