Macro to select a range based on the value of a cell

[danelskibr]

Hello,

I am looking for assistance in creating a macro that will select a range based on the value of a cell.

In cell A1 I have a date. In row 3, I have date headers.

I would like the macro to find the cell in row 3 that matches the date in cell A1, and then select a range beginning directly beneath the header row and down 100 rows.


For example:

Cell A1= 10/1/2016.
The location of 10/1/2016 in my header row is cell D3.

The macro would find 10/1/2016 in cell D3 and select the 100 rows beneath that cell (range of D4:D104).


Thank you in advance for your help!

Brett

 

[Eric W]

Maybe:

Sub SelectDate()

    Rows(3).Find(Range("A1").Value).Offset(1, 0).Resize(100, 1).Select
    
End Sub

This will generate an error if the value is not found.

 

[JoeMo]

If you want to avoid the error if the value is not found try:

Sub GetMeADate()
Dim F As Range
Set F = Rows(3).Find(Range("A1").Value, , xlValues, xlWhole)
If Not F Is Nothing Then F.Offset(1).Resize(101).Select
End Sub

 

[danelskibr]

Thank today both! Exactly what I was looking for.