0

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))
tulanejosh
  • 317
  • 2
  • 6
  • 18
  • 1
    when assigning a range directly to an array it starts at 1 not 0 – Scott Craner Jun 06 '16 at 14:34
  • So the safest method would be `For i = LBound(fndArr) To UBound(fndArr)` – Scott Craner Jun 06 '16 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 Jun 06 '16 at 15:03
  • What is the value of i when the error is thrown? Are both columns the same length? – Scott Craner Jun 06 '16 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 Jun 06 '16 at 15:07
  • Basically forcing the Array(sh1.range....) starts i at 0. However, it still appears to be not looping through. – tulanejosh Jun 06 '16 at 15:11
  • They should be: `fndArr = sh1.Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value` add the `.Value` to the end. As to why it is not working, Check your data for extra spaces and other non printable characters that would be causing them not to be found. – Scott Craner Jun 06 '16 at 15:15

1 Answers1

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
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • @tulanejosh You also need to get rid of the Selection stuff, but that is a different issue from your error message – Ron Rosenfeld Jun 06 '16 at 15:21
  • Why do I need to get rid of the Selection stuff? – tulanejosh Jun 06 '16 at 15:40
  • 1
    @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](http://stackoverflow.com/questions/10714251/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. – Ron Rosenfeld Jun 06 '16 at 16:18