Find & Replace is searching whole workbook instead of just the sheet

[JeffGrant]

Hi All,

I have adopted the following code from another user, Tmteast, in this forum and have a minor issue. On the whole it works great.
We set the table, array and ranges all ok.
All Variables are Dimensioned at the top of the module under Option Explicit

The issue is that when the the Find/Replace Sub is called, the code does the Find/Replace over the entire workbook, instead of just the set sheet. Which implies that actual source data in the tables is also being changed. Which it does.
The code seems to lose the set sheet name when the Find/Replace sub is called.

Why is the Find/Replace not being restricted to the set sheet?

All assistance is graciously received.

Thankyou

Sub Multi_FindReplace()

'Create variable to point to the Class table
Set tbl = Sheet27.ListObjects("tblFRTClassFull")

'Create an Array out of the Table's Data
Set TempArray = tbl.DataBodyRange
myArray = Application.Transpose(TempArray)

'Designate Columns for Find/Replace data
fndList = 1
rplcList = 2

Set shtJson = Sheet10

With shtJson
lrow = .Cells(Rows.Count, 1).End(xlUp).Row
lCol = .Cells(1, Columns.Count).End(xlToLeft).Column

Set rng1 = .Range("I1:I" & lrow) 'Track Name
Set rng2 = .Range("AC1:AC" & lrow) 'Track Name in Start Time Table
End With

'Create variable to point to the Track Name table
Set tbl = Sheet27.ListObjects("tblFRTTrackFull")

'Create an Array out of the Table's Data
Set TempArray = tbl.DataBodyRange
myArray = Application.Transpose(TempArray)

'Replace Track Name
Set rng = rng1
Call FindReplaceRangeWhole

'Replace Form Track Name
Set rng = rng2
Call FindReplaceRangeWhole

End Sub

Sub FindReplaceRangeWhole()
'Loop through each item in Array lists
For x = LBound(myArray, 1) To UBound(myArray, 2)

rng.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Next x
End Sub

 

[bobsan42]

For x = LBound(myArray, 1) To UBound(myArray, 2)

this is quite awkward, and probably wrong ...

myArray = Application.Transpose(TempArray)

you do this twice, but you do nothing with the array the first time. Just overwrite it with another array the second time. and why transpose it anyway?

'Create an Array out of the Table's Data
Set TempArray = tbl.DataBodyRange

is TempArray a range? and why? no checks or anything so why bother?

Nothing in your code suggests that Replace is applied to the whole workbook.
A bit strange way of dealing with variables (best is to pass rng as a parameter to the FindReplaceRangeWhole sub), but Replace seems to be done only on ranges rng1 and rng2, which seem to belong to sheet10.
If possible write explicitly, do not let the compiler try to figure out what you mean and figure out the defaults you've omitted.
And post the whole code, and with the code tags provided here.

 

[JeffGrant]

Hi Bobsan,

thanks for that.

myArray = Application.Transpose(TempArray) is done twice because it points to two different tables.

Please excuse my ignorance with, " is TempArray a range? and why? no checks or anything so why bother?" points to the body of the table where the values to find and the values to replace are found. then control is passed to the find/replace sub.

what checks would you suggest?

 

[JeffGrant]

If I was to write this explicitly inside the calling sub, then I would have to repeat the code many times as there are several find/replace operations that are passed to the find/replace sub. I would end up with the code repeated many times.

 

[bobsan42]

myArray = Application.Transpose(TempArray) is done twice because it points to two different tables.

yes, but you do nothing with it the data from the first table. Just overwrite it with the second table later on.

 

[bobsan42]

'Create an Array out of the Table's Data
Set TempArray = tbl.DataBodyRange
myArray = Application.Transpose(TempArray)

this should only be:

myArray = Application.Transpose(tbl.DataBodyRange.Value)

and I wouldn't bother with the transpose anyway.

this should be:

Sub FindReplaceRangeWhole(byref rng as range)
'...
end sub

then you call it like this:

FindReplaceRangeWhole rng1
...
FindReplaceRangeWhole rng2
...

no need to repeat anything.
just reduce the usage of module level variables if they are not really needed, which is rare at best e.g. if a variable needs to exist for a longer period.

Still, regardless of the above - I still don't see how this code can change many/all sheets in a workbook - only 2 columns on sheet10 should be affected.
There must be more than meets the eye.

 

[JeffGrant]

Clearly you understand considerably more than I, so thanks for that. I have to head off now, so I will look at what you say over the weekend.

Oddly enough, there is not more than meets the eye, which is why I am confused about the original source data in the tables being change.

At the end of the day, I am not married to this code. It is just a solution that pops up on several excel help sites. I am only looking for a solution, that:

1. Reads source find and replace data from a table/range. - generally about 200 values to find
2. Does the find and replace on several specified columns in specified sheets. - this can easily go to 20,000 replacements with each pass of the replacement value
3. Because my model is very large (>50Mb), that is why I am using arrays.

If this adopted code is not working as it should, then I am more than happy to be guided by you and those who know much more than I.

thanks again

 

[bobsan42]

If you build an array like this:

... then you can use the following example procedure (call it for example MyArray):

Sub multi_replace_eaxmple(ByRef myArray As Variant)
    Dim i As Long, j As Long
    Dim sh As Worksheet, rng As Range
    j = LBound(myArray, 2)
    For i = LBound(myArray, 1) To UBound(myArray, 1)
        Set sh = ThisWorkbook.Worksheets(myArray(i, j))
        With sh
            Set rng = Intersect(.UsedRange, .Columns(myArray(i, j + 1)))
        End With
        If Not rng Is Nothing Then
            findAndReplaceThroughArray rng, myArray(i, j + 2), myArray(i, j + 3), xlWhole
            'Alternative is to use the range.replace method but it is almost 5-10 times slower when replacing
            'rng.Replace myArray(i, j + 2), myArray(i, j + 3), xlWhole, , False
        End If
    Next i
    Set sh = Nothing
    Set rng = Nothing
    End Sub

'This is a generic procedure - uses un array to speed up Replace on a range
Sub findAndReplaceThroughArray(ByRef rng As Range, _
                   ByVal findWhat As Variant, _
                   ByVal replaceWith As Variant, _
                   Optional ByVal lookAt As XlLookAt = xlPart)
    Dim valuesArray As Variant
    valuesArray = rng.Formula
    Dim i As Long, j As Long, a As Variant
    findWhat = CStr(findWhat)
    If lookAt = xlPart Then
        For i = LBound(valuesArray, 1) To UBound(valuesArray, 1)
            For j = LBound(valuesArray, 2) To UBound(valuesArray, 2)
                a = valuesArray(i, j)
                If a Like "*" & findWhat & "*" Then
                    valuesArray(i, j) = Replace(a, findWhat, replaceWith, , , vbTextCompare)
                End If
            Next j
        Next i
    ElseIf lookAt = xlWhole Then
        For i = LBound(valuesArray, 1) To UBound(valuesArray, 1)
            For j = LBound(valuesArray, 2) To UBound(valuesArray, 2)
                If valuesArray(i, j) = findWhat Then _
                    valuesArray(i, j) = replaceWith
            Next j
        Next i
    End If
    rng.Value = valuesArray
    Erase valuesArray
    valuesArray = Null
    a = Null
    End Sub

if you provide more details and information maybe you can get more helpful and specific answers.