"Subscript out of range" error when looping through Array to Find/Replace in a column

Question

I have two lists of names on an excel sheet (sh1 - Column A, Column D). On another excel sheet (sh2 - Column B), I have a another list of names. I am trying to find/replace Column A names on Column B with Column D.

My full code is listed at the bottom. I am getting a "Subscript out of range" error on this line:

Selection.Replace What:=fndArr(i), Replacement:=rplArr(i), LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
    ReplaceFormat:=False

I have checked the range and it seems like the code should loop through every cell in sh2 Column B looking for Column A data and replacing it with Column D data.

I'm drawing a total blank on this. Does anyone know why I am getting this error? Thank you for your help.

Column A       |   Column B   |  Column D
--------------------------------------------
Hugh Jackman   |  Hugh J      |  Hugh Jackman
Ronald Reagan  |  Ronald R    |  Ronald Reagan
John Adams     |  John A      |  John Adams

.

Sub CheckReplace()

Dim sh1 As Worksheet, sh2 As Worksheet
Dim fndArr() As Variant
Dim rplArr() As Variant

Set sh1 = Sheets("CA")
Set sh2 = Sheets("FD")

''' turn off screen updating '''
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

''' Find/Replace CA '''
sh1.Activate
fndArr = sh1.Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
rplArr = sh1.Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)

sh2.Activate
Columns("B").Select

For i = 0 To UBound(fndArr)
    Selection.EntireColumn.Select
    Selection.Replace What:=fndArr(i), Replacement:=rplArr(i), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
Next i

''' turn on screen updating '''
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

EDIT:

The following change in code allows it to run without throwing an error, but then it find/replaces the first values in Column A/B, e.g. Hugh Jackman Hugh J, but not Ronald Reagan, Ronald R:

fndArr = Array(sh1.Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row))
rplArr = Array(sh1.Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row))

when assigning a range directly to an array it starts at 1 not 0 — Scott Craner
– Scott Craner, Commented Jun 6, 2016 at 14:34
So the safest method would be For i = LBound(fndArr) To UBound(fndArr) — Scott Craner
– Scott Craner, Commented Jun 6, 2016 at 15:00
I did 'For i = LBound(fndArr) To UBound(fndArr)' as you typed that up and i'm still getting the same error. — tulanejosh
– tulanejosh, Commented Jun 6, 2016 at 15:03
What is the value of i when the error is thrown? Are both columns the same length? — Scott Craner
– Scott Craner, Commented Jun 6, 2016 at 15:04
Both columns are the same length. the Value of i = 1 when error is thrown. Check out my edit above, I changed the array variables and the code runs without throwing an error, but it only find/replaces the first value in the array. — tulanejosh
– tulanejosh, Commented Jun 6, 2016 at 15:07

Ron Rosenfeld · Accepted Answer · 2016-06-06 15:26:20Z

2

When you equate an array to a range, you create a 1-based 2D array, even with a single column, so:

For i = 1 To UBound(fndArr)
    Selection.EntireColumn.Select
    Selection.Replace What:=fndArr(I,1), Replacement:=rplArr(I,1), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
Next I

EDIT: You also need to get rid of the selection stuff. Perhaps the following (not tested)

'Delete these two lines
'sh2.Activate   
'Columns("B").Select

With sh2.Columns("B") 
For i = 0 To UBound(fndArr)
    .Replace What:=fndArr(I,1), Replacement:=rplArr(I,1), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
Next i

edited Jun 6, 2016 at 15:26

answered Jun 6, 2016 at 15:16

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

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

3 Comments

Ron Rosenfeld Over a year ago

@tulanejosh You also need to get rid of the Selection stuff, but that is a different issue from your error message

tulanejosh Over a year ago

Why do I need to get rid of the Selection stuff?

Ron Rosenfeld Over a year ago

@tulanejosh It slows down your code. It is usually the main cause of runtime errors. See especially Siddharth Rout's answer in the thread on How to avoid using Select in Excel VBA Macros In your code in particular, you are selecting the entire column each time you go through the loop. There is no need for that.

Collectives™ on Stack Overflow

"Subscript out of range" error when looping through Array to Find/Replace in a column

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related