Search for string and displaying values into combo box 1

X

xboon_95

Board Regular
Joined
Jun 18, 2014
Messages
53
Hi all, the information below are my data :

Past records(up to 52 cartridges)
Month Date Hour Min Cyc OutP OutT InP InT ST
51508421660533454920 #
51501000000
515026000000
515022000000
515443179234831426400
5150152888844177008400
515010525265840869910
51502000000
712119000000
Cartridge type: CARTRIDGELAB

<colgroup><col span="11"></colgroup><tbody>
</tbody>

What I'm trying to do is to search for the string "Past records(up to 52 cartridges)" and to display all these information in a combo box. Now, I'm only able to display the first column which is "Month". May I know how do i display the rest of the information? Is it something to do with offset and list index? The data above could be at random places, so i couldn't specify the range and columns that I want.

My code is shown below :

Code: 
Private Sub CommandButton1_Click()


 Dim SearchRange As Range
    Dim FindRow As Range
    Set SearchRange = Range("A1", Range("A65536").End(xlUp))
    Set FindRow = SearchRange.Find("  Past records(up to 52 cartridges)", LookIn:=xlValues, lookat:=xlWhole)


row_review = FindRow.Row


    Dim TheSheet As Worksheet
    Set TheSheet = Sheets("Sheet1")
    
    Do
    DoEvents
    row_review = row_review + 1
    item_in_review = TheSheet.Range("A" & row_review)
    
    
    If Len(item_in_review) > 0 Then ComboBox1.AddItem (item_in_review)
    
    
    Loop Until item_in_review = ""
    
    MsgBox "Complete"


End Sub

Any help is appreciated. Thank you.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Hi xboon,

Below is a solution I provided for another person (it populated a listbox but the same principles apply). You should be able to adapt it to your problem.

Note: If you are populating a listbox/combobox like this you need to use a fixed width font (eg Courier New) otherwise it will appear all over the place


Code: 
Function PopulateListBox(sWSheet As String, Staff As String)
    
    Dim aCols, aColsWidth, aColsAlign As Variant  ' Arrays to hold the columns to show, their width and alignmemt
    
    ' The following arrays MUST have the same number of elements in each
    
    ' Cut down columns selected
    aCols = Array(1, 2, 10, 11) 'Array of columns you want to include
    aColsWidth = Array(10, 20, 10, 5) 'Array of column widths you want to include
    aColsAlign = Array("R", "L", "L", "R") 'Array of column alignments you want to include
    
    'Clear any existing data in the listbox
    If UserForm1.ListBox1.ListCount > 0 Then
        UserForm1.ListBox1.Clear
    End If
    
    'select the worksheet where the data is
    Sheets(sWSheet).Select
    
    'Get the header row
    n = 1
    strLeave = ""
    For x = 0 To UBound(aCols)
        strLeave = strLeave & PadIt(Cells(n, aCols(x)), Val(aColsWidth(x)), aColsAlign(x)) & " | "
    Next x
    UserForm1.ListBox1.AddItem strLeave & " is at Row 1" ' "is at row is needed to navigate to the selected row
    
       
    'Process the Records
    n = 2
    Do While Len(Cells(n, 2)) > 0 ' Continue until data runs out
        If Cells(n, 2) = Staff Then
            strLeave = ""
            For x = 0 To UBound(aCols)
                strLeave = strLeave & PadIt(Cells(n, aCols(x)), Val(aColsWidth(x)), aColsAlign(x)) & " | "
            Next x
            UserForm1.ListBox1.AddItem strLeave & " is at Row " & n  ' "is at row" is needed to navigate to the selected row
        End If
        n = n + 1
    Loop
    
    'Select the header row
    UserForm1.ListBox1.ListIndex = 0
    
    PopulateListBox = True

End Function

Function PadIt(strPad As String, padlength As Variant, PadDirection As Variant)

