VBA code does not jump to cell

Question

Hy I am trying to make a code that jumps to a cell.

The address is stored in a variable.

I tried 2 options, but both do not work with the variable.

They work fine with the concrete cell address.

Dim stMsg As String
Dim x As String

 x = Left(stMsg, Len(stMsg) / 2) 'x = 'Sheet2'!$C$8

    '1 Option
    Application.Goto Reference:="x" 'this works with format: Sheet2!R8C3

    '2 Option
    With Range("x")  'this works with format: 'Sheet2'!$C$8 , which is exactly x
    .Parent.Activate
    .Activate
    End With

It gives me

Method 'Range' or object'_Global' failed" error.

stMsg is a variable found in the first part of the macro. The first part take a formula in a cell and find the precedents in the formula, which are 2. stMsg stores them both, that's why I split it with x. The value of stMsg is 'Sheet2'!$C$8'Sheet2'!$C$8

How can I make it work with x?

Answer 1

xis your variable and you are trying to use it as "x", which is a text only containing the letter x

So your code should be more like this :

Dim x As String
x = Left(stMsg, Len(stMsg) / 2)

'''Option 1
Application.Goto Reference:=x

'''Option 2
With Range(x)
    .Parent.Activate
    .Activate
End With

And working for both options with :
Input provided by OP : 'Sheet2'!$C$8'Sheet2'!$C$8

Sub test_tombata(stMsg As String)

Dim x As String
x = Left(stMsg, Len(stMsg) / 2) 'x = "'Sheet2'!$C$8"
'Debug.Print x

'''Option 1
Application.Goto Reference:=Sheets(Replace(Split(x, "!")(0), "'", vbNullString)).Range(Split(x, "!")(1))

'''Option 2
With Range(x)
    .Parent.Activate
    .Activate
End With

End Sub

And working for both options with :
Input provided by OP : 'Sheet2'!$C$8

Sub test_tombata2(x As String)

'''Option 1
Application.Goto Reference:=Sheets(Replace(Split(x, "!")(0), "'", vbNullString)).Range(Split(x, "!")(1))

'''Option 2
With Range(x)
    .Parent.Activate
    .Activate
End With

End Sub

Code to try both :

Sub TEST_test_tombata()

test_tombata "'Sheet2'!$C$8'Sheet2'!$C$8"

test_tombata2 "'Sheet2'!$C$8"

End Sub

Answer 2

Hope below is whats you, by assuming K9 is contains the target range:

Dim rw, col As integer
rw = Range(Range("K9").Value).Row
col = Range(Range("K9").Value).Column
Application.Goto ActiveSheet.Cells(rw + 0, col - 0)