Run time error '9' Subscript out of range, array

[101dell]

Hi all, so I'm new to excel programming and I've run into an issue, this forum has helped me with other projects in the past but now i'm stuck.

For context; i'm creating a macro that will take the rows from one sheet that contain key words (that can be changed by the user and are written in the cells of another sheet) and pasting them to a new sheet. However the following code throws the Run time error '9' Subscript out of range error. I'm sure it's something silly, but I don't understand it well enough to identify the issue. The debugger highlights "Found = Found Or Not (rngCells.Find(strArray(J)) Is Nothing)" as the issue. I'm fully aware this isn't an elegant solution to the problem either, but any help would be appreciated.

Private Sub CommandButton1_Click()


Dim strArray As Variant
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim NoRows As Long
Dim DestNoRows As Long
Dim I As Long
Dim J As Integer
Dim rngCells As Range
Dim rngFind As Range
Dim Found As Boolean
    
    strArray = Sheets("Sheet2").Range("A1:A10").Value
    
    
    Set wsSource = Sheet1
    
    NoRows = wsSource.Range("A1000").End(xlUp).Row
    DestNoRows = 1
    Set wsDest = ActiveWorkbook.Worksheets.Add
        
    For I = 1 To NoRows
    
        Set rngCells = wsSource.Range("H" & I & ":V" & I)
        Found = False
        For J = 0 To UBound(strArray)
            Found = Found Or Not (rngCells.Find(strArray(J)) Is Nothing)
        Next J
        
        If Found Then
            rngCells.EntireRow.Copy wsDest.Range("A" & DestNoRows)
            
            DestNoRows = DestNoRows + 1
        End If
    Next I
End Sub

 

[dmt32]

I understand that, and I appreciate the help.

I tried your suggestion, to no avail. I think what is happening is that it stops looking after it finds the first result. If you don't mind me asking, could you explain where this code searches. It's changed a fair bit from my original and i'm not entirely confident I understand how and what it's doing. I initially defined which columns to search for the key words in, but now i don't get what it's looking at.

Many thanks.

Hi,
search range is set by this line of code

    Set rngCells = wsSource.Range("H1").CurrentRegion

Starting from H1, the current region is a range bounded by any combination of blank rows and blank columns. So if there is a blank column or row in amongst the required search range, then this property will not return the whole range you require.

Dave