.Find and Dates..

[p45cal]

I'm trying to write a function that can be used on the worksheet and from vba, and I'm coming up with a problem.
In the attached workbook there are formulae in cells C5 and C6 containing calls to the UDF GetRowNo.
These work fine and show the row number of the date sought in column A (even when the format of cells in column A is different from the format of the date used in cell C6).

In the code module, below the UDF, is a macro called test. As far as I can see it calls the UDF with the same data, yet it does not find the data. Stepping through it with F8 after the Stop instruction and watching the Locals pane, everything seems exactly the same.

Do you people get the same result?
What's the difference?
How do I fix it?

(I'm using Excel 2007 in Compatibility Mode)

[Paul_Hossler]

This seems to work for me

1. I always use Option Explicit
2. I changed the 'Header' to a Date variable, since I think trying to .Find a String when Dates are really numeric confused it
3. #5/22/2009# is a explicit Date type parameter, sort of like "ABC" is an explicit string parameter

[VBA]
Option Explicit
Function GetRowNo(Header As Date) As Variant
Dim xxx As Range

GetRowNo = "Not found"
Stop
Set xxx = Sheets("Sheet8").Columns(1).Find(What:=Header, LookAt:=xlWhole, LookIn:=xlFormulas)
If Not xxx Is Nothing Then GetRowNo = xxx.Row
End Function
Sub test()
MsgBox GetRowNo(#4/21/2006#)
End Sub
[/VBA]

Paul

[p45cal]

In haste 'cos I'm dog tired..
First, thanks for looking at this.
I tried this in Excel 2003 and while the call from code worked fine, both the worksheet cell calls returned 'not found'.
I'll play a bit more tomorrow..

[Paul_Hossler]

Well, I have 2007 and it seems to work as expected under 2007 in compatibility mode

This is the WB -- give it a try after some coffee :-)



Paul

[p45cal]

Paul,
with coffee consumed I tried your attached file in xl2007 compatibility mode, and.. I got the same as when I tried the code in xl2003, that is, the code call worked , but the worksheet calls didn't. Is there some setting/option somewhere do you think, that could cause us to get such different results?

In the end, I'm hoping to have the same function find any data type in column 1, not just dates.

[p45cal]

Would some people be able to check whether Paul's UDF (in the attachment in msg #4) works both in the Worksheet and in the code for any versions of excel please?
cheers,

[p45cal]

Would some people be able to check whether Paul's UDF (in the attachment in msg #4) works both in the Worksheet and in the code for any versions of excel please?
cheers,

No one at all?

[GTO]

Greetings Pascal,

As requested at #6:

I tried Paul's (at #4) in excel2003/XP. With test data, worked fine both as UDF and when called from the Sub.

Mark

[p45cal]

Thanks GTO,
I'm beginning to think the calls only work from both VBA AND the worksheet when the machine is working under English(US) locale settings (or at least where the locale's setting for dates is month/day/year.

If anyone else could chip in with results that would be helpful..

regards,

[GTO]

Please forgive if I am missing something (particularly if it should be painfully obvious), but if you don't mind the question, have you tried returning by the date's Long?

Mark

[p45cal]

Please forgive if I am missing something (particularly if it should be painfully obvious), but if you don't mind the question, have you tried returning by the date's Long?

Mark

Mark, I have. I've tried all sorts, trying to convert to/from dates at different places. It's beginning to look as thoiugh it will be quite an ugly function when done if it's going to cater for dates/date and times/non dates, including a range of locales. I was hoping there would be something simple and universal to handle the dates aspect.
Thanks,

[tpoynton]

Works fine for me with US locale. What's interesting to me is that the formula =CELL("format",A2) returns "G", which is General, or what I think is an unrecognized format (Help says the format is "# ?/? or # ??/??"). When I look at the format of the existing cells by right-clicking and selecting format cells, the date format is recognized as a UK locale date format.

I know you say you have tried lots of things in terms of formatting; have you tried dateserial?

Header = DateSerial(Year(Header), Month(Header), Day(Header))

[p45cal]

.. have you tried dateserial?
Header = DateSerial(Year(Header), Month(Header), Day(Header))

Thanks for looking and reporting.

It looks as though that sort of thing is what I will resort to later if there is nothing simpler that I don't know about.

regards,

[geekgirlau]

I have learnt the hard way to always "resort to" handling dates in this sort of manner. Regardless of your regional settings, VBA is very US-centric when it comes to date handling.

Bottom line, make NO assumptions when it comes to dates!