VBA代码未跳到单元格 [英] VBA code does not jump to cell

问题描述

嘿，我正在尝试编写跳到单元格的代码.

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

地址存储在变量中.

我尝试了2个选项，但两个选项均不适用于该变量.

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

它给了我

方法'Range'或对象'_Global'失败"错误.

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

stMsg 是在宏的第一部分中找到的变量.第一部分在一个单元格中采用一个公式，然后在公式中找到2个先例. stMsg 的值为'Sheet2'！$ C $ 8'Sheet2'！$ C $ 8

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

如何使其与x兼容?

推荐答案

x 是您的变量，并且您尝试将其用作"x" ，这是一个仅包含字母 x

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

并使用以下两个选项工作:
OP提供的输入:'Sheet2'！$ C $ 8'Sheet2'！$ C $ 8

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

并使用以下两个选项工作:
OP提供的输入:'Sheet2'！$ C $ 8

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

同时尝试两者的代码:

Sub TEST_test_tombata()

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

test_tombata2 "'Sheet2'!$C$8"

End Sub

这篇关于VBA代码未跳到单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！