Searching using macro & displaying results (more than 1 result)

**Smurlos** · 03-02-2010, 03:12 PM

I have made a booking confirmation system which stores each booking in a worksheet "Bookings". A macro enables you to view the history by searching for the booking reference number.

There's a seperate sheet "ConfirmationHistory" which has the following columns:

A) ReferenceID (this is the ref id from the Bookings sheet)
B) DateStamp (a date & time stamp that the confirmation was reprinted/emailed)
C) Info (displays whether confirmation was printed or e-mailed)
D) LoggedinUser (displays the user logged in who actioned the confirmation)

Now, when a booking is made, an entry will automatically be made in the "ConfirmationHistory" sheet that initial e-mail has been sent.

If the customer says they didnt get this, you may go back into the booking and print confirmation. In which case you'd then have a 2nd entry in this spreadsheet for the same reference number.

I was wondering if there was a way to select all rows from the table (a bit like an autofilter i guess) which have the selected reference number in the ref id field and then display these rows in one of these multi-select combo boxes for example?

i.e. Say I searched the ref num "111" in autofilter, it would only show the following 2 rows.

111 - 23/02/2010 12:00 - Initial Email Sent - Carl
111 - 25/02/2010 09:30 - Print Out Done - Carl

I then want these 2 rows to appear in a multi-line list box (not a drop down, the other kind) in my user form.

Hope I haven't confused anyone.
Thanks
Carl

**mojo249** · 03-02-2010, 06:00 PM

Hi Carl

You can copy the selected rows to a temp sheet using whatever criteria you want. Then link the temp sheet to a multi column listbox.

To copy the selected items use either a Do...Loop or For...Next:

This code probably won't work without some tweeking but it should give you the idea.

Dion

    'Clears list box
    Me.listView.RowSource = ""

    'define variables
    Dim varSourceRow As Integer
    Dim varTargetRow As Integer
    Dim varRowCount As Integer
    Dim varColumn As Integer
    Dim varReferenceNumber As String
    Dim WS As Object
    Dim WStemp As Object
    Dim FirstCell As Range
    Dim LastCell As Range
    Dim varListViewRange As Range
    
    Set WS = Sheets("ConfirmationHistory")
    Set WStemp = Sheets("tmpConfirmationHistory")
    
    'cleardown previous search
    WStemp.Range("A2:D5000").Value = ""

    'count the total number of records
    varRowCount = Sheets("ConfirmationHistory").Cells(50000, 1).End(xlUp).Row
    
    'define search string
    varReferenceNumber = "111"

    'reset loop counters
    varTargetRow = 2
        
    For varSourceRow = 2 To varRowCount
        If WS.Cells(varSourceRow, 1).Value = varReferenceNumber Then
            For varColumn = 1 To 4
                WStemp.Cells(varTargetRow, varColumn).Value = WS.Cells(varSourceRow, varColumn).Value
            Next varColumn
            
            varTargetRow = varTargetRow + 1
        End If
    Next varSourceRow

    'Cancel if there are no results found
    If WStemp.Cells(2, 1).Value = "" Then
        MsgBox "No results found.", vbInformation
        Exit Sub
    End If
    
    'Count the number of results
    varLastRow = Sheets("tmpConfirmationHistory").Cells(50000, 1).End(xlUp).Row
    
    'Define the range for the listbox
    With WStemp
        .Columns("A:D").EntireColumn.AutoFit
        Set FirstCell = .Cells(2, 1)
        Set LastCell = .Cells(varLastRow, 4)
        Set varListViewRange = .Range(FirstCell, LastCell)
    End With
    
    'Link the listbox to the range and adjust column widths
    With Me.listView
          .RowSource = varListViewRange.Address(external:=True)
          ColWidth = ""
          For c = 1 To 4
             ColWidth = ColWidth & varListViewRange.Columns(c).Width & ";"
          Next c
          .ColumnWidths = ColWidth
          .ListIndex = 0
    End With

**Smurlos** · 03-02-2010, 08:06 PM

Originally Posted by mojo249

Hi Carl

You can copy the selected rows to a temp sheet using whatever criteria you want. Then link the temp sheet to a multi column listbox.

To copy the selected items use either a Do...Loop or For...Next:

This code probably won't work without some tweeking but it should give you the idea.

