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

 

[DanteAmor]

Try this

Sub search_date()
    Dim wInput As Variant, wDate As Date, s As Worksheet, b As Range
    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")
        Set b = s.Cells.Find(wDate, LookIn:=xlValues, lookat:=xlWhole)
        If Not b Is Nothing Then
            s.Select
            b.Select
            MsgBox "I got it"
        Else
            MsgBox "Date does not exist"
        End If
    End If
End Sub

 

[Ironman]

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.

 

[igold]

From a good form point of view, all code that we are using here should really be in a code module. That said, the errors we are getting is not a function of where the code is located.

 

[Ironman]

Ah right, that's very informative. My major crime is I'm self taught so I stumble a lot but I learn the best stuff from guys like you.

 

[igold]

I just tried searching for the date 1/1/2019, and it found the cell with your custom format: Tue, 1 Jan 2019. That said I am not quite sure why it is not working for you..

 

[Ironman]

I just don't know what's wrong - I just tried your date/format and it returned "Date does not exist".

When I do the search I am doing it from the Analysis sheet. Is this what you did?

 

[igold]

From a good form point of view, all code that we are using here should really be in a code module. That said, the errors we are getting is not a function of where the code is located.

I take this back. I moved the code to a worksheet module and got a date not found. I ran it from the code module and it worked.

I am self taught as well, so now I am confused also. I did not think it would make a difference. You might want to try copying the code to a code module. You can create a code module by going to Insert-> Module, in the VB editor.

 

[Ironman]

Hmm! I just inserted a new module and pasted your code in. I ran the search (which I assigned to a button) with 1/1/2019 and it still returns Date does not Exist

 

[Michael M]

The dates in the training log are in the format
ddd, d mmm yyyy...
that may have something to do with it !

 

[igold]

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...