• 5 Subscribers
  • 114 Views
0

I would like my workbook to open on the cell of today's date. The dates of 2017 are listed in row 10. My macro is listed below but contains a syntax error on the line 'x = Format(Date, "Short Date")'

Private Sub Workbook_OpenDate()

    Dim wb As Workbook, wb1 As Workbook
    Dim LastRow As Long

    Set wb = Workbooks("2017 Capacity Planner.xlsm")

    Worksheets("Dashboard").Select
    x = Format(Date, "Short Date")

    On Error Resume Next
    Worksheets(“Dashboard”).Row(10).Find(What:=x, LookIn:=xlValues).Activate
    Application.Goto Selection, True

End Sub

1 answers in total

3
cyboashu Posted at 2017-01-11 17:57:47Z

You are still using magic quotes. Even if you remove that you will Error 438 on the line .Row(10). Then on the find another error is waiting when nothing is found.Change your code like this :

Sub test()

    Dim rng As Range
    Dim x As String

    Dim wb As Workbook, wb1 As Workbook
    Dim LastRow As Long

    Set wb = Workbooks("2017 Capacity Planner.xlsm")

    Worksheets("Dashboard").Select
    x = (Format(Date, "Short Date"))

    Set rng = Worksheets("Dashboard").Rows(10).Find(What:=x, LookIn:=xlValues)

    If Not rng Is Nothing Then
        rng.Parent.Activate
        rng.Select
    End If


End Sub

Answer this questsion