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]

Hi,

It was not clear from you post which sheet you wanted the active cell on. The code as is will activate the cell on whatever sheet you are on when you search for the date. If you want to activate the cell on the "Training Log" you can delete the two lines highlighted in Red.

Sub test()


    Dim resp As String, nam As String
    Dim srch As Range
    
[COLOR=#ff0000]    nam = ActiveSheet.Name[/COLOR]
    Application.ScreenUpdating = False
    resp = InputBox("Enter Date")
    Worksheets("Training Log").Activate
    Set srch = Cells(Cells.Find(What:=resp, SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row, _
        Cells.Find(What:=resp, SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column)
[COLOR=#ff0000]    Worksheets(nam).Activate[/COLOR]
    Cells(srch.Row, 8).Select
    Application.ScreenUpdating = True
    
End Sub

 

[Ironman]

Hi igold - thank you so much for helping me out.

I deleted the 2 lines in red - my apologies for the confusion.

I ran the amended code and it returned "Object Variable or With Block Variable not set"

Hope that's a simple fix?

Thanks again

 

[igold]

You probably searched for a date that did not exist. This should prevent that from happening. You do not have to delete any lines from this code...

Sub test()


    Dim resp As String, nam As String
    Dim srch As Range
    
    nam = ActiveSheet.Name
    On Error GoTo CleanUp
    Application.ScreenUpdating = False
    resp = InputBox("Enter Date")
    Worksheets("Training Log").Activate
    Set srch = Cells(Cells.Find(What:=resp, SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row, _
        Cells.Find(What:=resp, SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column)
    Cells(srch.Row, 8).Select
    Application.ScreenUpdating = True
    Exit Sub
CleanUp:
    MsgBox "Date Not Found"
    Worksheets(nam).Activate
    Application.ScreenUpdating = True
    
End Sub

I hope this helps.

 

[Ironman]

Thanks again - the dates I tried do in fact exist.

Could it be because the dates in Training Log are formatted as Custom, not Date i.e. ddd, d mmm yyyy - I don't really want to change this format - If this is the issue, is there a workaround?

Thanks again!

 

[Ironman]

Hey I've just looked at your code and I think it's searching for a date in column 8, but all the dates are in Column A. I just wanted the active cell to be Column 8 (H) in the searched date row

 

[igold]

The code is not just searching Column 8. Can you show me and example of what your are entering into the InputBox to search for...

 

[Ironman]

I have input the valid date 08/11/2018 (and a couple of others, all UK format) in the below formats:

08/11/2018
8/11/2018
08/11/18
8/11/18
08.11.2018
8.11.2018
08.11.18
8.11.18

They all return Date Not Found

 

[igold]

Ok, so none of these were found?

Can you show me the date as it appears on your worksheet in your custom format...

 

[Ironman]

That's correct, none of those were found.

The date is displayed in the cell as Thu, 8 Nov 2018 and in the formula bar it's displayed as 08/11/2018