Debug Macro to Populate Cell Based on Value in Array

I have a macro that assigns a specific term to a cell based on whether it finds a string in an array. I'm getting a "Subscript out of range" error in this line:

       If InStr(rngCell, FindWhat(i)) <> 0 Then

How do I fix the error?


Here is the macro:


Sub SFDCCategorizeAssetType()

        Dim FindWhat As Variant, rngCell As Range
    Dim i As Integer, ItemTypeOffset As Integer, r As Integer
     
    FindWhat = Array("datasheet", "data-sheet", "data sheet")
    For i = 0 To 14
        For Each rngCell In Range("B2", Range("B" & Rows.Count).End(xlUp))
            If InStr(rngCell, FindWhat(i)) <> 0 Then
                rngCell.Offset(0, 6) = "data-sheet"
                  End If
        Next rngCell
    Next i
   
    FindWhat = Array("brochure")
    For i = 0 To 14
        For Each rngCell In Range("B2", Range("B" & Rows.Count).End(xlUp))
            If InStr(rngCell, FindWhat(i)) <> 0 Then
                rngCell.Offset(0, 6) = "brochure"
                  End If
        Next rngCell
     Next i
     
    FindWhat = Array("applicationnote", "application-note", "application note", "appnote", "app-note")
    For i = 0 To 14
        For Each rngCell In Range("B2", Range("B" & Rows.Count).End(xlUp))
            If InStr(rngCell, FindWhat(i)) <> 0 Then
                rngCell.Offset(0, 6) = "application-note"
                  End If
        Next rngCell
    Next i
     
End Sub
calyx_terenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
You are looping 14 times, using the loop index to find an value in your FindWhat array. That array never has 14 items, so the code fails when the loop index ( I ) exceeds the number for elements in your FindWhat array.

Either change the "For I = " to a For Each loop, or use the UBound function to determine the number of elements in your FindWhat array.
Martin LissOlder than dirtCommented:
Your array only has one element so

If InStr(rngCell, FindWhat(0)) <> 0 Then

And if in fact it will always only have one element then you could get rid of the array and just do

If InStr(rngCell, FindWhat("brochure") )<> 0 Then

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
calyx_terenAuthor Commented:
Easy to implement. Thanks.
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
calyx_terenAuthor Commented:
Thanks, Martin. I have another question on a similar macro. The macro does the same thing except that the array is larger. I'm getting the same error even if i change the number in FindWhat(). The macro is also not populating the cells that it should.

Thanks for your help in advance.

Sub SFDCCategorizeGuide()


    Dim FindWhat, rngCell As Range, i As Integer
    FindWhat = Array( "booklet", "compliance-guide", "enrichment-guide", "field_guide", "fs_guide", "installation guide", "installation_guide", "installationguide", "installation-guide", "installguide", "install-guide", _
    "library-prep-guide", "operations_manual", "pm_guide", "pmguide", "procedure", "protocol", "reference guide", "reference-guide", "service guide", "service_guide", "serviceguide", "service-guide", "site-prep", _
    "software-guide", "software-guide", _
    "system_manual", "system-guide", "technical-guide", "troubleshooting", "-ug-", "user guide", "user_guide", "userguide", "user-guide", "analysis_guide", "app-guide", "compliance guide", "conversion_guide", _
    "customization_guide", "enrichment-guide", "maintenance_guide", _
    "preventive maintenance guide", "ref-guide", "safety-and-compliance-guide", "sampleprep_guide", "siteprepguide", "upgrade_guide")
    For i = 0 To 15
        For Each rngCell In Range("B2", Range("B" & Rows.Count).End(xlUp))
            If InStr(rngCell, FindWhat(65)) <> 0 Then
                rngCell.Offset(0, 6) = "guide"
                  End If
        Next rngCell
    Next i
     
End Sub
Martin LissOlder than dirtCommented:
You have only 48 entries in the array so the highest value that you can refer to is FindWhat(45).

Am I correct in assuming that you want to set the cell that is 6 columns to the right to be "guide" for all the cells that contain any of those 48 values?
calyx_terenAuthor Commented:
Yes, your assumption is correct. So, if I want to add several of these loops into one macro, then I have to update the number in FindWhat() to reflect the number of elements in each array. In other words, the number in FindWhat() should always equal the number of terms that should populate the cell 6 columns to the right with "guide" or whatever other key word is assigned.

I have lots of these loops. Is there a simpler way?
Martin LissOlder than dirtCommented:
This will go through each cell in column B starting at row two and if the value in the cell is equal to any value in two lists then column H will be updated. If this doesn't work then I'd like you to ask a new question and we can get into more details.

