cell within range reference

[BrianMH]

Is there a way to return a column and row index for a cell in a range.

For instance if I have range B2:D4 and the activecell for instance is B3 I would like to be able to return in vba what row and column the active cell is in within that range not within the entire worksheet. I know I can refer to it by range(B2:D4).cells(2,1) how do I basically get it to do the opposite and tell me the cell reference for that range.

Hope that makes sense.

[mdmackillop]

I'm sure there is a neater way, but until it comes along
[VBA]Sub RelativeRef()
Dim rng As Range
Dim cel As Range
Dim Rw As Long, Col As Long
Set cel = Cells(12, 9)
Set rng = Range(Cells(8, 8), Cells(20, 11))
Rw = cel.Row - rng(1).Row
Col = cel.Column - rng(1).Column
MsgBox "(" & Rw & ":" & Col & ")"
End Sub
[/VBA]

[Bob Phillips]

I think you missed a couple of 1s MD

[vba]

Sub RelativeRef()
Dim rng As Range
Dim cel As Range
Dim Rw As Long, Col As Long

Set cel = Cells(12, 9)
Set rng = Range(Cells(8, 8), Cells(20, 11))
Rw = cel.Row - rng(1).Row + 1
Col = cel.Column - rng(1).Column + 1
MsgBox "(" & Rw & ":" & Col & ")"
End Sub
[/vba]

[mdmackillop]

Thanks Bob,
I'm glad someone is watching to keep me right!

[GTO]

Greetings to all,

Well... probably less efficient, but was thinking either row,col, or both could be returned like...

[vba]Function InRange_Ret(Rang As Range, _
Optional Coll As Integer, _
Optional Roww As Long) As Variant()

Dim a(0 To 1)

If Coll > 0 _
Then a(0) = Rang.Columns(Coll - Rang.Columns(1).Column).Column

If Roww > 0 _
Then a(1) = Rang.Rows(Roww - Rang.Rows(1).Row).Row

InRange_Ret = a
End Function


Sub CallIt()
Dim rngBig As Range
Set rngBig = Range("B2:K11")

If Not Application.Intersect(ActiveCell, rngBig) Is Nothing Then
MsgBox "Col is: " & InRange_Ret(rngBig, ActiveCell.Column)(0)
MsgBox "Row is: " & InRange_Ret(rngBig, , ActiveCell.Row)(1)
End If
End Sub[/vba]

I've only tried returning an array from a function a few times, so please point out any poor thinking on my part.

Mark