Replace All equivalent for arrays

[Dr. Demento]

Is there an equivalent to Replace All for use within an array? The sub below accomplishes the same thing but it uses a nested loop which I was hoping to avoid. I'm using the outer loop to cycle thru the table/array with the Find/Replace terms and the inner loop to cycle thru the data; with as many as 20 find/replace terms and as few as 500 rows of data, I'm looking at 10k cycles.

Private Sub arr_replace(rra_format As Variant, _
                        col_rra As Long, _
                        lbt_replace As String)
Dim arr_tbl As Variant
Dim i As Long, _
    row_arr As Long
    
  ' ~~ Identify table that contains the Find/Replace terms
  arr_tbl = ThisWorkbook.Worksheets("formatLists").ListObjects(lbt_replace).DataBodyRange.value
  
  For i = LBound(arr_tbl, 1) To UBound(arr_tbl, 1)
    For row_arr = LBound(rra_format, 1) To UBound(rra_format, 1)
      rra_format(row_arr, col_rra) = Replace(rra_format(row_arr, col_rra), arr_tbl(i, 1), arr_tbl(i, 2))
    Next row_arr
  Next i
End Sub

Thanks y'all.

 

[Dr. Demento]

Additionally, what code can I add/use to mimic the .Range.Replace xlWhole term?? I'm getting xlPart behavior that I don't want.

Thanks.

 

[shg]

I would move the list object processing out of this procedure to the procedure above.

Sub ArrayReplace(avsInp As Variant, _
                 avsFindRepl As Variant, _
                 iCol As Long)
  
  ' avsFindRepl is a 2-column array of find/replace pairs
  ' column iCol of array avsInp is modified to replace the find values with the replace values

  Dim sFind         As String
  Dim sRepl         As String
  Dim iFR           As Long
  Dim iRow          As Long

  For iFR = LBound(avsFindRepl) To UBound(avsFindRepl)
    sFind = avsFindRepl(iFR, 1)
    sRepl = avsFindRepl(iFR, 2)
    For iRow = LBound(avsInp) To UBound(avsInp)
      If avsInp(iRow, iCol) = sFind Then avsInp(iRow, iCol) = sRepl
    Next iRow
  Next iFR
End Sub

Be aware that string comparisons in VBA are case-sensitive by default.

 

[Dr. Demento]

Thanks, shg. That worked great.

Out of curiosity, why??

I would move the list object processing out of this procedure to the procedure above.

 

[shg]

That worked great.

You're welcome.

Out of curiosity, why??

Because it's a very specific ornament on an otherwise reasonly reusable procedure.

 

[Dr. Demento]

Copy. Thanks again.

 

[shg]

Yer welcome.