CODEWITHSUNDEEP
vbaexcel
user1803900
user1803900

Reputation: 11

VBA code to check each sheet, locate a cell containing =TODAY() function, and select that cell

I have a workbook with 12 worksheets each named JANUARY through FEBRUARY.

Only the current month's sheet (e.g., NOVEMBER) will ever contain a cell containing the function =TODAY() in mm/dd/yyyy date format.

When I open the workbook, I want to automatically activate the Sheet that contains this cell (in my instance Cell N2) and Select it. I am truly a newbie learning slowly, but knowledge is minimal and can't find what I need. This what I have so far, but it doesn't work:

Sub ChooseSheet()
Dim SearchString As Variant

SearchString = "TODAY()"       'string I am searching for

Do Until SearchString = "TODAY()"
If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Columns(14), SearchString) > 0 Then
Worksheets("Sheet1").Activate
            End If
        Exit Do                
Loop

End Sub

Upvotes: 1

Views: 4563

Answers (2)

Tim Williams
Tim Williams

Reputation: 166256

Sub Test()

Dim ws As Worksheet
Dim f As Range

    For Each ws In ActiveWorkbook.Worksheets
        Set f = ws.Cells.Find(What:="=TODAY()", LookIn:=xlFormulas, LookAt:=xlWhole)
        If Not f Is Nothing Then
            ws.Activate
            f.Select
            Exit For
        End If
    Next ws

End Sub

Upvotes: 1

mattboy
mattboy

Reputation: 2910

This works for me. 

Sub searchToday()

    Dim sh As Worksheet
    Dim found As Range

    For Each sh In ActiveWorkbook.Worksheets
        Set found = sh.Cells.Find(what:="=TODAY()", LookIn:=xlFormulas)
        If Not found Is Nothing Then
           sh.Activate
           found.Select
           Exit Sub
        End If
    Next sh

End Sub

Upvotes: 2

Related Questions