Offset to two dynamic ranges

Question

I have two dynamic ranges that I'll call rng1 and rng2. I have a for each/next loop going through rng1 (example: A2,A3,A4,A5, etc...) I also need to get the same row value from rng2 (example: T2,T3,T4,T5, etc), but the range isn't always the same so a standard offset won't work (that I know of).

Any ideas?

How I set up the range (excludes the header and finds the last row)

lastrowTN = ws.Cells(65536, phcell.Column).End(xlUp).Row
Set rngtocopyTN = ws.Range(phcell.Offset(1, 0).Address, Cells(lastrowTN, phcell.Column))

The ranges are created and based on the header. I'll add an example in my post. — Mike
This doesn't make sense yet, but if you elaborate, I think the solution should be fairly straightforward... — John Bustos
you need to define any relation between these two ranges. between column A and T there is just .Offset(0,19) but you said it's not working. so, find the rule to get help... — Kazimierz Jawor
I am doing a .find on the column headings to find the right column. I apologize for not adding that. So, based on the .find... I identify the column and do my loop on that column. I do another .find to identify the other column and set that range. I just cant figure out how to relate the two ranges and offsets. — Mike

tigeravatar tigeravatar · Accepted Answer · 2013-08-13T18:22:30

It's hard to say without context, but I think the Intersect method can accomplish what you're looking for. It will find an intersection regardless of how close or far away the two ranges are. So using your example:

[EDIT]: I realized that you could alternatively use the Cells method (code has been updated to show that as well)

Sub tgr()

    Dim rng1 As Range
    Dim rng2 As Range
    Dim LoopCell As Range

    Set rng1 = Range("A2:A5")
    Set rng2 = Range("T2:T5")

    For Each LoopCell In rng1.Cells
        MsgBox Intersect(LoopCell.EntireRow, rng2.EntireColumn).Address
        MsgBox Cells(LoopCell.Row, rng2.Column).Address
    Next LoopCell

End Sub

Note that rng1 and rng2 could be in any column and it would still find them (assuming of course that they are both on the same sheet).

Offset to two dynamic ranges

1 Answers