Excel – vba range with offset instead of strictly given rows/columns

microsoft excelvba

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 the Range.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

'Always declare all variables
'Under Tools/Options/Editor SET to require variable declaration
'This will put Option Explicit at start of all newly created modules
Option Explicit

Sub terfuge()
 Dim rng As Range

 'I don't know of a reason to use Integer data type
 'It is limited to 2^15 and internally gets converted to a Long
 Dim furthest_row As Long
 Dim rw As Long

'Rarely is there a reason to use Select, Selection, Active, Activate, etc.
'   Use direct references instead
'It clutters the code and, if not very careful, can introduce bugs

Set rng = Range("A1:K2000").Find(What:="MJ") 'probably should add the other arguments,
                                                'as most will default to the last used, which may not be what you want
    If Not rng Is Nothing Then

        'what happens if "MJ" is in column A:D?  This next line will cause an error
        rw = rng.Offset(2000, -4).End(xlUp).Row
            If rw > furthest_row Then
                furthest_row = rw
            End If

         rw = rng.Offset(2000, -3).End(xlUp).Row
            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
End Sub
Related Question