获得“下标超出范围”来自“查找”的错误结果 [英] Getting "subscript out of range" error from "Find" result

问题描述

我想在Excel工作表中找到一个字符串。使用公式计算Excel单元格值。当我运行这段代码：

I would like to find a string in an Excel worksheet. The Excel cell values are calculated using formulas. When I run this code:

Set firstExcel = CreateObject("Excel.application")
firstExcel.Workbooks.Open "C:\Myroute\excelName.xls"
Set oSht = firstExcel.Worksheets("Input Data")
str="hello"
Set aCell = oSht.Range("A1:E15").Find(str, , xlValues)
MsgBox aCell.row

I有一个错误说：

错误：下标超出范围

代码：800A0009

Error: Subscript out of range
Code: 800A0009

推荐答案

您在 .Find 行是vbscript不能识别Excels常量，因此您需要用 -4163 替换 xlValues 。 （所有Excel常量值都可以在VBA对象浏览器中找到）

The reason you get the error message on the .Find line is that vbscript does not recognize Excels constants, so you need to replace xlValues with the number -4163. (All Excel constant values can be found in the VBA Object Browser).

另外，你写的行设置oSht = firstExcel.Worksheets（输入数据）对VB没有意义，因为 firstExcel 是Excel应用程序本身，没有 Worksheet 对象与Excel应用程序本身相关联，但在工作簿对象中。

Also, the line you wrote Set oSht = firstExcel.Worksheets("Input Data") does not make sense to VB because firstExcel is the Excel Application itself and there is no Worksheet object associated with the Excel Application itself, but there is within the Workbook object.

Set firstExcel = CreateObject("Excel.application")
Set wkb = firstExcel.Workbooks.Open("C:\Myroute\excelName.xls")
Set oSht = wkb.Worksheets("Input Data")
str="hello"
Set aCell = oSht.Range("A1:E15").Find(str,,-4163)
If Not aCell is Nothing Then MsgBox aCell.row 'because if it does not find the string, it will not return a range object

此外，您可以声明一个常量在代码的顶部，并在 .Find 语句中使用该常量。

Furthermore you could declare a constant at the top of your code and use that constant in the .Find statement.

const xlValues = -4163
.Find(str,,xlValues)

这篇关于获得“下标超出范围”来自“查找”的错误结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！