查找方法，无需选择或激活 [英] 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屋！