참조되는셀로 이동하는 방법이 있을까요..?(ctrl+[의 반대개념)

이름관리자를 활용하셔도 좋을듯 합니다. 이동하고자 하는 번호 cell을 선택하시고 이름상자에 이동을 입력하시면 됩니다.

첨부파일 : 21_01_09_셀이동.xlsx

안녕하세요.

아래 명령문을 사용해보시겠어요?^^

명령문을 VBA 편집기에 붙여넣기 하신 뒤, SelectCellDependents 명령문을 실행하면 해당셀을 참조하는 셀로 바로 이동합니다.

Sub SelectCellDependents()
Dim SelRange As Range
Dim sFull As String: Dim sWB As String: Dim sWS As String: Dim sRng As String
Dim WB As Workbook: Dim WS As Worksheet: Dim Rng As Range
Dim sidX As Long: Dim eidX As Long

Set SelRange = Selection

On Error GoTo Finish:

sFull = findDepend(SelRange)
sidX = 2: eidX = InStr(1, sFull, "]") - 1
sWB = Mid(sFull, sidX, eidX - sidX + 1)
sidX = eidX + 2: eidX = InStr(sidX, sFull, "!") - 1
sWS = Mid(sFull, sidX, eidX - sidX + 1)
sidX = eidX + 2: eidX = Len(sFull) - 1
sRng = Mid(sFull, sidX, eidX - sidX + 1)

Set WB = Application.Workbooks(sWB)
Set WS = WB.Worksheets(sWS)
Set Rng = WS.Range(sRng)

WB.Activate
WS.Activate
Rng.Select

Exit Sub

Finish:

End Sub

Function fullAddress(inCell As Range) As String
fullAddress = inCell.Address(External:=True)
End Function

Function findDepend(ByVal inRange As Range) As String
Dim sheetIdx As Integer
sheetIdx = Sheets(inRange.Parent.Name).Index

' If sheetIdx = Worksheets.Count Then 'vba bug workaround
' Sheets(sheetIdx - 1).Activate
' Else
' Sheets(Worksheets.Count).Activate
' End If

Dim inAddress As String, returnSelection As Range
Dim i As Long, pCount As Long, qCount As Long
Set returnSelection = Selection
inAddress = fullAddress(inRange)

Application.ScreenUpdating = False
With inRange
.ShowPrecedents
.ShowDependents
.NavigateArrow False, 1
Do Until fullAddress(ActiveCell) = inAddress
pCount = pCount + 1
.NavigateArrow False, pCount
If ActiveSheet.Name <> returnSelection.Parent.Name Then
Do
qCount = qCount + 1
.NavigateArrow False, pCount, qCount
findDepend = findDepend & fullAddress(Selection) & Chr(13)

On Error Resume Next
.NavigateArrow False, pCount, qCount + 1
Loop Until Err.Number <> 0
.NavigateArrow False, pCount + 1
Else
findDepend = findDepend & fullAddress(Selection) & Chr(13)

.NavigateArrow False, pCount + 1
End If
Loop
.Parent.ClearArrows
End With

With returnSelection
.Parent.Activate
.Select
End With

'Sheets(sheetIdx).Activate
End Function

답변이 도움이 되셨길 바랍니다.