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]

Let's say you have:
Find-replace:
aa -> bb
aaa -> xx

Data:
haaw
haaaw

what the result should look like?
hbbw or bb?
hxxw or xx?

 

[JeffGrant]

Hi Mate, I wasn't expecting answer so quickly, what country are you in? I am in Australia....

In the Data is something like Bench. 45
I look for "Bench. " - including the space after the .
and replace with "BM" without the quotes
End Result is BM45

 

[JeffGrant]

Same applied for the search terms
"Benchmark "
"Restricted "
"RST. "

Because at the end of the day...

Bench. 45 = Benchmark 45 = Restricted 45 = RST. 45 = BM45

So I really only want to end up with one term being BM45

 

[Akuini]

There are some issues with partial match find-replace:
1. It will increase processing time significantly
2. Depend on your data this could be a problem:
Let's say you have:
Find-replace:
own -> bb

Data:
own xx
brown yy

actually I just want "own xx" to become "bb xx", but "brown yy" will become "brbb yy" because "brown" has "own" in it.
Could this be a problem with your actual data?

Note: I live in Indonesia, so we are neighbors.

 

[JeffGrant]

You are correct. This does occur even with the Find and replace that I use now. Because sometimes I will end up with BMBM45.

The text will only ever end up in this format and not in the middle of the string as you described.

However, I compensate for this at the very last step by finding BMBM and replacing with BM

 

[JeffGrant]

Mate, I am so sorry to bother you like this.....

In the remove_number sub,

I changed this line va = Range("E3", Cells(Rows.Count, "E").End(xlUp)) to

va = Range("AX3", Cells(Rows.Count, "AX").End(xlUp)) - data starts at AX3, so no drama.

Added Sheet1.Select to make sure that we are pointing to the right sheet

and now this line va(i, 1) = Split(va(i, 1), " ")(0) is giving a runtime 9, out of subscript range error.

adding MSGBOX Cells(Rows.Count, "AX").End(xlUp) is only returning the value in AX3, not the row count.

I have been trying to work this for about an hour now and just dont understand why this is doing this.

But it works perfectly on the test sheet
any ideas ?

 

[Akuini]

Not sure why, but see what's in the message using this code:

Sub remove_number()
Dim i As Long
Dim va, t

Sheet1.Select
t = Timer
va = Range("AX3", Cells(Rows.Count, "AX3").End(xlUp))
On Error GoTo skip:
For i = 1 To UBound(va, 1)
    va(i, 1) = Split(va(i, 1), " ")(0)
Next

'change K3 to E3
Range("K3").Resize(UBound(va, 1), 1) = va
Debug.Print "It's done in: " & Timer - t & " seconds"
Exit Sub

skip: MsgBox ActiveSheet.Name & " : " & "row: " & i
End Sub

 

[JeffGrant]

 

[JeffGrant]

 

[JeffGrant]

I moved the On error message to above sheet1.select