Can not get the right syntax for nested array limit

[JeffGrant]

Hi All, I am starting to realise how much I don't know. That is for sure

in a bid to speed up my calculations, I would like to move my Find & Replace functions from reading/writing the spreadsheet to an array in memory.
I have adopted this array code from Hacker95 and am trying to modified it by watching a bunch of YouTube videos.
Doing a single array is easy enough, but nesting arrays is throwing me.

Because I am new to memory array structures, I am stuck on this syntax. I know the arr(5, Row) is where I am falling down in this line.

For Each c In arr(5, Row)

5 equates to column E in the spreadsheet and Row is supposed to be the incremental row.

I would really appreciate fi somebody could tell me what I am doing wrong here.

Thanks in advance

Jeff

Here is the whole Sub for reference

Sub ReadRange()
Dim arr As Variant, fnd As Variant

'Read the Arrays to read in
arr = Sheet15.Range("A1").CurrentRegion  'Range of values to be Updated
fnd = Sheet15.Range("G1").CurrentRegion  'Range of values to update with. Column G is Find Value, Column His Replace Value

'Am using this With Statement to find limits because I cant make the fnd array work 
With Sheet15 ' reading end of rows from here because is only a once off read
  LastRow = Range("H3").End(xlDown).Row
  Row = Range("A1").End(xlDown).Row
  sr = .Range("G3:H" & LastRow)
End With
 
'Do Find & Replace
  For Each c In arr(5, Row)     'THIS LINE GIVES A RUN-TIME 9, SUBSCRIPT OUT OF RANGE ERROR
    For i = LBound(sr) To UBound(sr)
      If InStr(c, sr(i, 1)) Then
        c = Replace(c, sr(i, 1), sr(i, 2))
      End If
    Next i
  Next c

'write the Array out
Sheet15.Range("A24").CurrentRegion.ClearContents
Dim rowCount As Long, columnCount As Long
rowCount = UBound(arr, 1)
columnCount = UBound(arr, 2)

Sheet15.Range("A24").Resize(rowCount, columnCount).Value = arr

End Sub

 

[Akuini]

Oh, you're talking about to remove number.
It needs to be a space in the string.
Well, then try using "Sub to_FR", see if it fast enough.

 

[JeffGrant]

Sorry mate.. not talking about remove number at all.. that is all great. thanks.

What happens is this. from external data source 1, I can get in column c, form class 58+.

From external data source 2, I can get form class BM58+, that would also be column C.

So when I replace 58+, with BM58+, if there is BM58+ somewhere else in the data, it will create BMBM58+

 

[JeffGrant]

just doing a couple of tests, if the first two letters test are BM, then continue to next iteration.....
that also removes the BMBM problem

 

[JeffGrant]

I just change the loop to this

For i = 1 To UBound(vb, 1)
    tx = vb(i, 1)
    If Left(tx, 2) = "BM" Then
    GoTo Skip
    
    ElseIf d.Exists(tx) Then
    vb(i, 1) = d(tx)
    Else
        For j = 1 To UBound(va, 1)
            If InStr(tx, va(j, 1)) Then
                vb(i, 1) = Replace(tx, va(j, 1), va(j, 2), 1, 1, vbTextCompare)
                Exit For
            End If
        Next
    End If
Skip:
Next

And that seems to have resolved the BMBM issue

 

[Akuini]

And that seems to have resolved the BMBM issue

Glad it works.

 

[JeffGrant]

Akuini,

just tested on the real data...works perfectly...

 

[JeffGrant]

Just out of curiosity, the data we are searching for and it's replacement are on the same worksheets as the live data. So this line

Call to_Replace("CV", "AM", "CY")

everything is together.

is it possible to expand to this....

CV is really on another worksheet being Data!A, and not the real data sheet. I just used CV to keep it on the same work sheet.

Can I do this...
Call to_Replace("Data!A", "Input1!AM", "Input1!CY")

 

[JeffGrant]

Nope - didn't like that... All good, I will adjust my code so that the find and replace values are always on the same active sheet.

Thank you so much for your help.

I wish I could show you how grateful I am..

 

[Akuini]

CV is really on another worksheet being Data!A, and not the real data sheet. I just used CV to keep it on the same work sheet.

So sheet "Data" is where list of Find-replace located, right?
And your data & the result is in another sheet.
Try this:
The other sheet must be the active sheet when you run the code.

Sub to_FR()
    Call to_Replace("G", "C", "J", "Data") 'G means list of find-replace is in col G:H, D means column where data is located, K is the output column
    'add another list & data column here
    'Call to_Replace("M", "A", "K")
End Sub

Sub to_Replace(a As String, b As String, c As String, sh As String)

Dim i As Long, j As Long
Dim va, vb, t
Dim d As Object
Dim tx As String

t = Timer
With Sheets(sh)
    va = .Range(a & 3, .Cells(Rows.Count, a).End(xlUp)).Resize(, 2)
End With
vb = Range(b & 3, Cells(Rows.Count, b).End(xlUp))

Set d = CreateObject("scripting.dictionary")
d.CompareMode = vbTextCompare

For i = 1 To UBound(va, 1)
    d(va(i, 1)) = va(i, 2)
Next

For i = 1 To UBound(vb, 1)
    tx = vb(i, 1)
    If d.Exists(tx) Then
        vb(i, 1) = d(tx)
    Else
        For j = 1 To UBound(va, 1)
            If InStr(tx, va(j, 1)) Then
                vb(i, 1) = Replace(tx, va(j, 1), va(j, 2), 1, 1, vbTextCompare)
                Exit For
            End If
        Next
    End If
Next

'put the result
Range(c & "3").Resize(UBound(vb, 1), 1) = vb

Debug.Print "It's done in: " & Timer - t & " seconds"
End Sub

 

[JeffGrant]

Akuini,

that is absolutely positively fantastically unbelievably good.

You sure know your programming.

That is fantastic man......