Reading A Worksheet Range To A VBA Array, get script out of range error

Question

I am tiring alter a macro by seting a range as my array

This works fine

Sub FindReplaceByArrays()

Dim FindValues As Variant
Dim ReplaceValues As Variant
Dim i As Long

FindValues = Array("Find1", "Find2", "Find3")
ReplaceValues = Array("Replace1", "Replace2", "Replace3")

Sheets("UnPivot").Select
For i = LBound(FindValues) To UBound(FindValues)
     Columns("P:P").Replace FindValues(i), ReplaceValues(i), xlWhole, xlByColumns, False
Next i

End Sub

Tiring to change to the following Sub but get error "Script out of range` and

Columns("P:P").Replace FindValues(i), ReplaceValues(i), xlWhole, xlByColumns, False

is high lighted

Thanks

Sub FindReplaceByArrays2()

Dim FindValues() As Variant
Dim ReplaceValues() As Variant
Dim i As Long

Sheets("UnPivot").Select
FindValues = Range("S2:S30")
ReplaceValues = Range("T2:T30")

For i = LBound(FindValues) To UBound(FindValues)
     Columns("P:P").Replace FindValues(i), ReplaceValues(i), xlWhole, xlByColumns, False
Next i

End Sub

score 1 · Accepted Answer · edited May 23 '17 at 11:49

Try this one:

Sub FindReplaceByArrays2()

    Dim FindValues As Variant
    Dim ReplaceValues As Variant
    Dim i As Long

    With Sheets("UnPivot")
        FindValues = .Range("S2:S30").Value
        ReplaceValues = .Range("T2:T30").Value

        For i = LBound(FindValues) To UBound(FindValues)
             .Columns("P:P").Replace FindValues(i, 1), ReplaceValues(i, 1), xlWhole, xlByColumns, False
        Next i
    End With
End Sub

Note, that I'm using

Dim FindValues As Variant instead Dim FindValues() As Variant
FindValues = .Range("S2:S30").Value - with .Value property (you can read about why you should use it here: Why am I having issues assigning a Range to an Array of Variants)
also FindValues = .Range("S2:S30").Value actually creates 2D array, that's why I'm using FindValues(i, 1). The same for ReplaceValues(i, 1)

and also: How to avoid using Select/Active statements:)

Thanks again simoco, that worked, also thank you for the link it looks helpful. — xyz, Apr 02 '14 at 14:20
The edit was helpful in following what you did, thanks again. — xyz, Apr 02 '14 at 14:23

Reading A Worksheet Range To A VBA Array, get script out of range error

1 Answers1