If Len(strPad) < padlength Then
    If PadDirection = "L" Then
        For n = 1 To padlength - Len(strPad)
            strPad = strPad & " "  'Now moved to spaces based on the listbox font you need to use (eg Courier New)
        Next n
    Else
        For n = 1 To padlength - Len(strPad)
            strPad = " " & strPad  'Now moved to spaces based on the listbox font you need to use (eg Courier New)
        Next n
    End If
    
Else
    strPad = Left(strPad, padlength)
End If

PadIt = strPad

End Function
 
Upvote 0
Sorry but I'm an amateur in excel programming. So how do i integrate those codes into my program? Do I place those in command button or straight into the list box itself?
 
Upvote 0
ozbod said:
Hi xboon,

Below is a solution I provided for another person (it populated a listbox but the same principles apply). You should be able to adapt it to your problem.

Note: If you are populating a listbox/combobox like this you need to use a fixed width font (eg Courier New) otherwise it will appear all over the place


Code: 
Function PopulateListBox(sWSheet As String, Staff As String)
    
    Dim aCols, aColsWidth, aColsAlign As Variant  ' Arrays to hold the columns to show, their width and alignmemt
    
    ' The following arrays MUST have the same number of elements in each
    
    ' Cut down columns selected
    aCols = Array(1, 2, 10, 11) 'Array of columns you want to include
    aColsWidth = Array(10, 20, 10, 5) 'Array of column widths you want to include
    aColsAlign = Array("R", "L", "L", "R") 'Array of column alignments you want to include
    
    'Clear any existing data in the listbox
    If UserForm1.ListBox1.ListCount > 0 Then
        UserForm1.ListBox1.Clear
    End If
    
    'select the worksheet where the data is
    Sheets(sWSheet).Select
    
    'Get the header row
    n = 1
    strLeave = ""
    For x = 0 To UBound(aCols)
        strLeave = strLeave & PadIt(Cells(n, aCols(x)), Val(aColsWidth(x)), aColsAlign(x)) & " | "
    Next x
    UserForm1.ListBox1.AddItem strLeave & " is at Row 1" ' "is at row is needed to navigate to the selected row
    
       
    'Process the Records
    n = 2
    Do While Len(Cells(n, 2)) > 0 ' Continue until data runs out
        If Cells(n, 2) = Staff Then
            strLeave = ""
            For x = 0 To UBound(aCols)
                strLeave = strLeave & PadIt(Cells(n, aCols(x)), Val(aColsWidth(x)), aColsAlign(x)) & " | "
            Next x
            UserForm1.ListBox1.AddItem strLeave & " is at Row " & n  ' "is at row" is needed to navigate to the selected row
        End If
        n = n + 1
    Loop
    
    'Select the header row
    UserForm1.ListBox1.ListIndex = 0
    
    PopulateListBox = True

End Function

Function PadIt(strPad As String, padlength As Variant, PadDirection As Variant)

If Len(strPad) < padlength Then
    If PadDirection = "L" Then
        For n = 1 To padlength - Len(strPad)
            strPad = strPad & " "  'Now moved to spaces based on the listbox font you need to use (eg Courier New)
        Next n
    Else
        For n = 1 To padlength - Len(strPad)
            strPad = " " & strPad  'Now moved to spaces based on the listbox font you need to use (eg Courier New)
        Next n
    End If
    
Else
    strPad = Left(strPad, padlength)
End If

PadIt = strPad

End Function
Click to expand...


Hi, now I'm able to display out the values but it only displays in a single column of the combo box. How do I display in terms of columns instead? Below is my code:

Code: 
Private Sub CommandButton1_Click()
Dim SearchRange As Range
    Dim FindRow As Range
    Set SearchRange = Range("A1", Range("A65536").End(xlUp))
    Set FindRow = SearchRange.Find("  Past records(up to 52 cartridges)", LookIn:=xlValues, lookat:=xlWhole)


