Avoid re-entering 'Find what:'
 

Col A contains a list of dates. In Cell B1 I enter a single date. How do I
write a macro to find the cell in Col A with the date which matches B1 and
make it the Active Cell. I could use Edit/Find but do not want to re-enter
the B1 date in 'Find what:'. I want the macro to use the B1 date
automatically.

Avoid re-entering 'Find what:'
 

Hi,

You could try this. Right click the sheet tab, view code and paste this in:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$B$1" Then
lastrow = Range("A65536").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value = Target.Value Then
c.Offset(0, 1).Select
Exit Sub
End If
Next
End If
End Sub

Mike

"Gav" wrote:

Col A contains a list of dates. In Cell B1 I enter a single date. How do I
write a macro to find the cell in Col A with the date which matches B1 and
make it the Active Cell. I could use Edit/Find but do not want to re-enter
the B1 date in 'Find what:'. I want the macro to use the B1 date
automatically.

Avoid re-entering 'Find what:'
 

Option Explicit
Sub Find_Date()
Dim Yday As Date
Dim rng As Range
Dim rng1 As Range
Dim rwStart As Long
Dim rwEnd As Long
Yday = Range("B1").Value
Set rng = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
rwStart = rng.Rows(1).Row
rwEnd = rng.Rows(rng.Rows.Count).Row
For i = rwEnd To rwStart Step -1
Set rng1 = Cells(i, "A")
If Not rng1.HasFormula Then
If IsDate(rng1.Value) Then
If CDate(rng1.Value) = Yday Then
rng1.Select
End If
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 19 Nov 2007 02:40:00 -0800, Gav wrote:

Col A contains a list of dates. In Cell B1 I enter a single date. How do I
write a macro to find the cell in Col A with the date which matches B1 and
make it the Active Cell. I could use Edit/Find but do not want to re-enter
the B1 date in 'Find what:'. I want the macro to use the B1 date
automatically.