Finding a specified DATE, HELP required

[alsyourpal]

Gday,

I love this macro stuff it saves me soo much time but unfortunately I havent had any courses on it yet and Im pretty green.

Anyway what I am trying to do is find the row where the date on sheet "Men U_D" is equal to the date in sheet "latest" cell B9. Then do some copying between the sheets ( no need to worry about that) but I'm really struggling with the dates thing any help would be much appreciated.

Regards

Alan O'Connor


Sheets("latest").Range(B9).Select

Dim **** As Date

Set **** = Sheets("latest").Range(B9).Value


Sheets("Men U_D").Select

ActiveWindow.ScrollRow = 1
Range("A1").Select

Do While Cells(x, A).Value <> "****"
x = x + 1

[stnkynts]

It would be better if you could narrow the range on the "Men U_D" sheet. Try this:

Please Login or Register  to view this content.

[AlphaFrog]

Another method using Application.Match

Please Login or Register  to view this content.

[alsyourpal]

Hey thanks so much for the solutions, Alph Frog yours seemed to go the best.... I do have one really dumb question left though, how do I se;ect a cell in the row with the matching date?

I tried


Worksheets("Men U_D").Range("Jr").Select

but I clearly dont know how the variants work so that didnt work..D'oh

[AlphaFrog]

Try something like this...

Worksheets("Men U_D").Select
Range("J" & r).Select
or
Cells(r, "J").Select

Also note; you do not need to select a cell to do something with it i.e., copy or paste. Selecting sheets and cells is very inefficient coding practice. The macro recorder records your selections because that's what you did with the mouse.

This example will copy the value from Worksheets("Men U_D").Range("J" & r) to Sheets("latest").Range("A1") without selecting anything.
Sheets("latest").Range("A1").Value = Worksheets("Men U_D").Range("J" & r).Value