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]

1. Do you have blank cell in your data set?
2. If the sheet name is Input1 then use: sheets("Input1").select

 

[JeffGrant]

Yes I did have blanks, you picked up an bug in my model that was not previously picked up because I always use a different column to find LastRow. Thanks for that.

However, change reference to Sheets("Input1").select instead of Sheet1.select, still generates

changing from Table format to Range format didn't make any difference either

 

[JeffGrant]

if i is starting at 1, why is the msgbox reporting the error is at row 0?

that would be because the error generated before we get to i = 1

which tells me the error is being generated here : va = Range("AX3", Cells(Rows.Count, "AX3").End(xlUp))

Your thoughts ?

 

[Akuini]

Actually this line:
va = Range("AX3", Cells(Rows.Count, "AX3").End(xlUp))
should be:
va = Range("AX3", Cells(Rows.Count, "AX").End(xlUp))

 

[JeffGrant]

Oh Man.. How did I miss something so obvious ???

cant believe my stupidity
Your Profile picture is laughing at me

 

[Akuini]

Try this one:

Sub to_FR()
    Call to_Replace("G", "C", "J") '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)

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

t = Timer
va = Range(a & 3, Cells(Rows.Count, a).End(xlUp)).Resize(, 2)
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 - Can not get the right syntax for nested array limit.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Good
2	Soft	horse name	form class	form track	form track condition		Find What	Replace With
3	Heavy	Bazooka	Bench. 72	Randwick	Good 4		Bench	BB		BB. 72
4	Good	Bazooka	Bench. 72	Rosehill	Soft 5		Class	QQQQ		BB. 72
5	Soft	Bazooka	Bench. 72	Randwick	Heavy 9		Maiden	U		BB. 72
6	Soft	Mantinello	Class 1	Scone	Good 4		x	y		QQQQ 1
7	Good	Mantinello	Class 1	Wyong	Soft 6					QQQQ 1
8	Good	Mantinello	x 12	Newcastle	Soft 5					y 12
9	Good	Fine Ruby	xx 14	Hawkesbury	Good 4					yx 14
10	Good	Fine Ruby	Maiden	Wyong	Good 4					U
11	Soft	Fine Ruby	Maiden	Newcastle	Good 4					U
12	Heavy	Chateaux Park	Bench. 64	Newcastle	Good 4					BB. 64
13	Soft	Chateaux Park	Class 111	Gosford	Soft 5					QQQQ 111
Sheet1

My Profile picture is laughing at me too, because I made the same mistake.

 

[JeffGrant]

doing it now

 

[JeffGrant]

Wow...bloody fast man....do you have any idea how much coffee I drink while this part of the code runs ?

Anyway, works great except for this one:

Find BMBM, replace with BM, there is no spaces with the BMBM?

Actually is doesn't do this one either:

Find Maiden Replace with Mdn

Oops - finds Maiden now. Maiden was in row 2, Find starts on line 3. Sorry

 

[Akuini]

Find BMBM, replace with BM, there is no spaces with the BMBM?

I don't understand, can you elaborate?

 

[JeffGrant]

when a BMBM occurs is always is in the form of BMBMxx, where xx can be from 42 to 120.

However, the point is there are no spaces in this string.