vba 在单独的工作簿中返回特定值的单元格位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11563825/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Returning the Cell location of specific value in a separate Workbook
提问by dancran
To clarify. I have 1 spreadsheet I am creating the VBA in. When the Macro runs, I want the code to find a specific value in a separate WorkBook sheet and return the location of the value in the other WorkBook.
澄清。我有 1 个电子表格，我正在其中创建 VBA。当宏运行时，我希望代码在单独的 WorkBook 工作表中查找特定值并返回该值在另一个 WorkBook 中的位置。
My initial thought was to use HLookUp, however that will only return the value, not the location of the value. My next thought was to use the Find function, but I can't get it to run. The error I get is: Run-time error "438': Object doesn't support this property or method
我最初的想法是使用 HLookUp，但这只会返回值，而不是值的位置。我的下一个想法是使用 Find 函数，但我无法让它运行。我得到的错误是：运行时错误“438”：对象不支持此属性或方法
startValue = enrollBook.Sheets("Pop-FY").Range("D:Z"). _
Applications.WorksheetFunction. _
Find(What:=FYString, LookIn:=xlValues)
enrollBook is the other workbook. startValue is supposed to be the location of the found value in the other spreadsheet
enrollBook 是另一个工作簿。startValue 应该是另一个电子表格中找到的值的位置
回答by GSerg
This is a weird way of calling a function & formatting you've got there, I first even thought that code was invalid.
这是调用函数和格式化的奇怪方式，我什至首先认为该代码无效。
First, you are confusing Range.Findwith Application.WorksheetFunction.Find. You need the Rangeone, but are calling the other one.
Second, the error is because it's Application, not Applications.
Third, you will need a Set:
首先，您Range.Find对Application.WorksheetFunction.Find. 你需要Range一个，但正在调用另一个。
其次，错误是因为它是Application，而不是Applications。
第三，你需要一个Set：
Set startValue = enrollBook.Sheets("Pop-FY").Range("D:Z").Find(What:=FYString, LookIn:=xlValues)
回答by Trace
You mean something like this?
你的意思是这样的？
Dim sTest As String
Dim oRange As Range
Set oRange = Worksheets(1).Range("A1:Z10000").Find("Test", lookat:=xlPart)
MsgBox oRange.Address
I tested this, but you need to change your parameters.
我对此进行了测试，但是您需要更改参数。
