Macro - Finding a cell value within another range.

[mackius]

I am new to macro's but have been able to find bits of code I needed here and there but without a great deal of knowledge on how they are actually working. The following has me stumped though.

I have a worksheet named "Data" with a date value "dd/mm/yy hh:mm" in cell B1.
Column A has many unique date values in it, sorted oldest to newest.

I need to find the cell in "A:A" that contains the same value as in B1. Once this cell is highlighted I will easily be able to delete all values prior to and including the value in B1.
[TABLE="width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/01/13 23:23[/TD]
[TD]02/01/13 22:30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]02/01/13 00:15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02/01/13 08:52[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]02/01/13 22:30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]03/01/13 03:05[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]03/01/13 17:21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]04/01/13 09:12[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The above is a mock up of what the spreadsheet I am using. B1 changes daily and its value depends on when the last data grab occured and when the Macro is being run. Previous attempts to use code found on the net have failed, and I wonder if this has something to do with trying to search on string values but the value actually being a "date time" value.

Any assistance would be appreciated.

 

[JoeMo]

Assuming your dates in columns A & B are dates and not text. This code snippet will find the first exact match in column A to the date/time in B1.

Sub FindMyDateAndTime()
Dim D As Date, dteRng As Range
D = Range("B1").Value
Set dteRng = Intersect(Range("A:A"), ActiveSheet.UsedRange).Find(D, [A1], LookIn:=xlFormulas, lookat:=xlWhole)
If Not dteRng Is Nothing Then
    MsgBox "First match in cell " & dteRng.Address
Else
    MsgBox "No match in column A"
End If
End Sub

 

[mackius]

Brilliant, it worked perfectly. I changed from msg box "first match is in cell" to dterng.activate to select the desired cell.
Thanks.

 

[JoeMo]

Brilliant, it worked perfectly. I changed from msg box "first match is in cell" to dterng.activate to select the desired cell.
Thanks.

You are welcome. Thanks for the reply.