Replace All equivalent for arrays

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
618
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.

Code:
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
I would move the list object processing out of this procedure to the procedure above.

Code:
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.
 
Last edited:
Upvote 0
That worked great.

You're welcome.

Out of curiosity, why??

Because it's a very specific ornament on an otherwise reasonly reusable procedure.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top