Search column for rows with value greater than X

[StrawS]

Hi

I have a database in excel where each record is on a different row.
The first column in each row is a date.

I need a sub that will identify the rows which have a date greater than X. Then it needs to insert those row-numbers into an array.

I have other search criteria have identified rows where a certain column EQUALS or contains a variable. In these cases I have been able to use a block of code based around the .find method cycling through the whole column checking each row for a match and adding the row number to the array if true. ... but I don't know any way to use the .find method to search for values "greater than" X.

Anyone got ideas?
Thanks in advance!

 

[Derek]

Hi there

Try this on a practise sheet first. It asks for a start date then looks for dates in col A greater than the start date and records the row numbers in column B. Modify to you own requirements.

Sub Test()
'
Dim D As Date
D = InputBox("Insert date in format dd/mm/yy", "DATE")

For Each cell In Range("A1", Range("A65536").End(xlUp))
If cell.Value > D Then Range("B65536").End(xlUp).Offset(1, 0).Value = cell.Row
Next
End Sub

regards
Derek

 

[Derek]

Hi again

Actually, this might be a little better as it deals with the user selecting Cancel from the input box:

Sub Test()
'
Dim D As Date

On Error GoTo Out
D = InputBox("Insert date in format dd/mm/yy", "DATE", 0)

For Each cell In Range("A1", Range("A65536").End(xlUp))
If cell.Value > D Then Range("B65536").End(xlUp).Offset(1, 0).Value = cell.Row
Next
Out:
End Sub

regards
Derek

 

[StrawS]

brilliant.
That'll do it.