正在尝试使用宏在Excel中动态获取位置

Question

我试过这个宏。

'
' Findthelocation Macro
'
'
ActiveSheet.Paste
Selection.Copy
Range("K5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="Inputs!R9C3"
End Sub

细胞K5有不同的值，宏复制，粘贴到Goto栏。所以我希望有动态定位(无论是什么宏复制并粘贴到Goto栏(，而不是有"输入！R9C3">。所以无论宏复制什么，它都会粘贴到Goto栏中并指向特定的位置。有人能帮忙解决这个问题吗？

感谢您的阅读和投入时间。

Answer 1

您可以在K5中获取位置值，然后在R1C1样式中获取其地址，因此它将在Goto命令中工作。

Range("K5").Value = "Inputs!$C$15" 'location example
v = Range("K5").Value 'get location value, you could set Goto bar with this value
x = Range(v).Address(, , xlR1C1) 'get location address in R1C1 style
Application.Goto Reference:=x 'select location

Answer 2

如果单元格K5中的值是单元格引用或命名范围名称，则可以这样使用：

Range(Range("K5").Value).Select

(如果您的参考资料在不同的工作表上，有时会出现问题，在这种情况下，您需要首先Activate工作表(

也就是说，最好尽可能避免在Excel VBA中使用Select。

---

由于这似乎具有；不同的工作表"；问题，这里有一个扩展的子程序来查找&激活工作簿&工作表：

Sub GoToAddressFromCellValue(InputCell AS Range)
Dim TextToSplit AS String, WhereToSplit AS Long
Dim wbTarget AS String, wsTarget As String, rngTarget As String

TextToSplit = InputCell.Value 'Retrieve the address

WhereToSplit = InStr(TextToSplit, "]")
If WhereToSplit>0 Then 'Workbook was specified
wbTarget = Left(TextToSplit, WhereToSplit)
TextToSplit = Mid(TextToSplit, WhereToSplit + 1)
Else 'Workbook was not specified
wbTarget = InputCell.Worksheet.Parent.Name
End If

WhereToSplit = InStr(TextToSplit, "!")
If WhereToSplit>0 Then 'Worksheet was specified
wsTarget = Left(TextToSplit, WhereToSplit-1)
TextToSplit = Mid(TextToSplit, WhereToSplit + 1)
Else 'Worksheet was not specified
wsTarget = InputCell.Worksheet.Name
End If

rngTarget = TextToSplit

With Workbooks(wbTarget)
.Activte
With .Worksheets(wsTarget)
.Activate
With .Range(rngTarget)
.Select
.Show
End With
End With
End With
End Sub