Select cells in column M, until today's date in column B

[Yulyo]

Hello all,

I need some help and i just can't figure it out by myself. So if you have a few minutes, please help.


I have an excel file with columns from A to X.
On column B, i have some dates, starting from 01.11.2018 (B2)
On column M, i have some values, stating from M2.


Please help me with a macro that can select the values on column M (from a given cell, let's say M5), until today's date is reached in column B.

Thank you in advance.

 

[James006]

Hello,

An important question is related to your dates in Column B ...

Are they sorted ? ... If yes in which order ...?

 

[Yulyo]

Hello James,

Yes, they are sorted, just like this:

01.11.2018
02.11.2018
03.11.2018
04.11.2018
05.11.2018
..........

 

[James006]

Hello again,

OK ... so you can use the Match() function to return both row numbers ... ( for your start and end dates ...)

HTH

 

[Yulyo]

Thank for your answer James,
It's a little bit more complicated. I needed a VBA, because after the step above, i should copy the range and paste it as values on another sheet...
I will try the match function and see what i can do.
Cheers

 

[MARK858]

The below works for me with UK date formats...

Sub findToday()
Dim xRng As Range
Set xRng = Columns(2).Find(CDate(Date), , xlValues, , xlByRows, xlNext)
Range(Cells(5, "M"), Cells(xRng.Row, "M")).Select
End Sub

Not sure whether your dates as posted are "real" dates though as it is unusual to have periods used as date separators.

 

[Yulyo]

Thank you Mark,
I am getting a "runtime error 91 / Object variable or With block variable not set" on this line:

Range(Cells(5, "M"), Cells(xRng.Row, "M")).Select

Regarding the dates, this is how excel format them for my country...

 

[MARK858]

Find the cell with today's date in it then in a blank cell put the formula =ISNUMBER(B2) in it changing the B2 to the cell reference of the cell with today's date in it.
Does the formula return TRUE or FALSE.

 

[Yulyo]

The result is true

 

[MARK858]

What do you get with....

Sub findToday()
Dim xRng As Range
Set xRng = Columns(2).Find(Format(Date, "dd.mm.yyyy"), , xlValues, , xlByRows, xlNext)
Range(Cells(5, "M"), Cells(xRng.Row, "M")).Select
End Sub