excel 链接到指定数据

[blueice0120]

表一
A1 A2
1 01
2 02
表二
A1 A2 A3
1 01 100
2 01 15
3 02 25
4 02 63

要求：在表一中A2列建立超链接，点击后直接定位到表二中与表一关联的数据行

[wuyuchang]

学习~

[cnedge]

学习，留名。

[laoyebin]

sheet2里加个selectionchange事件

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If Len(Target.Value) = 0 Then Exit Sub
Dim findResult
Dim lRow&
Set findResult = Sheet1.Range("a:a").Find(Cells(Target.Row, 1).Value, , xlValues, xlWhole)
If findResult Is Nothing Then Exit Sub
lRow = findResult.Row
Sheet1.Activate
Sheet1.Rows(lRow).Select
End Sub

[chenhao1988]

学习~

[huajinok]

不明白

[chenhao1988]

我还是弄错了，呵呵，改了laoyebin的例子，但是还是从sheet2链接到sheet1的，再改。
A1列关联，sheet1里加个selectionchange事件

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Len(Target.Value) = 0 Then Exit Sub
Dim findResult
Dim lRow&
Set findResult = Sheet2.Range("a:a").Find(Cells(Target.Row, 1).Text, , xlValues, xlWhole)
If findResult Is Nothing Then Exit Sub
lRow = findResult.Row
Sheet2.Activate
Sheet2.Rows(lRow).Select
End Sub
A2关联方法类似，呵呵。再次谢谢laoyebin！

[chenhao1988]

[Quote=引用 1 楼 laoyebin 的回复:]
sheet2里加个selectionchange事件

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column <> 2 Then Exit Sub
    If Target.Row = 1 Then Exit Sub
    If Len(Target.Value) = 0 Then Exit Sub
    Dim findResult
    Dim lRow&
    Set findResult = Sheet1.Range("a:a").Find(Cells(Target.Row, 1).Value, , xlValues, xlWhole)
    If findResult Is Nothing Then Exit Sub
    lRow = findResult.Row
    Sheet1.Activate
    Sheet1.Rows(lRow).Select
End Sub
[/Quote]
看了很久，终于有些明白了，不过你好像理解错了楼主的意思，这个selectionchange事件应该加在表一上。
楼主也没说明白如何关联，是转到相同行呢(A1列相同)？还是转到A2列的值相同的行中？

[laoyebin]

[Quote=引用 5 楼 chenhao1988 的回复:]
引用 1 楼 laoyebin 的回复:
sheet2里加个selectionchange事件

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column <> 2 Then Exit Sub
    If Target.Row = 1 Then Exit Sub
    If Len(Target.Value) = 0 Then Exit Sub
    Dim findResult
    Dim lRow&
    Set findResult = Sheet1.Range("a:a").Find(Cells(Target.Row, 1).Value, , xlValues, xlWhole)
    If findResult Is Nothing Then Exit Sub
    lRow = findResult.Row
    Sheet1.Activate
    Sheet1.Rows(lRow).Select
End Sub

看了很久，终于有些明白了，不过你好像理解错了楼主的意思，这个selectionchange事件应该加在表一上。
楼主也没说明白如何关联，是转到相同行呢(A1列相同)？还是转到A2列的值相同的行中？
[/Quote]

哦，嗯，确实是写反了

[chenhao1988]

[Quote=引用 6 楼 laoyebin 的回复:]
[/Quote]
参考你的，我修改了如下：
A2列关联
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Len(Target.Value) = 0 Then Exit Sub
Dim findResult
Dim lRow&
Set findResult = Sheet1.Range("b:b").Find(Target.Text, , xlValues, xlWhole)
If findResult Is Nothing Then Exit Sub
lRow = findResult.Row
Sheet1.Activate
Sheet1.Rows(lRow).Select
End Sub
A1列关联
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Len(Target.Value) = 0 Then Exit Sub
Dim findResult
Dim lRow&
Set findResult = Sheet1.Range("a:a").Find(Cells(Target.Row, 1).Text, , xlValues, xlWhole)
If findResult Is Nothing Then Exit Sub
lRow = findResult.Row
Sheet1.Activate
Sheet1.Rows(lRow).Select
End Sub
第一行是表头还要加上If Target.Row = 1 Then Exit Sub 参考laoyebin