CODEWITHSUNDEEP
vbaexcel
user2888590
user2888590

Reputation: 67

Find specific Cell and using it to create a range

I am currently searching an excel sheet to find a specific cell:

Set findCell = ActiveSheet.Range("D:D").Find(What:=startTerm).Offset(1, 0)

Once I have this cell I want to create a range from that cell downwards an arbitrary number, it doesn't matter now.

Set instrumentList = ActiveSheet.Range("findCell:D100").Cells

I cant' seem to figure out the syntax to make this correct. I essentialy just need to find the cell with some specific text and create a range downwards from it, but I just get an application error.

Upvotes: 1

Views: 60

Answers (1)

Chrismas007
Chrismas007

Reputation: 6105

You would want to use Offset() again like this: (added check for startTerm not found)

If Not findCell is Nothing Then
    Set instrumentList = ActiveSheet.Range(findCell, findCell.Offset(x, 0))
Else
    MsgBox startTerm & " Not Found!"
    Exit Sub
End If

Where x is your arbitrary number of rows down you want to go.

Upvotes: 2

Related Questions