Error R/T 1004

[jim may]

I'm getting R/T Error 1004 on RED Line below;
My Range(C2:C5) are 4 strings (AA, BB, CC, DD)
My target cell - while a formula cell - returns a string - such as BB
Ant assistance appreciated.. Jim

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address <> "$E$1" Then Exit Sub
Dim r As Long, v As String
Cancel = True
Set Rng = Range("C2:C5")
Worksheets("Sheet2").Activate
r = Application.WorksheetFunction.Match(Target.Value, Rng, 0)
Cells(r, 3).Select
End Sub

 

[AlphaFrog]

Worksheets("Sheet2").Activate
Set Rng = Range("C2:C5"),,,, "defined on Sheet2!!

Thanks again for all your assistance. Jim

When the code is in a worksheet's code module, unqualified ranges refer to the worksheet of the module and not the ActiveSheet. That's why I used the With Sheet2 to qualify the search range.


Did you try the hyperlink formula in post #7 ?

 

[jim may]

"unqualified ranges refer to the worksheet of the module and not the ActiveSheet" -- Thanks for this type "hidden" fact..

Yes, the hyperlink formula was SPOT-ON!! Why start out the link with the "#" ? -- No such examples are furnished in the Hyperlink Function Help Files. Jim

 

[AlphaFrog]

Why start out the link with the "#" ?

The # indicates the hyperlink location is within the current workbook.