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

Community · Accepted Answer · 2017-05-23 11:49:53Z

1

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:)

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Apr 2, 2014 at 14:06

Dmitry Pavliv

35.9k13 gold badges85 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

xyz Over a year ago

Thanks again simoco, that worked, also thank you for the link it looks helpful.

xyz Over a year ago

The edit was helpful in following what you did, thanks again.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related