Find a cell location

[gheyman]

I have a table that can start in a different row each time the user is working in the workbook.

Is there a way to find what cell the header "Index" is in? It will always be in column B but the row will change from time to time.

I need to use this location in order to find the Intersect (so I would also need to determine the last Row of column B)

here is what I need to adjust:
If Intersect(ActiveCell, Range("B16:E" & LR)) Is Nothing Then
MsgBox "You must select a cell within the applicable Proposal Row. Select a valid Cell"
Exit Sub
End If


Thanks

 

[gheyman]

I was able to use an old post from Mumps to find where "Index" is in column B. But I cannot figure out how to modify this code - specifically the Intersect section.

I want to identify the Intersect. So I tried incorporating Mumps advise from the other post

Dim fndIndex As Range
Set fndIndex = Range("B:B").Find("Index", LookIn:=xlValues, lookat:=xlWhole)


If Intersect(ActiveCell, Range("B" & fnd.Row + 1, Range("AJ" & LR9))) Is Nothing Then
MsgBox "You must select a Cost Source in column E (VendorName). Select a valid Cell, then click Select"
Exit Sub
End If

But this obviously didn't work - Help Please!!!

 

[gheyman]

Solved

If Intersect(ActiveCell, Range("B" & fndIndex.Row + 1, Range("AJ" & LR9))) Is Nothing Then
MsgBox "You must select a Cost Source in column E (VendorName). Select a valid Cell, then click Select"
Exit Sub
End If