Dion

    'Clears list box
    Me.listView.RowSource = ""

    'define variables
    Dim varSourceRow As Integer
    Dim varTargetRow As Integer
    Dim varRowCount As Integer
    Dim varColumn As Integer
    Dim varReferenceNumber As String
    Dim WS As Object
    Dim WStemp As Object
    Dim FirstCell As Range
    Dim LastCell As Range
    Dim varListViewRange As Range
    
    Set WS = Sheets("ConfirmationHistory")
    Set WStemp = Sheets("tmpConfirmationHistory")
    
    'cleardown previous search
    WStemp.Range("A2:D5000").Value = ""

    'count the total number of records
    varRowCount = Sheets("ConfirmationHistory").Cells(50000, 1).End(xlUp).Row
    
    'define search string
    varReferenceNumber = "111"

    'reset loop counters
    varTargetRow = 2
        
    For varSourceRow = 2 To varRowCount
        If WS.Cells(varSourceRow, 1).Value = varReferenceNumber Then
            For varColumn = 1 To 4
                WStemp.Cells(varTargetRow, varColumn).Value = WS.Cells(varSourceRow, varColumn).Value
            Next varColumn
            
            varTargetRow = varTargetRow + 1
        End If
    Next varSourceRow

    'Cancel if there are no results found
    If WStemp.Cells(2, 1).Value = "" Then
        MsgBox "No results found.", vbInformation
        Exit Sub
    End If
    
    'Count the number of results
    varLastRow = Sheets("tmpConfirmationHistory").Cells(50000, 1).End(xlUp).Row
    
    'Define the range for the listbox
    With WStemp
        .Columns("A:D").EntireColumn.AutoFit
        Set FirstCell = .Cells(2, 1)
        Set LastCell = .Cells(varLastRow, 4)
        Set varListViewRange = .Range(FirstCell, LastCell)
    End With
    
    'Link the listbox to the range and adjust column widths
    With Me.listView
          .RowSource = varListViewRange.Address(external:=True)
          ColWidth = ""
          For c = 1 To 4
             ColWidth = ColWidth & varListViewRange.Columns(c).Width & ";"
          Next c
          .ColumnWidths = ColWidth
          .ListIndex = 0
    End With

Hi Dion

Thanks. I'll give it a whirl.
Just a quick note. How would I carry out the first part in regards to searching for a particular criterion and then copying those rows to the temp worksheet?

Thanks :-)

**mojo249** · 03-06-2010, 12:25 AM

The following part of the code copies columns A to D of each row where the value in column A is equal to varReferenceNumber (in this case "111").

If you have a userform, you can set the value of varReferenceNumber from a textbox or combobox. If you don't, then you can set varReferenceNumber from a cell value:

varReferenceNumber = Sheets("Sheet1").Cells(1, 1).Value

For varSourceRow = 2 To varRowCount
        If WS.Cells(varSourceRow, 1).Value = varReferenceNumber Then
            For varColumn = 1 To 4
                WStemp.Cells(varTargetRow, varColumn).Value = WS.Cells(varSourceRow, varColumn).Value
            Next varColumn
            
            varTargetRow = varTargetRow + 1
        End If
    Next varSourceRow

**Smurlos** · 03-06-2010, 10:19 AM

Thank you sooo much, works perfectly.

I just have one small issue as I have sort of adapted it...

On the initial userform, "Welcome" I have a textbox called "RefNum".
The user types in the reference number and clicks "Search".

I have a worksheet called "SearchData".

When they click "Search", I have a macro which searches my Bookings sheet for that reference number. If it finds a result, it copies all the information into the "SearchData" sheet. If no result was found it says "No match to that reference number"

So it copies the information from column B (customer name) to the "CustomerName" field in "SearchData", column C (telephone number) to the "TelephoneNumber" field in "SearchData" etc. etc.

Then it pops up a new userform called "ViewBooking"

Each textbox in there pulls the information from the "SearchData" worksheet. I've then taken the reference number that was searched for, from the SearchData worksheet and made that the value of "varReferenceNumber" and it works perfectly.

However, when I close the "ViewBooking" window, i make it clear all textboxes to blank and it clears the SearchData worksheet. This is fine.

Then it shows the "Welcome" userform again.
However if I enter a new reference number AFTER having done a search already and click "Search" it doesn't search for the new reference number and pops up the ViewBooking userform with all fields blank.

Is there a way to completely reset the userform to the initial state when it first opens so that this will work?

Many thanks for your help :-)

**Smurlos** · 03-06-2010, 11:52 AM

"Welcome" userform

