CODEWITHSUNDEEP
excelexcel-2010vba
acr_scout
acr_scout

Reputation: 579

Excel VBA find a cell on worksheet with specific value (string)

I need help finding a text value on a worksheet and offset from there two rows down.

I have a cell with "Bottom of Range" title in it. Two cells below that is the actual value. The issue is "Bottom of Range" could be in 7 possible places depending on the worksheet template.

How can I find the cell with "Bottom of Range" in it? I can then .Offset(2,0).value2 to get what I want.

In the grander scheme of things. I am cycling through hundreds of workbooks and then through each of their worksheets collecting data and centralizing it. Because the values I am looking for could be anywhere I need to look for their title/definition before getting to the values.

Upvotes: 5

Views: 54610

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

Here you go! You want to use .Find:

Sub t()
Dim bottomCell As Range
Dim offsetCell As Range
With Sheets("Sheet1")
    Set bottomCell = .Cells.Find(what:="Bottom of Range")
    Set offsetCell = bottomCell.Offset(2, 0)
    ' Now, your offsetCell has been created as a range, so go forth young padawan!
End With
End Sub

Upvotes: 8

Related Questions