Look up

[LaDdIe]

Hello

I have a range of dates in col A formated as 'ddd dd-mm-yyyy' the range is
named 'DataSheetDate', I would like a Macro that will find a date >= today's
date and select the the cell offset(0,1).

Thanks.

 

[Mike Fogleman]

This will find the first date in the list and then stop looking:

Sub DateFind()
Dim c As Range

For Each c In Range("DataSheetDate")
If c >= Date Then
c.Offset(, 1).Select
Exit Sub
End If
Next
End Sub

Mike F

 

[LaDdIe]

Perfecto

This will find the first date in the list and then stop looking:

Sub DateFind()
Dim c As Range

For Each c In Range("DataSheetDate")
If c >= Date Then
c.Offset(, 1).Select
Exit Sub
End If
Next
End Sub

Mike F



.

 

[michdenis]

Hi,

Replace A1:A6 by your range

=MIN(IF(ISNUMBER(MATCH(A1:A6,N(A1:A6>=TODAY())*A1:A6,0)),A1:A6))



"LaDdIe" <[email protected]> a écrit dans le message de groupe de
discussion : (e-mail address removed)...
Hello

I have a range of dates in col A formated as 'ddd dd-mm-yyyy' the range is
named 'DataSheetDate', I would like a Macro that will find a date >= today's
date and select the the cell offset(0,1).

Thanks.

 

[michdenis]

I forgot to say you need to validate the formula by Ctrl + Maj + Enter

Replace A1:A6 by your range

=MIN(IF(ISNUMBER(MATCH(A1:A6,N(A1:A6>=TODAY())*A1:A6,0)),A1:A6))



"LaDdIe" <[email protected]> a écrit dans le message de groupe de
discussion : (e-mail address removed)...
Hello

I have a range of dates in col A formated as 'ddd dd-mm-yyyy' the range is
named 'DataSheetDate', I would like a Macro that will find a date >= today's
date and select the the cell offset(0,1).

Thanks.

 

[michdenis]

And in case one cell within your range is empty :

Validation : Maj + Ctrl + Enter
=MIN(IF((ISNUMBER(MATCH(A1:A6,N(A1:A6>=TODAY())*A1:A6,0))*(LEN(A1:A6)>0))>0,A1:A6))



"michdenis" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
I forgot to say you need to validate the formula by Ctrl + Maj + Enter

Replace A1:A6 by your range

=MIN(IF(ISNUMBER(MATCH(A1:A6,N(A1:A6>=TODAY())*A1:A6,0)),A1:A6))



"LaDdIe" <[email protected]> a écrit dans le message de groupe de
discussion : (e-mail address removed)...
Hello

I have a range of dates in col A formated as 'ddd dd-mm-yyyy' the range is
named 'DataSheetDate', I would like a Macro that will find a date >= today's
date and select the the cell offset(0,1).

Thanks.

 

[michdenis]

Sorry, i misread the question,

But we can use this :

'-----------------------------------
Sub test()
With Worksheets("Sheet1")
.Activate
With .Range("A1:A6") ' Your range
Range(Evaluate("ADDRESS(MATCH(MIN(IF((ISNUMBER(MATCH(" & _
.Address & ",N(" & .Address & ">=TODAY())*" & _
.Address & ",0))*(LEN(" & .Address & ")>0))>0," & _
.Address & "))," & .Address & ",0)," & _
.Column + 1 & ")")).Select
End With
End With
End Sub
'-----------------------------------





"michdenis" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
And in case one cell within your range is empty :

Validation : Maj + Ctrl + Enter
=MIN(IF((ISNUMBER(MATCH(A1:A6,N(A1:A6>=TODAY())*A1:A6,0))*(LEN(A1:A6)>0))>0,A1:A6))



"michdenis" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
I forgot to say you need to validate the formula by Ctrl + Maj + Enter

Replace A1:A6 by your range

=MIN(IF(ISNUMBER(MATCH(A1:A6,N(A1:A6>=TODAY())*A1:A6,0)),A1:A6))



"LaDdIe" <[email protected]> a écrit dans le message de groupe de
discussion : (e-mail address removed)...
Hello

I have a range of dates in col A formated as 'ddd dd-mm-yyyy' the range is
named 'DataSheetDate', I would like a Macro that will find a date >= today's
date and select the the cell offset(0,1).

Thanks.