VB Search a text string

[rwrichard1]

Hi.

I have some script in a form that is looking up a date value from a combo box, and then populating a list box with data from sheet 6 columns where the date is equal to combo box value.

Private Sub ComboBox10_Change()
UserForm5.ListBox1.Clear ' First Clear the Box
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'When Date is Selected in ComboBox, I populate the ListBox to display all other downtime booked against this date
'====================================================================================================================
I = 1
x = 0
Do Until Sheet6.Cells(I, 1) = ""
If Sheet6.Cells(I, 1) = DateValue(UserForm5.ComboBox10) Then
        x = x + 1
        With UserForm5.ListBox1
            UserForm5.ListBox1.ColumnCount = 8
            'UserForm5.ListBox1.ColumnHeads = True ' This is switched off as only displays blanks
            UserForm5.ListBox1.ColumnWidths = "20;40;90;80;40;40;90;130"
            .AddItem
            .List(x - 1, 0) = Sheet6.Cells(I, 12).Row 'Row
            .List(x - 1, 1) = Sheet6.Cells(I, 6) 'Downtime Duration
            .List(x - 1, 2) = Sheet6.Cells(I, 12) 'Downtime Category
            .List(x - 1, 3) = Sheet6.Cells(I, 13) 'Downtime Category
            .List(x - 1, 4) = WorksheetFunction.Text(Sheet6.Cells(I, 4), "hh:mm")
            .List(x - 1, 5) = WorksheetFunction.Text(Sheet6.Cells(I, 5), "hh:mm") 'Downtime From
            .List(x - 1, 6) = Sheet6.Cells(I, 8) 'Downtime Category
            .List(x - 1, 7) = Sheet6.Cells(I, 11) 'Comments
           
        End With
    End If
     
I = I + 1
Loop
End Sub

This all works perfectly. What I am trying to do however, is the same thing but with Text.

So on sheet 7, I have the data duplicated but pipe separated into column A like this...

day|shift ID|Line No|Downtime From|Downtime To|duration Downtime|Downtime PKey|family Equipment|detailed Equiment|Equipment Number|additionalInformation(freetext)|Downtime Family|Downtime Type
09/02/2018||1|18/01/2018 00:02|18/01/2018 00:04|0.03|phol|E114 - Full System|N/A|||Planned - External|Public Holiday
10/02/2018||1|17/01/2018 00:01|17/01/2018 00:12|0.18|phol|E114 - Full System|N/A|||Planned - External|Public Holiday
11/02/2018|Shift B|1|18/01/2018 00:23|18/01/2018 00:34|0.18|phol|E114 - Full System|N/A|||Planned - External|Public Holiday
12/02/2018|Shift B|1|20/01/2018 00:01|20/01/2018 00:22|0.35|phol|E114 - Full System|N/A|||Planned - External|Public Holiday
12/02/2018|Shift B|1|20/01/2018 00:03|20/01/2018 00:34|0.52|pbrks|E114 - Full System|N/A|||Planned - External|Paid Breaks
12/02/2018|Shift B|1|20/01/2018 00:04|20/01/2018 00:45|0.68|pbrks|E114 - Full System|N/A|||Planned - External|Paid Breaks
12/02/2018||1|20/01/2018 00:10|20/01/2018 00:11|0.02|phol|E114 - Full System|N/A|||Planned - External|Public Holiday
12/02/2018||1|20/01/2018 00:21|20/01/2018 00:36|0.25|phol|E114 - Full System|N/A|||Planned - External|Public Holiday
12/02/2018||1|20/01/2018 00:01|20/01/2018 00:23|0.37|phol|E114 - Full System|N/A|||Planned - External|Public Holiday
12/02/2018||1|20/01/2018 00:04|20/01/2018 00:06|0.03|phol|E114 - Full System|N/A|||Planned - External|Public Holiday
12/02/2018||1|20/01/2018 03:00|20/01/2018 04:00|1|phol|E114 - Full System|N/A|||Planned - External|Public Holiday
10/02/2018||1|18/01/2018 10:00|18/01/2018 10:45|0.75|phol|E114 - Full System|N/A|||Planned - External|Public Holiday
10/02/2018|Shift B|MU4336|19/01/2018 00:01|19/01/2018 00:02|0.02|pbrks|E114 - Full System|N/A||hi|Planned - External|Paid Breaks
09/02/2018||MU4336|19/01/2018 00:02|19/01/2018 00:08|0.1|anmain|E101 - Feeder & Bag Opener|N/A|12||Planned - Maintenance|Annual Maintenance
10/02/2018||MU4336|16/01/2018 00:02|16/01/2018 00:12|0.17|nofeed|E114 - Full System|N/A|||Unplanned - External|No Feedstock
11/02/2018||MU4336|16/01/2018 01:00|16/01/2018 03:00|2|phol|E114 - Full System|N/A|||Planned - External|Public Holiday
11/02/2018||MU4336|17/01/2018 01:00|17/01/2018 03:00|2|nofeed|E114 - Full System|N/A|||Unplanned - External|No Feedstock
11/02/2018||MU4336|20/01/2018 03:00|19/01/2018 07:00|-20|nofeed|E114 - Full System|N/A||testing|Unplanned - External|No Feedstock
12/02/2018||MU4336|20/01/2018 01:00|20/01/2018 04:00|3|train|E114 - Full System|N/A|||Planned - Management|Training
12/02/2018|Shift B|MU4336|20/01/2018 02:00|20/01/2018 04:00|2||E114 - Full System|N/A|||Unplanned - Process|Process Preparation
12/02/2018|Shift B|MU4336|20/01/2018 02:00|20/01/2018 04:00|2|nopurch|E114 - Full System|N/A|||Unplanned - External|No Purchaser
12/02/2018||MU4336|20/01/2018 00:03|20/01/2018 00:13|0.17|phol|E114 - Full System|N/A|||Planned - External|Public Holiday
12/02/2018||MU4336|20/01/2018 00:01|20/01/2018 00:02|0.02|phol|E114 - Full System|N/A|||Planned - External|Public Holiday
12/02/2018|Shift B|MU4336|20/01/2018 00:03|20/01/2018 00:12|0.15|phol|E114 - Full System|N/A|||Planned - External|Public Holiday

Is there a way to search the Split Sting of each row in the same way? I can split the string for 1 cell without a problem, but I cant use the loop when I do it the way I found, and was wondering if anyone might be able to help?

Also, is there any forum or website where I might be able to pay for further development work? I have a few things I am trying to resolve, but this isn't really my area of expertise, and think it may be easier all around if I got in some help..?

Thanks for taking time to read my thread.

Regards

Richard

 

[Fluff]

Also, is there any forum or website where I might be able to pay for further development work? I have a few things I am trying to resolve, but this isn't really my area of expertise, and think it may be easier all around if I got in some help..?

Click on the "Excel Consulting Services" button at the top of the page.

Regards your main query, the easiest way would be to split the data using text to columns.

 

[rwrichard1]

Thanks, I have contacted someone from the consulting services page.