VBA find today's date in Column A

[Guest]

Hi,

I have the date for everyday in column A from 2001 out to 2015. I want a
vba function that finds today's date in column A and makes it the active cell.

Can anyone help please?

Thanks,

B/

 

[Guest]

Sub find_date()
Dim d As Date, i As Long
d = Date
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Cells(i, "A").Value = d Then
Cells(i, "A").Select
Exit Sub
End If
Next
End Sub

 

[Guest]

The following should allow you to enter a date at the input box and the find
method will find and activate the cell with the date. If not found then a
message is displayed.

Note the comment about editing to insert your worksheet name
Sub Find_Date()

Dim rng1 As Range
Dim dateStr As String
Dim dateToFind As Date
Dim foundDate As Range

'Get date as string value
dateStr = InputBox("Enter the date to be found")

'Convert string value to date format
dateToFind = DateValue(dateStr)

'Edit Sheet1 to your worksheet name
Set rng1 = Sheets("Sheet1").Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp))

Set foundDate = rng1.Find(What:=dateToFind, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not foundDate Is Nothing Then
foundDate.Select
Else
MsgBox dateStr & " not found"
End If


End Sub

Regards,

OssieMac

 

[Guest]

My apologies. You wanted to find today's date not a date you input and I
copied a macro that I had without amending it so try this code instead.

Sub Find_Date_2()

Dim rng1 As Range
Dim dateToFind As Date
Dim foundDate As Range

dateToFind = Date 'This is today's date

'Edit Sheet1 to your sheet name
Set rng1 = Sheets("Sheet1").Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp))

Set foundDate = rng1.Find(What:=dateToFind, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not foundDate Is Nothing Then
foundDate.Select
Else
MsgBox dateStr & " not found"
End If


End Sub

Regards,

OssieMac

 

[Guest]

Both pieces of code are good.

Thanks Guys,

B/

My apologies. You wanted to find today's date not a date you input and I
copied a macro that I had without amending it so try this code instead.

Sub Find_Date_2()

Dim rng1 As Range
Dim dateToFind As Date
Dim foundDate As Range

dateToFind = Date 'This is today's date

'Edit Sheet1 to your sheet name
Set rng1 = Sheets("Sheet1").Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp))

Set foundDate = rng1.Find(What:=dateToFind, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not foundDate Is Nothing Then
foundDate.Select
Else
MsgBox dateStr & " not found"
End If


End Sub

Regards,

OssieMac