查找方法,无需选择或激活 [英] Find method without selecting or activating
问题描述
我是研究员.Matlab很酷,R也很酷,但是现在是时候使用VBA Excel了.我想在"Sheet2"(列A)中找到一个存储在"Sheet1"(单元格A1)中的值.我可以使用选择"并从一张纸跳到另一张纸来做到这一点,但是我想做到这一点而没有所有这些跳纸.我想在运行代码时不让Excel看上去狂热地运行我的代码.而且,无论我在运行宏时在Excel中激活的工作表如何,我都希望它能够运行.那可能吗?检查我的代码如下.祝大家好运!
I fellows. Matlab was cool and R as well, but now it's time to VBA Excel. I want to find in "Sheet2" (column A) one value that is stored in "Sheet1" (cell A1). I could do this using Select and jumping from one sheet to the other, but I want to do it without all this jumps. I want to run my code without having the Excel looking frenetic while the code is running. And I want it to run no matters the Worksheet that I have activated in Excel when I run the macro. Is that possible? Check my code bellow. All the best for all of you!
Sub FindName()
Dim Name As String
Dim TablePosition As Range
Name = Worksheets("Sheet1").Range("A1").Value
'If I insert here: Worksheets("Sheet2").Select
'The codes runs but just because I am telling him to move to sheet2
'Why is it not going to Sheet2 with the instruction bellow?
With Worksheets("Sheet2").Application.Range("A1", Range("A1").End(xlDown))
Set TablePosition = _
.Find(What:=Name, _
After:=Range("A1").End(xlDown), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
Searchorder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not TablePosition Is Nothing Then
Application.Goto TablePosition, True
Else
MsgBox "Name not specified."
End If
End With
End Sub
推荐答案
Worksheets("Sheet2").Application.Range("A1", Range("A1").End(xlDown))
与
Activesheet.Range("A1", Range("A1").End(xlDown))
因此,除非您在运行代码时使用Sheet2,否则它将搜索错误的工作表.
So unless you're on Sheet2 when your code runs, it will search the wrong sheet.
您应使用类似以下内容的
You should use something like:
Dim sht as Worksheet
Set sht = Worksheets("Sheet2")
With sht.Range(sht.Range("a1"), sht.Range("a1").End(xlDown))
'...
这篇关于查找方法,无需选择或激活的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!