Sub SFDCCategorizeGuide()
    Dim FindWhat, rngCell As Range, i As Integer
    Dim lngEntry As Long
    Dim FindWhat2
    Dim bFound As Boolean
    
    FindWhat = Array("booklet", "compliance-guide", "enrichment-guide", "field_guide", "fs_guide", "installation guide", "installation_guide", "installationguide", "installation-guide", "installguide", "install-guide", _
    "library-prep-guide", "operations_manual", "pm_guide", "pmguide", "procedure", "protocol", "reference guide", "reference-guide", "service guide", "service_guide", "serviceguide", "service-guide", "site-prep", _
    "software-guide", "software-guide", _
    "system_manual", "system-guide", "technical-guide", "troubleshooting", "-ug-", "user guide", "user_guide", "userguide", "user-guide", "analysis_guide", "app-guide", "compliance guide", "conversion_guide", _
    "customization_guide", "enrichment-guide", "maintenance_guide", _
    "preventive maintenance guide", "ref-guide", "safety-and-compliance-guide", "sampleprep_guide", "siteprepguide", "upgrade_guide")
    
    FindWhat2 = Array("this", "is", "a", "test")
    
    For i = 2 To Range("B1048576").End(xlUp).Row
        bFound = False
        For lngEntry = 0 To UBound(FindWhat)
            If UCase(Cells(i, 2).Value) = UCase(FindWhat(lngEntry)) Then
                Cells(i, 8).Value = "guide"
                bFound = True
                Exit For
            End If
        Next
        If Not bFound Then
            For lngEntry = 0 To UBound(FindWhat2)
                If UCase(Cells(i, 2).Value) = UCase(FindWhat2(lngEntry)) Then
                    Cells(i, 8).Value = "blah"
                    bFound = True
                    Exit For
                End If
            Next
        End If
        ' etc.
'        If Not bFound Then
'            For lngEntry = 0 To UBound(FindWhat3)
'                If UCase(Cells(i, 2).Value) = UCase(FindWhat3(lngEntry)) Then
'                    Cells(i, 8).Value = "yada yada"
'                    bFound = True
'                    Exit For
'                End If
'            Next
'        End If

    Next
End Sub

Open in new window

calyx_terenAuthor Commented:
Column H was updated only when the macro found a match to FindWhat2. So, I got four instances of "blah" but none of "guide". I'll start a new question.
Martin LissOlder than dirtCommented:
Here's a more compact version.

Sub SFDCCategorizeGuide()
    Dim FindWhat, rngCell As Range, i As Integer
    Dim lngEntry As Long
    Dim FindWhat2
    Dim bFound As Boolean
    
    FindWhat = Array("booklet", "compliance-guide", "enrichment-guide", "field_guide", "fs_guide", "installation guide", "installation_guide", "installationguide", "installation-guide", "installguide", "install-guide", _
    "library-prep-guide", "operations_manual", "pm_guide", "pmguide", "procedure", "protocol", "reference guide", "reference-guide", "service guide", "service_guide", "serviceguide", "service-guide", "site-prep", _
    "software-guide", "software-guide", _
    "system_manual", "system-guide", "technical-guide", "troubleshooting", "-ug-", "user guide", "user_guide", "userguide", "user-guide", "analysis_guide", "app-guide", "compliance guide", "conversion_guide", _
    "customization_guide", "enrichment-guide", "maintenance_guide", _
    "preventive maintenance guide", "ref-guide", "safety-and-compliance-guide", "sampleprep_guide", "siteprepguide", "upgrade_guide")
    
    FindWhat2 = Array("this", "is", "a", "test")
    
    For i = 2 To Range("B1048576").End(xlUp).Row
       
        bFound = LookUp(FindWhat, "guide", Cells(i, 2))
        If Not bFound Then
            bFound = LookUp(FindWhat2, "blah", Cells(i, 2))
        End If
        ' etc.
'        If Not bFound Then
'            bFound = LookUp(FindWhat3, "yada yads", Cells(1, 2))
'        End If
    Next
End Sub

Open in new window

Private Function LookUp(Arr As Variant, Category As String, cel As Range) As Boolean

    Dim lngEntry As Long
    
    For lngEntry = 0 To UBound(Arr)
        If UCase(cel.Value) = UCase(Arr(lngEntry)) Then
            cel.Offset(0, 6).Value = Category
            LookUp = True
            Exit Function
        End If
    Next

End Function

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.