Search Function

**ch715a** · 07-22-2011, 04:50 PM

Can someone please help with my problem. Firstly I'm new to the forum, secondly I have no VBA knowledge whatsoever. I'm reasonable with formulas and I know about macros, how to record and assign etc, just don't know how to code VBA.

I don't really know how to explain what I want to do, but I know it will involve making a macro of some description using VBA coding

I have attached an example that I hope will make it a bit clearer. I can't divulge exactly what it's for as it's work related, but any help would be appreciated.

As the example shows, the dates would not all be in the same year. There are currently about 25 sheets I want it to search, but I also want to be able add more sheets and include these in the search function.

If it is easier to put the date string on the individual sheet, then that would be fine. I'm very open to suggestion. My only requirement is that it's idiot proof ie all the end user has to do is input the date in the search field.

**tigeravatar** · 07-22-2011, 05:17 PM

ch715a,

Attached is a modified version of your sample workbook. The 'Search' bevel has been assigned to the following macro:

Sub SearchDateMacro_for_ch715a()
    
    Dim UserInput As String: UserInput = InputBox(Title:="Date Search", _
                                                  Prompt:="Type a date to search for")
    If Trim(UserInput) = vbNullString Then Exit Sub
    
    On Error GoTo InvalidDate
    Dim fDate As Date: fDate = UserInput
    
    Dim rngFound As Range: Set rngFound = Cells.Find(What:=Format(fDate, "d mmm yy"), LookIn:=xlValues)
    If Not rngFound Is Nothing Then
        Sheets(rngFound.Row - 8).Activate
    Else
        MsgBox Title:="Date Search Error", _
               Prompt:="Date """ & fDate & """ not found."
    End If
    Exit Sub
    
InvalidDate:
    MsgBox Title:="Date Search Error", _
           Prompt:="""" & UserInput & """ is not a valid date."
    
End Sub

Hope that helps,
~tigeravatar

**ch715a** · 07-23-2011, 03:34 PM

This is amazing, perfect! Exactly what I need.

As I said in my previous message, I know nothing about VBA. On the workbook I want to assign this macro to I have at least 20 date strings, each one linked to an individual sheet. In the example I sent there are only 3. How do get this macro to work with more date strings/sheets?

Again, what you have done is perfect so thank you so much for you time in helping me with this.

**ch715a** · 07-23-2011, 03:43 PM

Originally Posted by ch715a

This is amazing, perfect! Exactly what I need.

As I said in my previous message, I know nothing about VBA. On the workbook I want to assign this macro to I have at least 20 date strings, each one linked to an individual sheet. In the example I sent there are only 3. How do get this macro to work with more date strings/sheets?

Again, what you have done is perfect so thank you so much for you time in helping me with this.

Please ignore me, I have worked this out now, however I can't work out how to move the date strings further down the page. eg. If I want to start my date strings on line 20 instead of line 10, and line 20 is assigned to the first sheet rather than the 10th sheet.

Hope this makes sense, and thank you again.

**tigeravatar** · 07-25-2011, 12:38 PM

Originally Posted by ch715a

Please ignore me, I have worked this out now, however I can't work out how to move the date strings further down the page. eg. If I want to start my date strings on line 20 instead of line 10, and line 20 is assigned to the first sheet rather than the 10th sheet.

ch715a,

The following is the line you're looking for:

Sheets(rngFound.Row - 8).Activate

The 8 should be the starting row of the date strings - 2. In your example workbook, the starting row of the date strings was 10, so that line is rngFound.Row - 8. So if you want the starting row of the date strings to be 20, then the line should be: