How to jump to the cell with current date when open an Excel file

[JJRed]

Hi,

I have question regarding How to jump to the cell with current date when open an Excel file.

below are the links to my file.

https://drive.google.com/open?id=1_5H6x6pA53OcbOyvtpRfXm6oLVnT1vVa

I have tried using below VBA macro but it didn;t work.

Private Sub Workbook_Open()
Worksheets(“Sheet1”).Select
x = Day(Date)
Worksheets(“Sheet1”).Columns(2).Find(What:=x, LookIn:=xlValues).Activate
End Sub

Please advise.

 

[nemmi69]

Try

Dim LastColNo As Long
Dim TodayDate As Long
Dim DateLoop As Long




Private Sub Workbook_Open()
LastColNo = Worksheets("Sheet1").Cells(4, Columns.Count).End(xlToLeft).Column
TodayDate = Now()


For DateLoop = 3 To LastColNo
If TodayDate = Worksheets("Sheet1").Cells(4, DateLoop).Value Then
Worksheets("Sheet1").Cells(4, DateLoop).Select
Exit For
End If
Next DateLoop
End Sub

 

[JazzSP8]

Hi - Welcome to the Board

I can't download your Workbook as I'm at work and file sharing sites are blocked, but I did notice you said you wanted to go to a cell with the current date?

In your code the variable x will resolve to 2.

Is that the value you're looking for or is it a proper date you have in your cells? (2 wont match to 2/8/2018)

 

[JJRed]

Try

Dim LastColNo As Long
Dim TodayDate As Long
Dim DateLoop As Long




Private Sub Workbook_Open()
LastColNo = Worksheets("Sheet1").Cells(4, Columns.Count).End(xlToLeft).Column
TodayDate = Now()


For DateLoop = 3 To LastColNo
If TodayDate = Worksheets("Sheet1").Cells(4, DateLoop).Value Then
Worksheets("Sheet1").Cells(4, DateLoop).Select
Exit For
End If
Next DateLoop
End Sub

When I open the file it jumps not on the current date but on the next date.

Please advise

 

[nemmi69]

Have to say, I have no idea why this would happen

 

[Fluff]

How about

Private Sub Workbook_Open()
   Dim Fnd As Range
   
   Set Fnd = Sheets("Sheet1").Range("B4:AV4").Find(Format(Date, "dd mmm yy"), , xlValues, , , , , , False)
   If Not Fnd Is Nothing Then Application.Goto Fnd, True

End Sub