Trying to get location dynamic in Excel using Macro

Question

I tried this macro.

'
' 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

Cell K5 is having different values which Macro copies, paste it into Goto bar. So I would like to have dynamic location (whatever macro copy and paste in to Goto bar) rather having "Inputs!R9C3". So whatever Macro copies, it paste into Goto bar and point towards that particular location. Can any one please help solving this?

Thanks for reading and putting your time.

Chronocidal · Answer 1 · 2020-10-22T08:45:09.707

If the value in cell K5 is a cell reference or Named Range name, you can use that like this:

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

(This sometimes has issues if your reference is on a different worksheet, in which case you will need to Activate the Worksheet first)

That said, it is best to try to Avoid using Select in Excel VBA where possible.

Since this seems to have the "different worksheet" issues, here is an extended subroutine to find & activate the Workbook & Worksheet:

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

Thanks for your input. But this did not work. The value in K5 is the location generated by formula. For example, the value in K5 is '[Presented.xlsm]Inputs'!$C$9. I am currently in '[Presented.xlsm]Dash' work sheet. What I want macro to do is to pick up the location address in cell K5 and paste that in to Goto bar. It is now Input C9, may be next time it is Input C55 or Input C200. — Chris Smith, Oct 21 '20 at 23:21
@ChrisSmith I have added a function to show how to identify when the Address provided specifies a different Workbook and/or Worksheet — Chronocidal, Oct 22 '20 at 08:45

score 0 · Answer 2 · answered Oct 22 '20 at 00:36

You could get the location value in K5 and then get its address in R1C1 style, so it will work in the Goto command.

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

Trying to get location dynamic in Excel using Macro

2 Answers2