row_review = FindRow.Row


    Dim TheSheet As Worksheet
    Set TheSheet = Sheets("Sheet1")
    
    Do
    DoEvents
    row_review = row_review + 1
    
    
    item_in_review = Worksheets("Sheet1").Range("A" & row_review)
    item_in_review2 = Worksheets("Sheet1").Range("B" & row_review)
    item_in_review3 = Worksheets("Sheet1").Range("C" & row_review)
    item_in_review4 = Worksheets("Sheet1").Range("D" & row_review)
    item_in_review5 = Worksheets("Sheet1").Range("E" & row_review)
  


    
    If Len(item_in_review) > 0 Then ComboBox1.AddItem (item_in_review)
    If Len(item_in_review2) > 0 Then ComboBox1.AddItem (item_in_review2)
    If Len(item_in_review3) > 0 Then ComboBox1.AddItem (item_in_review3)
    If Len(item_in_review4) > 0 Then ComboBox1.AddItem (item_in_review4)
    If Len(item_in_review5) > 0 Then ComboBox1.AddItem (item_in_review5)
    
     Loop Until item_in_review = ""
 
    
    MsgBox "Complete"
End Sub
 
Upvote 0
xboon,

To make it into a column type view in the combobox you need to join the columns together (concatenate). Based on your code, replace

Code: 
    If Len(item_in_review) > 0 Then ComboBox1.AddItem (item_in_review)
    If Len(item_in_review2) > 0 Then ComboBox1.AddItem (item_in_review2)
    If Len(item_in_review3) > 0 Then ComboBox1.AddItem (item_in_review3)
    If Len(item_in_review4) > 0 Then ComboBox1.AddItem (item_in_review4)
    If Len(item_in_review5) > 0 Then ComboBox1.AddItem (item_in_review5)

with

Code: 
If Len(item_in_review) > 0 Then
    sItem_In_Review = item_in_review & "|" & item_in_review2 & "|" & item_in_review3 & "|" & item_in_review4 & "|" & item_in_review5
    ComboBox1.AddItem (sItem_In_Review)
End If

However, this will appear skewed when the combobox is displayed.

It you add the PadIt Function in your VBA module you can align things better. Based on your dataset in your original post, the maximum length would be 2 and being numeric you would want it rights aligned.

So replace above with

Code: 
If Len(item_in_review) > 0 Then
            ' the & _ allows you to carry over your filling of the string
            
    sItem_In_Review = PadIt(item_in_review, 2, "R") & "|" & _
        PadIt(item_in_review2, 2, "R") & "|" & _
        PadIt(item_in_review3, 2, "R") & "|" & _
        PadIt(item_in_review4, 2, "R") & "|" & _
        PadIt(item_in_review5, 2, "R")
    ComboBox1.AddItem (sItem_In_Review)
End If

and add this into a module in your workbook

Code: 
Function PadIt(strPad As String, padlength As Variant, PadDirection As Variant)

If Len(strPad) < padlength Then
    If PadDirection = "L" Then
        For n = 1 To padlength - Len(strPad)
            strPad = strPad & " "  'Now moved to spaces based on the listbox font you need to use (eg Courier New)
        Next n
    Else
        For n = 1 To padlength - Len(strPad)
            strPad = " " & strPad  'Now moved to spaces based on the listbox font you need to use (eg Courier New)
        Next n
    End If
    
Else
    strPad = Left(strPad, padlength)
End If

PadIt = strPad

End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Display Entire Filtered Row Except Need Value from One Column on Separate Row
Replies
1
Views
215
jarett
J
B
  • Question
Populating a worksheet based on a Combo Box value within a User Form
2 3 4
Replies
35
Views
1K
igold
igold
P
  • Question
Dynamic approach to calculating charges against nominal codes (single value, multi value, range & combo)
Replies
0
Views
229
PureBluff
P
P
  • Question
Populate Combo Box on Sheet with range of values
Replies
0
Views
294
psycoperl
P
N
  • Solved
Combo Box Typing and Auto Completion in Number
Replies
2
Views
316
nandhavnk
N

Forum statistics

Threads
1,214,920
Messages
6,122,262
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top