Click on a cell to link to a cell on a different sheet with same string

[Lorebass]

With Exel 2003:

Sheet 1

A
B
C

<TBODY>
</TBODY>

Sheet 2

	C
	B
	A

<TBODY>
</TBODY>

I want to be able to click on a cell in sheet 1 and get linked directly to a cell with the exact same string in sheet 2. I have just been asked to do this for my boss and I havent touched VB in years! there are thousands of rows in either sheet on what I am working with so I know I cant do it manually. Sorry but I need help and I don't have the time to study for it!

 

[Smitty]

Welcome to the Board!

I don't understand your screen shots, but do you want it so if you click on cell A1 in the destination sheet, then it should populate with cell A1's value from the source sheet?

 

[Lorebass]

Sorry, working on only 2 cylinders, sick at work!

The idea is if I click on A1('A') in Sheet 1
it brings me to cell C2('A') in Sheet 2
because they both have the same data in the cell: "A".

the information is only to be searched in a specific column in each sheet.
I dont really know anything about VB and the boss wants it done now... Is there any other information you'd need?

 

[Smitty]

OK, so what's the search range on sheet 2? And what if you have duplicate information?

 

[Lorebass]

There wont be duplicate information because of the way the sheet has been created. The range on this in particular is just a single column thats 25,000 rows tall. If I am on worksheet 1 and want to see the information of method 'A', I click that cell and am brought to the row in sheet 2 where 'A' exists. worksheet 2 is a reference sheet we use that has information about methods in programs listed in worksheet 1.

 

[Smitty]

See if this does what you want:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> FirstAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wsSource <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsSource = Sheets("Sheet2")<br>    <br>    <SPAN style="color:#00007F">With</SPAN> wsSource.Cells<br>        <SPAN style="color:#00007F">Set</SPAN> c = .Find(Target.Value, LookIn:=xlValues)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            FirstAddress = c.Address<br>            <SPAN style="color:#00007F">Do</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> wsSource<br>                    .Activate<br>                    .Range(c.Address).Select<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> c = .FindNext(c)<br>            <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And c.Address <> FirstAddress<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

You'll need to change the reference to the second sheet as needed.