Position of a range within another range

[XL Pro]

What's the easiest what to determine the position of a range within another range via VBA?

In my case, I want to know what column (not the column number) a range falls within another range?

For example:
Range("C1") in Range("C1:G1")
Range("N1") in Range("L1:O1")

I would like to know that C1 is in the first column of C1:G1, N1 would be in the 3rd column of L1:O1.

 

[Joe4]

Here is a User Defined Function I designed to do that:

Function ColPos(rng1 As Range, rng2 As Range) As Long
'   rng1 is bigger of two ranges
    
    Dim col1 As Long
    Dim col2 As Long

'   Exit if rng2 not found within rng1
    If Intersect(rng1, rng2) Is Nothing Then Exit Function
    
'   Find the column numbers of each range
    col1 = rng1(1, 1).Column
    col2 = rng2(1, 1).Column
    
'   Return the column number of start of rng2 within rng1
    ColPos = col2 - col1 + 1

End Function

So, then you could use it as a Worksheet formula like this:

=ColPos(C1:G1,C1)

Or use it in VBA code like this:

MsgBox ColPos(Range("L1:O1"), Range("P1"))

Note that if the second range is not found within the first, it will return 0.

 

[XL Pro]

{facepalm} sometimes the simplest of math can just trip you up!

Thanks Joe4!

 

[Joe4]

You are welcome.