Help! Can't pass a range of cell strings into a variant array/can't reference the strings in this array - myarray(I,1) gives subscript error

[Cozkincaud]

Hi,

I’ve spent the last four hours trying to pass a range of cell strings into a variant array for use with an inner for each cell cell.replace loop.

I can't seem to

1. Pass the cell string range into my array - myArray = myRange.Value
2. Reference each string in this array - SearchValue = myArray(I, 1) I get a 'Subscript out of range' error

I’ve Googled the problem to no avail. I've also doubled checked the spelling of the named ranges I reference in the macro.

My macro takes each of a range of string cells in one worksheet and searches for them in a range of string cells in another worksheet. If there is a match the found string is replaced with a null string (“”).

Here is the macro as it presently stands.

My thanks in advance for your help and suggestions.

Sub EraseTaxCategories()

Dim WSFind As Worksheet ' worksheet with the range holding the text strings that are to be searched for
Dim WSSearch As Worksheet ' worksheet with the range of text cells that are to be searched for text strings
Dim myRange As Range ' the variable naming the worksheet range holding the text strings to be searched for
Dim mySearchRange As Range ' the variable naming the worksheet range holding the text strings to be searched
Dim myArray As Variant ' range holding the text strings that are to be search for (they are copied into an array)
Dim SearchValue As String

    Set WSFind = Worksheets("Variable Names") ' two lines to activate the sheet containing the text strings WSFind.Activate to be searched for

    Set myRange = Sheets("Variable Names").Range("TaxNames") 'Set a range variable to the range of search strings
    myArray = myRange.Value ' create an array containing the text strings that will be searched for in sheet "WSSearch". Seems to fail! I can't tell if any values are passed!

    Set WSSearch = Worksheets("Remove Extraneous Text") ' 2 lines to activate the sheet (WSSFIND) containing the WSSearch.Activate text strings that are to be searched in
    Set mySearchRange = Sheets("Remove Extraneous Text").Range("TaxPayers") 'Set a range variable to the range of text cells that are to be searched

    For Each Cell In myArray 'the text strings in the named range 'TaxNames' in sheet 'Variable Names" that are to be searched for
   
        I = 0 'Initialize a counter for the array containing the text strings that are to be searched for. First row in an array is 0 by default?
        SearchValue = myArray(I, 1) 'the string value from the named range "TaxNames" in sheet 'Variable Names' Gives 'Subscript out of range' error
       
        For Each InnerCell In mySearchRange ' Search 'Tax Names' in 'Remove Extraneous Text' for each of the cell string values in rrange "TaxPayers' in 'Variable Names' sheet
                                           
            Cell.Replace SearchValue, "", LookAt:=xlPart 'Replace any found string with a null string
       
        Next InnerCell
        
        I = I + 1 ' increment the counter so that it moves to the next row in the array 'myArray'
        
    Next Cell
   
End Sub

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block at the bottom of this post has more details. I have added the tags for you this time.

I also suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Your problem is this

I = 0 'Initialize a counter for the array containing the text strings that are to be searched for. First row in an array is 0 by default?

Not necessarily. When you read a range into an array like this ..

myArray = myRange.Value

.. it creates a 1-based array not a 0-based array.

Try starting your "I" variable at 1 instead of 0. I have not tested the rest of the code but that is the initial error.
I think that you will also have an issue with your (undeclared) variable 'Cell' as I think you are trying to use it both as a string from your array and as a Range object.

 

[Cozkincaud]

Thanks for your quick response. I very much appreciate it.
Your suggestion worked fine and I fixed the cell problem "Cell.Replace SearchValue" with InnerCell.Replace SearchValue
Thanks again

 

[Peter_SSs]

You're welcome. Glad you got it working.

BTW, please don't forget my suggestion above in paragraph 2