Private Sub LookUpRef_Click()

    If Me.EZBook_Ref.Value = "" Then
    
    MsgBox "You must enter an EZBook Reference number to locate a booking"
    
    Else
    
    Range("SEARCH_REF:SEARCH_REF") = ""

    Dim LR As Long
    Dim FindString As String
    
    LR = Sheets("BookingsList").Range("A" & Rows.Count).End(xlUp).Row
    FindString = EZBook_Ref.Value
    If Trim(FindString) <> "" Then
        With Sheets("BookingsList").Range("A2:A" & LR)
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
            Sheets("Data").Select
                Application.Goto Rng.Offset(0, 0), True
                Range("SEARCH_REF:SEARCH_REF") = Rng.Offset(0, 0).Value
                Range("SEARCH_Name:SEARCH_Name") = Rng.Offset(0, 1).Value
                Range("SEARCH_Type:SEARCH_Type") = Rng.Offset(0, 7).Value
                Range("SEARCH_PartyDate:SEARCH_PartyDate") = Rng.Offset(0, 4).Text
                Range("SEARCH_BookingTime:SEARCH_BookingTime") = Rng.Offset(0, 5).Text
                Range("SEARCH_DateBooked:SEARCH_DateBooked") = Rng.Offset(0, 6).Text
                Range("SEARCH_BookedBy:SEARCH_BookedBy") = Rng.Offset(0, 8).Text
                Range("SEARCH_EmailAddress:SEARCH_EmailAddress") = Rng.Offset(0, 3).Text
                Range("SEARCH_Telephone:SEARCH_Telephone") = Rng.Offset(0, 2).Text
                Range("SEARCH_ChildAge:SEARCH_ChildAge") = Rng.Offset(0, 9).Text
                Range("SEARCH_ChildName:SEARCH_ChildName") = Rng.Offset(0, 10).Text
                Range("SEARCH_Attendees:SEARCH_Attendees") = Rng.Offset(0, 11).Text
                Range("SEARCH_NumLanes:SEARCH_NumLanes") = Rng.Offset(0, 13).Text
                Range("SEARCH_NumGames:SEARCH_NumGames") = Rng.Offset(0, 14).Text
                Range("SEARCH_SubType:SEARCH_SubType") = Rng.Offset(0, 15).Text
                Range("SEARCH_Notes:SEARCH_Notes") = Rng.Offset(0, 16).Text
                
                Welcome.Hide
                ViewBooking.Show
            Else
                MsgBox "No Results for that booking reference number '" & Me.EZBook_Ref.Value & "'"
            End If
        End With
    End If
    
    End If

End Sub

This searches "BookingsList" for the value of "EZBook_Ref". If there are no results it says "No Results for that booking reference number 'xxxx'", otherwise, it selects the booking in the "BookingsList" sheet. It then selects the information from each column and transfers the information to the relevent cell names (which refer to cells in the sheet "Data").

For the very first search, this works. It then shows the User form "ViewBooking".

Private Sub UserForm_Initialize()

ViewBooking.Caption = "View Booking - Reference Number " & Range("SEARCH_REF:SEARCH_REF") & " - " & Range("SEARCH_Name:SEARCH_Name")

