Trying to help out another poster and am stuck with a dilemma.
Dates are not my forte.

I have a date from one sheet stored in a variable:

Dim eDate As String
eDate = Range("A" & Target.Row).Value

MsgBox eDate results in "8/28/03"

Now I need to find that date in column "A" of another sheet.
That part would be easy except for the fact that the dates in
column A are the result of formulas:
The corresponding date 8/28/03 in column A is represented
by the formula ='2003'!A283

How (or is it possible) to search the formulas in column A
where the result of the formula would equal 8/28/03??

I'm actually looking to get the corresponding row number of
where the match is found.

Thanks,
John

Option Explicit
Sub FindDate()
Dim rng As Range, rng1 As Range
Dim edate As Date
Dim res As Variant
Set rng = Worksheets("sheet1").Range("A9")
edate = rng.Value
With Worksheets("Sheet2")
Set rng1 = .Range(.Cells(1, 1), .Cells(365, 1))
End With
res = Application.Match(CLng(edate), rng1, 0)
If Not IsError(res) Then
MsgBox "found at " & rng(res).Address(external:=True)
End If
End Sub

Worked for me. Cells in Sheet2 were formulas.

--
Regards,
Tom Ogilvy


"John Wilson" wrote in message
...
Trying to help out another poster and am stuck with a dilemma.
Dates are not my forte.

I have a date from one sheet stored in a variable:

Dim eDate As String
eDate = Range("A" & Target.Row).Value

MsgBox eDate results in "8/28/03"

Now I need to find that date in column "A" of another sheet.
That part would be easy except for the fact that the dates in
column A are the result of formulas:
The corresponding date 8/28/03 in column A is represented
by the formula ='2003'!A283

How (or is it possible) to search the formulas in column A
where the result of the formula would equal 8/28/03??

I'm actually looking to get the corresponding row number of
where the match is found.

Thanks,
John

Tom,

Thank you very much for that.
Apologies for not posting a reply sooner.
Tried it on the workbook that I was helping someone with and
it didn't work. Not your fault. Your code works perfectly in a test
workbook, but the workbook I was trying to use this on has a
number of blank cells in column "A" (forgot to mention that caveat).
As it turns out, the OP"did" want the information on the same sheet
(meaning that a simple Offset would do the trick) and he'll pull that
information onto his main sheet via formulas (made it a lot easier
for me).

Anyway, I will save your code for future reference as I'm sure
I'll come across a similar scenario again and thanks for giving
me just a little bit more insight into dates in Excel which I think
only you and a select few others truly understand. Hopefully,
someday, I'll be part of that select few.

Thanks again,
John

"Tom Ogilvy" wrote in message
...
Option Explicit
Sub FindDate()
Dim rng As Range, rng1 As Range
Dim edate As Date
Dim res As Variant
Set rng = Worksheets("sheet1").Range("A9")
edate = rng.Value
With Worksheets("Sheet2")
Set rng1 = .Range(.Cells(1, 1), .Cells(365, 1))
End With
res = Application.Match(CLng(edate), rng1, 0)
If Not IsError(res) Then
MsgBox "found at " & rng(res).Address(external:=True)
End If
End Sub

Worked for me. Cells in Sheet2 were formulas.

--
Regards,
Tom Ogilvy


"John Wilson" wrote in message
...
Trying to help out another poster and am stuck with a dilemma.
Dates are not my forte.

I have a date from one sheet stored in a variable:

Dim eDate As String
eDate = Range("A" & Target.Row).Value

MsgBox eDate results in "8/28/03"

Now I need to find that date in column "A" of another sheet.
That part would be easy except for the fact that the dates in
column A are the result of formulas:
The corresponding date 8/28/03 in column A is represented
by the formula ='2003'!A283

How (or is it possible) to search the formulas in column A
where the result of the formula would equal 8/28/03??

I'm actually looking to get the corresponding row number of
where the match is found.

Thanks,
John