Find Date from formula

J

John Wilson

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
 
T

Tom Ogilvy

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.
 
J

John Wilson

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
 
Top