Locating an index of a range within a range

  • Thread starter Thread starter robjs
  • Start date Start date
R

robjs

I have a double click event handler in Excel that hands me, among othe
things, a range describing the double click target cell. I also have
(single axis) named range within which I know the double click targe
lives. What I'd like is an index - the numerical position within m
named range, at which the double click took place.

In other words, I have a named Range object, myRange of, say, B4:E4
and the double click target is on cell B4. I'd like a simple way t
determine that the offset of the double click event was '1' within th
named range, or 2 if the double click was on C4, etc.

The named range will always describe a single axis, so, for instance
if myRange described a row range like B4:B9 instead of a column rang
like the previous example, a double click in B6 should return 3, th
offset into that named range.

Any help will be greatly appreciated! Thanks in advance,

- Ro
 
Rob,

I don't like it but this is the best I could come up with

Set myRange = Range("B4:E4")
For i = 1 To myRange.Count
If target.Address = myRange(1, i).Address Then
Exit For
End If
Next
If i > myRange.Count Then
Debug.Print "Not found"
Else
Debug.Print "Found as item " & i
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
One more way:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

Dim myRng As Range
Dim myItem As Long

Set myRng = Me.Range("b5:b19")

If Intersect(Target, myRng) Is Nothing Then Exit Sub

'this works ok
'If myRng.Columns.Count = 1 Then
' myItem = Target.Row - myRng.Row + 1
'Else
' myItem = Target.Column - myRng.Column + 1
'End If

'but so does this.
'if it's the same row, then the row subtraction is 0.
'if it's the same column, then column subtraction is 0.
myItem = Target.Row - myRng.Row + Target.Column - myRng.Column + 1

MsgBox myItem

End Sub
 
Guys -

Thanks to you both for your quick, very helpful responses. Why...
might even go into work tomorrow (Saturday) to try them both out!

You've been a fantastic resource, and my project can now move forwar
thanks to you.

- Ro
 
Back
Top