Search column for rows with value greater than X

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
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!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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
 

Forum statistics

Threads
1,137,298
Messages
5,680,678
Members
419,924
Latest member
Dhamodharan992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top