macro to select row

[kendel]

Hello,

I'd like a macro that will select the row that corresponds to todays date. Dates are in Col A, and todays date is the =today() formula in A3. I'd like the macro to run with a call sub when I open the workbook. The objective is to just select the row, as if I clicked on the row header.

I'm using WIN XP & excel 2007

Thanks,
Kendel

 

[rjwebgraphix]

if it is always A3 you're looking for then

Rows(3).Select

would do the job, however if you're looking at all rows in column A starting at the 2nd Row to the last row you can use.

Dim DateToday As String
DateToday = Date

Dim i As Long
Dim lr As Long
lr = Range("A" & Rows.count).End(xlUp).row
For i = 2 To lr
    If Cells(i, "A").Value = DateToday Then
        Rows(i).Select
    End If
Next i

although, the fastest way would be to just find the row with the date.

Dim DateToday As String
DateToday = Date

Dim DateRow As Long
DateRow = Columns("A").Find(DateToday, [A2], , xlWhole, xlByRows, xlPrevious).row

Rows(DateRow).Select

All methods should work. I just immediately went with a loop at first then went Duh! .find would be better and didn't want a good loop to go to waste.

 

[rjwebgraphix]

Please Pardon my 3rd example above. It was missing something to work with a formula for a date....

this one would work for a .find method.

Dim DateToday As String
DateToday = Date

Dim DateRow As Long
DateRow = Columns("A").Find(DateToday, [A2], xlValues, xlWhole, xlByRows, xlPrevious).row

Rows(DateRow).Select

 

[kendel]

RJ!!
I couldn't let that loop go to waste!! works great THANKS!!!

 

[kendel]

Just want to clarify a little for anyone coming to this

Col A is a list of dates, so I put a button linked to the loop macro in A2 and it selects the row with today's date
just too lazy to scroll to it lol!!

Thanks again RJ!!

 

[rjwebgraphix]

RJ!!
I couldn't let that loop go to waste!! works great THANKS!!!

LOL. For something this simple, the loop method does work, but the .find method is faster. If there isn't a lot of data you wouldn't notice much of a difference, but if there were 50,000 rows of data, the loop method would take a lot longer than the .find method and it'll become very noticeable.

 

[kendel]

Hey RJ,
I tried running your Find macro but get Run-time error '13': Type Mismatch
I substituted A4, the first cell of the date range, for the [A2] in your code, and when I run the debug, A4 shows 'empty', and the DateToday=0.
It's my guess that the mismatch has something to do with the formating of my date series.
When I run the debug, the 'DateToday' shows as "m/d/yy", text, the date format in the cells are m/d/yy, date format, but it shows in the formula bar as m/d/yyyy, and all attempts at changing that have not been successful.
Can you walk me thru the sequence and help me figure out what's happening?

Thanks,
Kendel