I have following code (shortened):
Dim rng As Range
Range("A1:K2000").Select
Set rng = Selection.Find(What:="MJ")
If Not rng Is Nothing Then
rng.Cells.Select
Dim furthest_row As Integer
rng.Offset(2000, -4).Select
Selection.End(xlUp).Select
If ActiveCell.Row > furthest_row Then
furthest_row = ActiveCell.Row
End If
rng.Offset(2000, -3).Select
Selection.End(xlUp).Select
If ActiveCell.Row > furthest_row Then
furthest_row = ActiveCell.Row
End If
Range("T9:T" & furthest_row).Select
.
.
.
ElseIf rng Is Nothing Then
Worksheets(ActiveSheet.Index + 1).Activate
End If
Next wrksheet
I need to modify the code: Range("T9:T" & furthest_row).Select
to code with Offset and furthest_row variable
Something like this:
Range(rng.Offset(3, 15), 'whole current column') & furthest_row).Select
I chose nearly ten combinations, but I was not successful.
TY !
Best Answer
This does not really answer your question, but is too long for a comment. I'm not sure what you want to do with Column T, or whether you are looking for something three columns to the right of the column in which you found
MJ
If you clarify exactly what you are wanting to do with regard to: I need to modify the code: Range("T9:T" & furthest_row).Select to code with Offset and furthest_row variable, I suspect the answer will be obvious, especially after cleaning up the code.
There are also arguably better methods of finding the
furthest_row
using theRange.Find
method, looking either for the last row used on the worksheet; the last row with data, the last row limited to several columns, etc.But there are a number of issues with your code, so please carefully read the inline comments.
Also see How to avoid using Select in VBA