Me.EZBook_RefNum.Text = Range("SEARCH_REF:SEARCH_REF").Text
Me.PartyType.Text = Range("SEARCH_Type:SEARCH_Type").Text
Me.PartyDate.Text = Range("SEARCH_PartyDate:SEARCH_PartyDate").Text
Me.PartyTime.Text = Range("SEARCH_BookingTime:SEARCH_BookingTime").Text
Me.NumAttendees.Text = Range("SEARCH_Attendees:SEARCH_Attendees").Text
Me.NumLanes.Text = Range("SEARCH_NumLanes:SEARCH_NumLanes").Text
Me.NumGames.Text = Range("SEARCH_NumGames:SEARCH_NumGames").Text
Me.CustomerName.Text = Range("SEARCH_Name:SEARCH_Name").Text
Me.PhoneNum.Text = Range("SEARCH_Telephone:SEARCH_Telephone").Text
Me.EMailAddr.Text = Range("SEARCH_EmailAddress:SEARCH_EmailAddress").Text
Me.BookedBy.Text = Range("SEARCH_BookedBy:SEARCH_BookedBy").Text
Me.DateBooked.Text = Range("SEARCH_DateBooked:SEARCH_DateBooked").Text
Me.BookingNotes.Text = "System Notes"
Me.User_Notes.Text = Range("SEARCH_Notes:SEARCH_Notes").Text


    'define variables
    Dim varSourceRow As Integer
    Dim varTargetRow As Integer
    Dim varRowCount As Integer
    Dim varColumn As Integer
    Dim varReferenceNumber As String
    Dim WS As Object
    Dim WStemp As Object
    Dim FirstCell As Range
    Dim LastCell As Range
    Dim varListViewRange As Range
    
    Set WS = Sheets("Confirmation")
    Set WStemp = Sheets("ConfSearch")
    
    'cleardown previous search
    WStemp.Range("A2:D5000").Value = ""

    'count the total number of records
    varRowCount = Sheets("Confirmation").Cells(50000, 1).End(xlUp).Row
    
    'define search string
    varReferenceNumber = Range("SEARCH_REF:SEARCH_REF")

    'reset loop counters
    varTargetRow = 2
        
    For varSourceRow = 2 To varRowCount
        If WS.Cells(varSourceRow, 1).Value = varReferenceNumber Then
            For varColumn = 1 To 4
                WStemp.Cells(varTargetRow, varColumn).Value = WS.Cells(varSourceRow, varColumn).Value
            Next varColumn
            
            varTargetRow = varTargetRow + 1
        End If
    Next varSourceRow

    'Cancel if there are no results found
    If WStemp.Cells(2, 1).Value = "" Then
        Exit Sub
    End If
    
    'Count the number of results
    varLastRow = Sheets("ConfSearch").Cells(50000, 1).End(xlUp).Row
    
    'Define the range for the listbox
    With WStemp
        .Columns("A:D").EntireColumn.AutoFit
        Set FirstCell = .Cells(2, 1)
        Set LastCell = .Cells(varLastRow, 4)
        Set varListViewRange = .Range(FirstCell, LastCell)
    End With
    
    'Link the listbox to the range and adjust column widths
    With Me.ConfirmationHistory
          .RowSource = varListViewRange.Address(external:=True)
          ColWidth = ""
          For c = 1 To 4
             ColWidth = ColWidth & varListViewRange.Columns(c).Width & ";"
          Next c
          .ColumnWidths = ColWidth
          .ListIndex = 0
    End With

End Sub

ViewBooking works perfectly. If i click on the "Close Booking" button, it runs the following macro.

Private Sub CloseBooking_Click()

'cleardown previous search
Sheets("ConfSearch").Range("A2:D5000").Value = ""

Sheets("BookingsList").Select
Range("A1:A1").Select

Sheets("Data").Select
Range("SEARCH_REF:SEARCH_REF") = ""
Range("SEARCH_Type:SEARCH_Type") = ""
Range("SEARCH_PartyDate:SEARCH_PartyDate") = ""
Range("SEARCH_BookingTime:SEARCH_BookingTime") = ""
Range("SEARCH_Attendees:SEARCH_Attendees") = ""
Range("SEARCH_NumLanes:SEARCH_NumLanes") = ""
Range("SEARCH_NumGames:SEARCH_NumGames") = ""
Range("SEARCH_Name:SEARCH_Name") = ""
Range("SEARCH_Telephone:SEARCH_Telephone") = ""
Range("SEARCH_EmailAddress:SEARCH_EmailAddress") = ""
Range("SEARCH_BookedBy:SEARCH_BookedBy") = ""
Range("SEARCH_DateBooked:SEARCH_DateBooked") = ""
Range("SEARCH_Notes:SEARCH_Notes") = ""
Range("SEARCH_ChildAge:SEARCH_ChildAge") = ""
Range("SEARCH_ChildName:SEARCH_ChildName") = ""
Range("SEARCH_SubType:SEARCH_SubType") = ""

Me.EZBook_RefNum.Text = ""
Me.PartyType.Text = ""
Me.PartyDate.Text = ""
Me.PartyTime.Text = ""
Me.NumAttendees.Text = ""
Me.NumLanes.Text = ""
Me.NumGames.Text = ""
Me.CustomerName.Text = ""
Me.PhoneNum.Text = ""
Me.EMailAddr.Text = ""
Me.BookedBy.Text = ""
Me.DateBooked.Text = ""
Me.BookingNotes.Text = ""
Me.User_Notes.Text = ""

Sheets("BookingsList").Select

ViewBooking.Hide
Welcome.Show

End Sub

So you end up back on the Welcome userform, back to square one.
If you then enter a new reference number that isn't in the system it will show the error message that no booking was found.
If you then enter a different reference number that is in the booking system, it does not transfer the information from each column into the cells in "Data" like it does on the first search and I am not sure why.

Can anyone help pleeeease? Thanks :-)