Error R/T 1004

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,486
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


Rich (BB code):
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
The Event code is behind Sheet1 (next). r should = 5 so that Cell C5 gets selected.


Excel 2012
ABCDE
1Select Link from this Dropdown >>>GGGMSNBC

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>
Sheet1

Worksheet Formulas
CellFormula
E1=VLOOKUP(D1,Sheet2!B2:C5,2,FALSE)

<THEAD>
</THEAD><TBODY>
</TBODY>

<TBODY>
</TBODY>



Excel 2012
ABC
1My Table >>>W-SiteVariable
2ABCFord
3DDDYahoo
4FFFMrExcel
5GGGMSNBC

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>
Sheet2

I changed my code to:

Code:
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
Worksheets("Sheet2").Activate
Set Rng = Range("C2:C5")
r = Application.WorksheetFunction.Match(Target.Value, Rng, 0)
Cells(r, 3).Select
End Sub
 
Last edited:
Upvote 0
Hi,

It seems to be the Worksheets("Sheet2").Activate that is causing the confusion.

Well it is for me if I put the macro behind Sheet1. It works OK behind Sheet2, buth then Sheet2 is already activated.

I presume the Range("C2:C5") and Cells(r, 3).Select are expecting another sheet?

I am not quite sure what you are trying to do but the Worksheets("Sheet2").Activate part needs looking at.

I hope this helps.
 
Upvote 0
After passing the Set Rng line ( Using the F8 Step through)
from the immediate window, I get
? Rng.address
$C$2:$C$5
In the r = Application line
the target.value = "MSNBC" < when holding cursor over the target.value portion of the current yellow line
 
Upvote 0
Try this...

Code:
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_BeforeDoubleClick([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range, Cancel [color=darkblue]As[/color] [color=darkblue]Boolean[/color])
    [color=darkblue]Dim[/color] r [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]If[/color] Target.Address <> "$E$1" [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    Cancel = [color=darkblue]True[/color]
    [color=darkblue]With[/color] Worksheets("Sheet2")
        r = Application.WorksheetFunction.Match(Target.Value, .Range("C2:C5"), 0)
        Worksheets("Sheet2").Activate
        .Cells(r + 1, 3).Select
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Also, it could be do with just a hyperlink formula instead of VBA.

Code:
=HYPERLINK("#"&ADDRESS(MATCH(D1,Sheet2!$B$1:$B$5),3,,,"Sheet2"),VLOOKUP(D1,Sheet2!$B$2:$C$5,2,FALSE))
 
Upvote 0
Wow!! That worked... Can you offer any additional comments as to why the previous try didn't work -- Thanks for everything. Jim
 
Upvote 0
Wow!! That worked... Can you offer any additional comments as to why the previous try didn't work -- Thanks for everything. Jim

You're welcome.

In your original code, ask yourself what sheet is rng defined on?
Set Rng = Range("C2:C5")
 
Upvote 0
Worksheets("Sheet2").Activate
Set Rng = Range("C2:C5"),,,, "defined on Sheet2!!

Thanks again for all your assistance. Jim
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top