Code to search for a date in another sheet, then go to that cell

[Ironman]

Hi

I am looking for a custom search macro that does the following:

I enter a date into a custom search box then click search.

Then whichever sheet I am in, the query then only searches Sheet 1 ("Training Log") and makes column H of the row containing that date the active cell.

Hope you can help?

Many thanks

 

[igold]

@michael,

It worked for me with that date format and entering a "straight" date such as 1/1/2019, but only when the code was in a code module.

 

[Ironman]

Where ever your button is, on that sheet, if your button is CommandButton1 then try to call the code from that sheet. I put a button on a worksheet. In the worksheet module, I have this:

Private Sub CommandButton1_Click()
    Call test
End Sub

I have the "test" code in a code module.

I had copied the code to the above Button click event on the worksheet and it did not work. When I changed to the above it did work. I am pretty flumoxed at this point. As far I was concerned this should have been an easy do. I am sorry that this has become so drawn out...

Hmm, that's a bit too techie for me The only way I know is to open the 'Customise the Quick Access Toolbar', open the dropdown menu to show Macros, locate the macro then assign an icon to the new button on the QAT and then click that.

 

[DanteAmor]

Hi Dante, many thanks for your assistance. I tried your code with the same date used before (and a couple of others too) and they all return "Date does not exist" (but I know it does!)

Does it matter which sheet the code is entered into? I tried yours in This Workbook and igold I tried yours in This Workbook and Analysis sheet.

Just for text. Change format date in your cells to dd/mm/yyyy. Try again. In inputbox, same dd/mm/yyyy

 

[Ironman]

Many thanks Dante - that works, although I was hoping to avoid changing the format.

 

[DanteAmor]

Many thanks Dante - that works, although I was hoping to avoid changing the format.

It is the problem with the date and the format in the cell.


You can change the search, but it involves comparing cell by cell in the whole sheet. With Find Method, the search is direct.

Try this:

Sub search_date2()
    Dim wInput As Variant, wDate As Date, s As Worksheet, b As Range
    Dim wCell As Range, existe As Boolean, wAddress As String
    
    wInput = InputBox("Enter a date", "SEARCH DATE")
    If wInput = "" Then Exit Sub
    
    If Not IsDate(wInput) Then
        MsgBox "Enter a valid date:", vbExclamation
    Else
        wDate = CDate(wInput)
        Set s = Sheets("Training Log")
        For Each wCell In s.Cells.SpecialCells(xlCellTypeConstants, 23)
            If wCell = wDate Then
                existe = True
                wAddress = wCell.Address
                Exit For
            End If
        Next
    End If
    If existe Then
        s.Select
        Range(wAddress).Select
        MsgBox "I got it"
    Else
        MsgBox "Date does not exist"
    End If


End Sub

 

[Ironman]

Many thanks for your help Dante.

 

[DanteAmor]

You're welcome & thanks for the feeback.