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

 

[Special-K99]

This same error occurs when a specified worksheet does not exist

 

[101dell]

I've made sure that they both exist and that they are named correctly.

 

[Norie]

strArray is 2-dimensional.

 

[101dell]

strArray is 2-dimensional.

Would making it 3D fix it I assume?
Thanks

 

[Norie]

No, what would fix it would be to refer to both dimensions when trying to access the items in the array.

For example.

        For J = LBound(strArray) To UBound(strArray)
            Found = Found Or Not (rngCells.Find(strArray(J, 1)) Is Nothing)
        Next J

 

[dmt32]

Hi,
not fully tested & bit of a guess but see if this comes close to what you are trying to do

Private Sub CommandButton1_Click()
    Dim strArray As Variant
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim NoRows As Long, DestNoRows As Long
    Dim J As Integer
    Dim rngCells As Range, Found As Range
    Dim FirstAddress As String
    
'2D array
    strArray = Sheets("Sheet2").Range("A1:A10").Value
    
'set source sheet
    Set wsSource = Sheet1
'no of used rows
    NoRows = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
'row counter
    DestNoRows = 1
'search range
    Set rngCells = wsSource.Range("H1").CurrentRegion
    
    For J = 1 To UBound(strArray, 1)
        If Len(strArray(J, 1)) > 0 Then
'start search
            Set Found = rngCells.Find(strArray(J, 1), LookIn:=xlValues, lookat:=xlWhole)
            If Not Found Is Nothing Then
'add new worksheet
                If wsDest Is Nothing Then Set wsDest = ActiveWorkbook.Worksheets.Add
'mark start address
                FirstAddress = Found.Address
                Do
'copy found record
                    Found.EntireRow.Copy wsDest.Range("A" & DestNoRows)
'increment counter
                    DestNoRows = DestNoRows + 1
'find next record match
                    Set Found = rngCells.FindNext(Found)
'bail out if no further match
                    If Found Is Nothing Then Exit Do
'or get back to start address
                Loop While Found.Address <> FirstAddress
            End If
        End If
    Set Found = Nothing
    Next J
End Sub

I have used Range.FindNext Method to search for additional matches.

As already pointed out, when you read a range into an array it's 2D with lower bound of 1.

Hope helpful but adjust code as required.

Dave

 

[101dell]

Hi,
not fully tested & bit of a guess but see if this comes close to what you are trying to do

Private Sub CommandButton1_Click()
    Dim strArray As Variant
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim NoRows As Long, DestNoRows As Long
    Dim J As Integer
    Dim rngCells As Range, Found As Range
    Dim FirstAddress As String
    
'2D array
    strArray = Sheets("Sheet2").Range("A1:A10").Value
    
'set source sheet
    Set wsSource = Sheet1
'no of used rows
    NoRows = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
'row counter
    DestNoRows = 1
'search range
    Set rngCells = wsSource.Range("H1").CurrentRegion
    
    For J = 1 To UBound(strArray, 1)
        If Len(strArray(J, 1)) > 0 Then
'start search
            Set Found = rngCells.Find(strArray(J, 1), LookIn:=xlValues, lookat:=xlWhole)
            If Not Found Is Nothing Then
'add new worksheet
                If wsDest Is Nothing Then Set wsDest = ActiveWorkbook.Worksheets.Add
'mark start address
                FirstAddress = Found.Address
                Do
'copy found record
                    Found.EntireRow.Copy wsDest.Range("A" & DestNoRows)
'increment counter
                    DestNoRows = DestNoRows + 1
'find next record match
                    Set Found = rngCells.FindNext(Found)
'bail out if no further match
                    If Found Is Nothing Then Exit Do
'or get back to start address
                Loop While Found.Address <> FirstAddress
            End If
        End If
    Set Found = Nothing
    Next J
End Sub

I have used Range.FindNext Method to search for additional matches.

As already pointed out, when you read a range into an array it's 2D with lower bound of 1.

Hope helpful but adjust code as required.

Dave

Thanks for the help Dave, it kind of works, for some reason it only shows 1 match, in some cases it doesn't show anything even though I know the keywords exist. What have I missed?

 

[dmt32]

Thanks for the help Dave, it kind of works, for some reason it only shows 1 match, in some cases it doesn't show anything even though I know the keywords exist. What have I missed?

as always, provided solutions are, without seeing copy of workbook, a bit of a guess

Try adding MatchCase parameter to the Find function & see if that helps.

Set Found = rngCells.Find(strArray(J, 1), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)

Dave

 

[101dell]

as always, provided solutions are, without seeing copy of workbook, a bit of a guess

Try adding MatchCase parameter to the Find function & see if that helps.

Set Found = rngCells.Find(strArray(J, 1), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)

Dave

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.