Home>

The Range object from which i am finding exists, but you get an "Index is not in scope" error.
Please tell me the cause.

public sub sub1 (filePath as string)
On Error GoTo ErrProc

'Checked if the book and sheet below exist
Dim wb As Workbook
Set wb = Workbooks.Open (filePath, False, True)

Dim st As Worksheet
Set st = wb.Worksheets ("xx")

Dim rng As Range
Set rng = st.Cells.Find ("xxx",, xlValue, xlWhole)
↑ Here, "Index is not in the valid range" error occurs

'The following processing is omitted.

ErrProc:
Abbreviation
End Sub

I searched a lot, but the "index is not in the valid range" error is because there is no object
Although it is written, it exists even if you look at st on the watch.

  • Answer # 1

    Try xlValues ​​instead of xlValue

    Set rng = st.Cells.Find ("xxx",, xlValues, xlWhole)

    Range.Find method (Excel)

    LookIn Optional Variant You can use XlFindLookIn constants: either xlFormulas, xlValues, xlComments or xlCommentsThreaded.

  • Answer # 2

    × xlValue
    ○ xlValues

  • Answer # 3

    The question code has an extra comma, so remove it.

    Set rng = st.Cells.Find ("xxx", xlValue, xlWhole)