Searching Value in ComboBox as per comboBox Columns

[SamDsouza]

Hello
I would like to search values of Combobox Column values
For eg typing J in comboBox column 1
it should dropdown the list with all J and respective range in column2
Now if i type A or D or : in column 2 dropdown list to display all the names and and range
if typed Specificaly the Range A36: D42 in col2 of combobox then DropDown to display Steve A36:D42

What to code inorder to run the below following properly
1. Search Value Column wise as explained above
2. Combobox to display the Searched Name and respective Range or Searching Range in col 2 to display its respective Name in Column 1
3. After Searching, Selecting the item, Clicking or Pressing Enter to get the values in respective textbox

The Following code when executed
1. Combobox takes the full List of Names and Ranges
2. When clicked on DropDown Arrow and selecting an item it displays the Name only the Range section disappears
3. When Pressed Enter with Names shown(only) in combo List it executes Combobox_Exit Event

Private Sub UserForm_Initialize()
   Dim Rng As Range
   
   For Each Rng In Sheet1.Range("B:B").SpecialCells(xlConstants).Areas
      With Me.ComboBox1
         .AddItem Rng(1).Value
         .List(.ListCount - 1, 1) = Rng.CurrentRegion.Address(0, 0)
      End With
   Next Rng
   Me.ComboBox1.ColumnCount = -1
   Me.ComboBox1.ColumnWidths = "50;50"
End Sub

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   Dim Fnd As Range
   Dim Rw As Long
   
   Set Fnd = Sheet1.Range("B:B").Find(me.ComboBox1.text, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      With Fnd.CurrentRegion
         If .Cells(.Rows.Count, 2) = "Balance :" Then Rw = .Rows.Count - 1 Else Rw = .Rows.Count
         Me.txtPymnt = .Cells(Rw, 3)
         Me.txtPymtRcd = .Cells(Rw, 4)
         Me.txtRngAdd = .Address(0, 0)
         Me.txtRngFrom = Split(.Address(0, 0), ":")(0)
         Me.txtRngTo = Split(.Address(0, 0), ":")(1)
         If .Cells(Rw + 1, 3) <> "" Then
            Me.txtBalance = .Cells(Rw + 1, 3)
            Me.lblBal.Caption = "Balance : To be Paid"
         ElseIf .Cells(Rw + 1, 4) <> "" Then
            Me.txtBalance = .Cells(Rw + 1, 4)
            Me.lblBal.Caption = "Balance : To be Recieved"
         Else
            Me.txtBalance = ""
            Me.lblBal.Caption = "Balance"
         End If
      End With
   End If
End Sub

[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Individual Name:[/TD]
[TD]Jerry[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Transaction Date[/TD]
[TD]Description[/TD]
[TD]Payments Made[/TD]
[TD]Payments Received[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02-04-2015[/TD]
[TD]vchr no : 2[/TD]
[TD]600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]sfdfdsf[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]03-04-2015[/TD]
[TD]vchr no : 3[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]rerrt[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]04-04-2015[/TD]
[TD]Agst vchr no : 2 [/TD]
[TD][/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]rwewrew[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]800[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Balance :[/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Individual Name:[/TD]
[TD]Mike[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Transaction Date[/TD]
[TD]Description[/TD]
[TD]Payments Made[/TD]
[TD]Payments Received[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]02-04-2015[/TD]
[TD]vchr no : 4[/TD]
[TD]1750[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]gfhfhgfhgf[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]03-04-2015[/TD]
[TD]Agst vchr no : 4 [/TD]
[TD][/TD]
[TD]350[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]oipoiopo[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]1750[/TD]
[TD]350[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Balance :[/TD]
[TD][/TD]
[TD][/TD]
[TD]1400[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Individual Name:[/TD]
[TD]Kate[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Transaction Date[/TD]
[TD]Description[/TD]
[TD]Payments Made[/TD]
[TD]Payments Received[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]01-04-2015[/TD]
[TD]vchr no : 5[/TD]
[TD]1500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[TD]dfgfgff[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]05-04-2015[/TD]
[TD]vchr no : 6[/TD]
[TD]600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][/TD]
[TD]fsfdsfds[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]06-04-2015[/TD]
[TD]Agst vchr no : 12[/TD]
[TD][/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD][/TD]
[TD]fdfds[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]09-04-2015[/TD]
[TD]Agst vchr no : 5[/TD]
[TD][/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD][/TD]
[TD]ggfgfgfg[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]10-04-2015[/TD]
[TD]Agst vchr no : 2 [/TD]
[TD][/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD][/TD]
[TD]fdfdsfds[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]2100[/TD]
[TD]3800[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]Balance :[/TD]
[TD][/TD]
[TD]1700[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]Individual Name:[/TD]
[TD]Steve[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]Transaction Date[/TD]
[TD]Description[/TD]
[TD]Payments Made[/TD]
[TD]Payments Received[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]02-04-2015[/TD]
[TD]vchr no : 4[/TD]
[TD]1235[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD][/TD]
[TD]dfdsfds[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]03-04-2015[/TD]
[TD]Agst vchr no : 4 [/TD]
[TD][/TD]
[TD]1235[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD][/TD]
[TD]fdfdsf[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]1235[/TD]
[TD]1235[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


SamD