将一个单元格链接到同一工作表中的另一个单元格Excel
问题描述:
我想要实现的是,当我单击包含文本的单元格时,我希望将视图转换为单元格链接/连接的位置。将一个单元格链接到同一工作表中的另一个单元格Excel
例如,让我们说我有50列。如果我要移动到列号48,35或40的右侧是非常耗时的。因此,我想要一个单元格或者一个按钮,可以将我导航到相应的列。
我试过搜索,但也许该函数被命名为别的东西..无论如何,我希望有人可以给我一个线索,我如何做到这一点,或信息的位置。
答
这不是真的,你是在谈论一个链接,但如果影响此代码到一个按钮,它应该包括你的需求:
Sub test_navi()
Dim ColNb As Integer
ColNb = InputBox("Type the number of the column you want to go to :", "Navigation", "38")
If ColNb > 0 Then
ActiveSheet.Cells(2, ColNb).Select
Else
End If
End Sub
+0
这很好,但我想将按钮连接到位置,而不需要输入哪个地方也要移动。我希望用户单击该按钮并将其引导到正确的位置,而无需任何文本输入。 –
答
假设Cell A1
包含您想要移动列的数量,您可以使用:
col = CLng(Range("A1").Value)
ActiveWindow.ScrollColumn = col
答
继“细胞”的想法,你可以使用工作表Worksheet_Change
事件
,让您在单元格中键入所需的列索引‘A1’(但它可以是任何你喜欢的细胞),并让它‘自动地’选择所选列
发生在工作表代码窗格下面的代码:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim val As Long
Dim strng As String
Dim mixed As Boolean
If Target.Address(False, False) = "A1" Then '<~~ just change "A1" with whatever cell address you want to type column index in
If IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
GetValAndNumber Target, val, strng, mixed '"interpret" the value input in cell A1
On Error Resume Next 'prevent possible invalid inputs could error subsequent 'Range' methods calls
If mixed Then
Range(Target.Value).Select
Else
Cells(1, Target.Value).Select
End If
On Error GoTo 0
Application.EnableEvents = True
End If
End Sub
Sub GetValAndNumber(cell As Range, valLng As Long, strng As String, mixed As Boolean)
Dim valStrng As String, char As String
Dim i As Long
With cell
For i = 1 To Len(.Value2)
char = Mid(.Value2, i, 1)
If char Like "[0-9]" Then
valStrng = valStrng & char
If strng <> "" Then Exit For
Else
strng = strng & char
If valStrng <> "" Then Exit For
End If
Next i
End With
mixed = strng <> "" And valStrng <> ""
If strng <> "" Then
mixed = valStrng <> ""
If mixed Then valLng = CLng(valStrng)
Else
If valStrng <> "" Then valLng = CLng(valStrng)
End If
End Sub
答
也许这些代码两位 - 作品,如果你的只有一个先例。
Public Sub FindPrecedent()
Dim StartCell As Range
Set StartCell = ActiveCell
StartCell.ShowPrecedents
StartCell.NavigateArrow TowardPrecedent:=True, ArrowNumber:=1, LinkNumber:=1
StartCell.Parent.ClearArrows
End Sub
Public Sub FindDependent()
Dim StartCell As Range
Set StartCell = ActiveCell
StartCell.ShowDependents
StartCell.NavigateArrow TowardPrecedent:=False, ArrowNumber:=1, LinkNumber:=1
StartCell.Parent.ClearArrows
End Sub
您可以随时点击'Trace Precedents'按钮并双击它绘制的线条 - 它将带您进入两个单元格之间。 –