Finding current date

[Saturn]

I have a worksheet with large number of rows with dates; one date per row.
I need a macro to find todays date.
Can anyone help me with this procedure ?

Saturn

[zbor]

Something like this:

=INDEX(A:A, MATCH(TODAY(), B:B, 0))

where are dates in B column

[BeerKing]

I have a worksheet with large number of rows with dates; one date per row.
I need a macro to find todays date.
Can anyone help me with this procedure ?

Saturn

I'll answer this in Norwegian as well as English

If I understand you correctly, you don't need a macro, du can use one of the bulit-in formulas in Excel:

Please Login or Register  to view this content.

The actual cell will then always show todays date when you open the file.

Hvis jeg forstår deg riktig, så trenger du ikke en makro, du kan bruke en av Excels innebyggede formler:

Please Login or Register  to view this content.

Så vil den aktuelle cellen alltid vise dags dato når du åpner filen.

[Saturn]

Thank you zbor.

But I am interesting how the VBA procedure is written.

[barryleajo]

build on this?

Please Login or Register  to view this content.

where fndnow is a Range

[royUK]

Try this

Please Login or Register  to view this content.

[snb]

Please Login or Register  to view this content.

[Saturn]

I think I have expressed myself unclear in this thread. Appology to everyone who has answered.

The worksheet has a lot of rows with dates in each row. (The dates are accending down the w/s).
When I am at the top of the worksheet I want to have a button (linked to a macro) which sends me to the row with todays date.
How is the VBA-procedure written ?

[Marcol]

Assign Roys' macro to your button

[Saturn]

It doesn't work.
I get the messagebox which tells me: 19.06.2011 is not listed.
I want the macro to send me to the row with todays date.

Saturn

[watersev]

hi, Saturn, try a bit modified Roy's code:

Please Login or Register  to view this content.

it should work as expected. If it does not, try to change this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

[snb]

The method 'Find' never gives me a result finding a date.(no matter using xlvalue, xlformula,xlwhole,xlpart, format(date,"dd-mm-yyyy"), formatdatetime(date,xldategeneral) or formatdatetime(date,xlshortdate) or a numerical value using 1*Date)

@watersev
The addition "*"... "*" to Date doesn't seem logical, considering the use of the argument 'xlwhole'.

[watersev]

@snb, I do not see any problem with it in my case, run "x"

[Marcol]

I suspect that it is the regional settings that you are using that causes ".Find" to fail.

What regional settings are you using, and how is your system date formatted?

Post a sample workbook with a typical selection of your data, and state your date settings.

This can take forever to sort out without this information.

[snb]

@watersev

I did, only perceiving the messagebox.

BTW, I'd prefer

Please Login or Register  to view this content.

[Marcol]

This should work regardless of formatting and regional settings (see the attached)

Please Login or Register  to view this content.

[snb]

To get the method 'Find' performing I need to convert all dates to numericals:

Please Login or Register  to view this content.