Search through a range for a date greater than specified cell

[CookieMonster76]

Hi

In Cell G1 I have the date 31/3/17 (UK - i.e. 31st March 2017)

In Cells B8 to B20 I have a list of dates.

I need to loop through these cells and stop once I reach a date greater than that specified in G1.

I had an attempt, being the following, but it doesn't work:

Range("B8").Select
ActiveCell.Select
Do Until ActiveCell.Value > Range ("G1")
ActiveCell.Select
Loop

Thanks for any help

Paul

 

[Fazza]

Hi, Paul

suggest you add a few lines through the code, say before the loop, in the loop and after the loop. use\

debug.print activecell.address

then in the Visual Basic Editor,
1 make sure you can see the immediate window. CTRL-G if you can't
2 position the cursor in the code and press F8 to step through code execution line-by-line whilst looking at the immediate window entries

notice what happens. and why

 

[Joe4]

Your code is NEVER moving off of the original active cell. You would need to do something like this:

[COLOR=#333333]Range("B8").Select[/COLOR]
[COLOR=#333333]ActiveCell.Select[/COLOR]
[COLOR=#333333]Do Until ActiveCell.Value > Range ("G1")[/COLOR]
[COLOR=#333333]    ActiveCell[/COLOR][COLOR=#ff0000].Offset(1,0)[/COLOR][COLOR=#333333].Select
[/COLOR]    [COLOR=#ff0000]If ActiveCell.Row = 20 Then Exit Do[/COLOR]
[COLOR=#333333]Loop[/COLOR]

However, Select statements are pretty inefficient and slow down your code. I would probably choose a method like this instead:

Dim cell as Range
For each cell in Range("B8:B20")
    If cell.Value > Range("G1") Then Exit For
Next cell