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

Look in the vba help index for FIND

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MicrosoftNews" wrote in message ...
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

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


"MicrosoftNews" wrote in message
...
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

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" <peter_t@discussions schrieb im Newsbeitrag ...
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


"MicrosoftNews" wrote in message
...
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

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


"MicrosoftNews" wrote in message
...
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" <peter_t@discussions schrieb im Newsbeitrag
...
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


"MicrosoftNews" wrote in message
...
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

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


"Peter T" <peter_t@discussions schrieb im Newsbeitrag
...
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


"MicrosoftNews" wrote in message
...
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" <peter_t@discussions schrieb im Newsbeitrag
...
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


"MicrosoftNews" wrote in message
...
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