Find/Replace All Macro - Issue

[Gater]

Hello all! I've been trying to get this macro/script to run correctly for a bit now, and it does...sorta. Found this site and thought I would ask the masters on here for help (much appreciated in advance).

I have a table of data. Column 1 and Column 2. I currently have this data in a reference worksheet (which I have called Data below) in the workbook. What I'm trying to do is run a macro that finds cell data (Column 1) and replaces it with what is located adjacent to it (Column 2) in the workbook (minus my data column sheet).

What is happening is it replaces the data and works, but after about the 13th row it replaces, it starts adding 01 onto the end of things or 0101C, etc. I'm not sure what is causing this. I usually do java/html/css/php and web-based coding, I'm not extremely familiar with excel and the VB commands/functions, but I should be able to understand the code upon seeing it. I'll post the script I have now and see if maybe I've done something wrong. I'm assuming it has something to do with the looping of the array(s) or how it composes the array from the table (not sure really).

---

 'Find/Replace All In Workbook From Table



Sub FR_All()


Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant


'Variable -> Data Table


    Set tbl = Worksheets("Data").ListObjects("Table1")


'Data Table -> Array


    Set TempArray = tbl.DataBodyRange
    myArray = Application.Transpose(TempArray)
  
'Find/Replace (Column Definitions)


    fndList = 1
    rplcList = 2


'Array List Loop


    For x = LBound(myArray, 1) To UBound(myArray, 2)
  
'Loop Each Worksheet (minus Data) & Change Entries


    For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> tbl.Parent.Name Then
            sht.Cells.Replace what:=myArray(1, x), Replacement:=myArray(2, x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
    End If
        Next sht


    Next x


End Sub

---

This actually is for a manager at work that requires this or I would just say "Just use the find and replace all and change them." Any help is appreciated, thanks guys!

 

[sijpie]

You have mixed up the dimensions of the array, lerting X go from LBound(Array,1) to UBound(Array,2)
So it takes the lowerbound of the 1st dim and the upperbound of the2nd dimension

I have further refined your code a bit. haven't tested it but it should work

<font face=Calibri><br><SPAN style="color:#00007F">Sub</SPAN> FR_All()<br><br><br><SPAN style="color:#00007F">Dim</SPAN> sht <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> fndList <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> rplcList <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> tbl <SPAN style="color:#00007F">As</SPAN> ListObject<br><SPAN style="color:#00007F">Dim</SPAN> myArray <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> lR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>    <SPAN style="color:#007F00">'Variable -> Data Table</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> tbl = Worksheets("Data").ListObjects("Table1")<br><br><br>    <SPAN style="color:#007F00">'Data Table -> Array  (rows in 1st dim, cols in 2nd dim)</SPAN><br>    myArray = tbl.DataBodyRange.Value<br>    <br>  <br>    <SPAN style="color:#007F00">'Find/Replace (Column Definitions)</SPAN><br>    fndList = 1<br>    rplcList = 2<br><br><br>    <SPAN style="color:#007F00">'Array List Loop through rows</SPAN><br><br>    <SPAN style="color:#00007F">For</SPAN> lR = <SPAN style="color:#00007F">LBound</SPAN>(myArray, 1) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(myArray, 1) <SPAN style="color:#007F00">' for each of the rows</SPAN><br>  <br>        <SPAN style="color:#007F00">'Loop Each Worksheet (minus Data) & Change Entries</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> sht <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>            <SPAN style="color:#00007F">If</SPAN> sht.Name <> tbl.Parent.Name <SPAN style="color:#00007F">Then</SPAN><br>                sht.Cells.Replace what:=myArray(lR, 1), Replacement:=myArray(lR, 2), _<br>                    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _<br>                    SearchFormat:=False, ReplaceFormat:=False<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> sht<br>    <SPAN style="color:#00007F">Next</SPAN> x<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Gater]

Sub FR_All()


Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant
Dim lR As Long

'Variable -> Data Table
Set tbl = Worksheets("Data").ListObjects("Table1")


'Data Table -> Array**(rows in 1st dim, cols in 2nd dim)
myArray = tbl.DataBodyRange.Value


'Find/Replace (Column Definitions)
fndList = 1
rplcList = 2


'Array List Loop through rows

For lR = LBound(myArray, 1) To UBound(myArray, 1) ' for each of the rows

'Loop Each Worksheet (minus Data) & Change Entries
For Each sht In ActiveWorkbook.Worksheets
If sht.Name <> tbl.Parent.Name Then
sht.Cells.Replace what:=myArray(lR, 1), Replacement:=myArray(lR, 2), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End If
Next sht
Next x
End Sub

Compile Error: Invalid Next Control Variable Reference

Happens on the Next x at the end of the code this occurs. I think I'm beginning to understand how the arrays are referenced through this. I am trying to figure things out now how to fix this. I appreciate the response as well, if you have any further help I sure do appreciate it!

 

[Gater]

I found the culprit in the original code I had posted. I was using xlPart when I should have been using xlWhole. Once I swapped the LookAt it fixed everything and its running as intended. Thanks for the help!