Date issue - Someone please look at this and tell me I'm not crazy!

[aMember]

Argh! It's been one of those days.

OK, create a new spreadsheet.
In column A, enter a date in mmm-yy format.
In column B, enter several dates in mmm-yy format.

In a VBA module, try to use .match or .find to lookup Col-A date in column B.
The only way I have been able to get this to work is to hardcode A as a serial date and format column b as serial date. Obviously, hard-coding isn't an option.

Please Help!

 

[xlbo]

pick up the format from the cells you're searching in

FindWhat = format(Range("A1&quot.value,range("B1&quot.numberformat)

With columns("B&quot
set FCell = .find(FindWhat)
msgbox FCell.address
end with

HTH Rgds
~Geoff~

 

[aMember]

Thanks, Geoff, I had not thought of going that route.

But, I tried it and it didn't work.

In spare cells on WS, I did
=Cells("format", A1) and
=cells("format&quot,B1)

Both came back format code D3, which is mmm-yy format. Great!

But, in code when I stepped past this line:

FindWhat = Format(Range("Month3&quot.Value, DateRange(1).NumberFormat),
(where Month3 is cell A1 and DateRange is used cells in column b),

DateRange(1).NumberFormat = "d-mmm", which is format code D2.

If I change the line of code to:

FindWhat = Format(Range("Month3&quot.Value, "mmm-yy&quot,
Set FCell = DateRange.Find(FindWhat)

FCell is nothing.
Any ideas what I'm doing wrong? I mean, it is obvious from the formatting codes above I have the right format.

 

[RobBroekhuis]

I think all you need is:

With columns("B&quot
set FCell = .find(datevalue(range("A1&quot)
msgbox FCell.address
end with

Rob

 

[Zathras]

Try this variation:

Sub FindDate()
Dim FCell As Range
With Columns("B&quot
Set FCell = .Find(Range("a1&quot.Value, LookIn:=xlFormulas)
If Not FCell Is Nothing Then
MsgBox FCell.Address
Else
MsgBox "Not found"
End If
Set FCell = Nothing
End With
End Sub

 

[aMember]

Thanks also, Rob!, but that wasn't it either.
Seems like this should be pretty straight forward. It's got to be some fluky thing with the format statement.

Oh, you know, I am running Excel '97.
Columns A & B are actually in 2 different, but open, workbooks. Does that matter?

Thanks for all your help. Keep the suggestions coming if you can.
Andrea

 

[RobBroekhuis]

Hmmm... The solution I proposed worked for me. I even made the date format of the value to be found different from the range being searched. Maybe I misunderstand your problem? To be sure your problem in fact relates to the date format, could you simplify the problem (put both on the same worksheet and try a dummy routine) to troubleshoot?
Rob

 

[aMember]

Have to keep them separate, but thanks anyway. (I at least got some good ideas for another search through your comments so all was not lost!)

I have had to use a brute-force way. Not eloquent but at least it works.

So thanks for the help and quick response!

Andrea

 

[RobBroekhuis]

I wasn't proposing you permanently move things around - but since your problem really wasn't making sense, it often helps to temporarily simplify things. Again, the search methodology worked for me quite robustly. But I too have sometimes just gone with less elegant approaches when the obvious ones don't seem to work ;-)
Rob

 

[xlbo]

I'm guessing that it's gotta be something to do with the 2 workbooks 'cos both mine and Robs method should work Rgds
~Geoff~