Find the actual date or higher one

[MicrosoftNews]

Hi al,

I need your help to solve the following problem.

I've got a column where not every cell contains a date. Now I need to've a script which search this column for the actual date. If the date isn't found the script should go to the next higher date which is found below an choose this cell.

That's the problem I can't solve at the moment. Can somebody help ?


WinXP - XL2003
Regards Joachim

 

[Don Guillett]

Look in the vba help index for FIND

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hi al,

I need your help to solve the following problem.

I've got a column where not every cell contains a date. Now I need to've a script which search this column for the actual date. If the date isn't found the script should go to the next higher date which is found below an choose this cell.

That's the problem I can't solve at the moment. Can somebody help ?


WinXP - XL2003
Regards Joachim

 

[Peter T]

Depending on what you are doing you might find the Match function works well
for your needs. Dates would need to be ordered, presumably ascending, eg

v = Application.Match(CLng(dt), rng, 1)

where dt is a date value and rng is a single row or column of cells
containing only sorted ascending dates, eg

Dim rng as range
Set rng = ActiveSheet.Range("B2:B20")

"go to the next higher date which is found below an choose this cell" ?
if I follow correctly -

v = Application.Match(CLng(dt), rng, 1)

If Not IsError(v) Then
If rng(v) < dt Then
' not a match so get next highest date
v = v + 1
End If

Regards,
Peter T


Hi al,

I need your help to solve the following problem.

I've got a column where not every cell contains a date. Now I need to've a
script which search this column for the actual date. If the date isn't found
the script should go to the next higher date which is found below an choose
this cell.

That's the problem I can't solve at the moment. Can somebody help ?


WinXP - XL2003
Regards Joachim

 

[MicrosoftNews]

I tried it, but it doesn't work.

this is the script I use:
##################################
Sub atest()

Dim ThisDay As Date
Dim rng As Range
Set rng = ActiveSheet.Range("E5:E276")
ThisDay = Range("AI4")
v = Application.Match(CLng(ThisDay), rng, 1)
If Not IsError(v) Then
If rng(v) < ThisDay Then
' not a match so get next highest date
v = v + 1
End If
End If
End Sub
######################################
I'm not so good in programming

Joachim

 

[Peter T]

What doesn't work. Are your dates sorted in ascending order, do all cells in
the range contain dates, is 'theDate' between lowest and highest dates.

In a new sheet or a sheet with empty column-B run SetupTestData to places
some dates in colB.

Run test(). Also try stepping through, put cursor in the procedure and keep
pressing F8


Sub SetupTestData()
Dim i As Long
Dim dt As Date
Dim cell As Range

dt = DateSerial(2007, 1, 1)
For Each cell In Range("B2:B20").Cells
cell = dt
dt = dt + 7
Next

End Sub

Sub test()
Dim i As Long
Dim theDate As Date
Dim v As Variant
Dim rng As Range

Set rng = ActiveSheet.Range("B2:B20")

theDate = Range("B10") ' 2007 2 26
For i = 1 To 2
v = Application.Match(CLng(theDate), rng, 1)

If Not IsError(v) Then
If rng(v) <> theDate Then
' not exact match so get the next highest
v = v + 1
End If
MsgBox rng(v).Address & vbTab & rng(v).Value, , _
"search " & theDate
Else
MsgBox "Not found", , "search " & theDate
End If

theDate = theDate + 1
' 2007 2 27 this won't match
' so will want to get the next highest match
' which will be 2007 3 5
Next

End Sub

As written, test should advise 1st date in $B$10, 2nd date in $B$11

Regards,
Peter T


I tried it, but it doesn't work.

this is the script I use:
##################################
Sub atest()

Dim ThisDay As Date
Dim rng As Range
Set rng = ActiveSheet.Range("E5:E276")
ThisDay = Range("AI4")
v = Application.Match(CLng(ThisDay), rng, 1)
If Not IsError(v) Then
If rng(v) < ThisDay Then
' not a match so get next highest date
v = v + 1
End If
End If
End Sub
######################################
I'm not so good in programming

Joachim

 

[MicrosoftNews]

thanks .. now it works for me
... thanks to all and Peter T