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

 

[JeffGrant]

						Find & Replace - Form Track Condition
class restrictions	horse name	form class	form track	form track condition		Find What	Replace With
Class 1	Bazooka	Bench. 72	Randwick	Good 4		Firm 1	Firm
Class 1	Bazooka	Bench. 72	Rosehill	Soft 5		Firm 2	Firm
Class 1	Bazooka	Bench. 72	Randwick	Heavy 9		Good 2	Good
Class 1	Mantinello	Class 1	Scone	Good 4		Good 3	Good
Class 1	Mantinello	Class 1	Wyong	Soft 6		Good 4	Good
Class 1	Mantinello	Maiden	Newcastle	Soft 5		Soft 5	Soft
Class 1	Fine Ruby	Maiden	Hawkesbury	Good 4		Soft 6	Soft
Class 1	Fine Ruby	Maiden	Wyong	Good 4		Soft 7	Soft
Class 1	Fine Ruby	Maiden	Newcastle	Good 4		Heavy 8	Heavy
Class 1	Chateaux Park	Bench. 64	Newcastle	Good 4		Heavy 9	Heavy
Class 1	Chateaux Park	Class 1	Gosford	Soft 5		Heavy 10	Heavy
Class 1	Chateaux Park	Class 1	Hawkesbury	Heavy 8		Dead 4	Dead
Class 1	Tip Top Timing	Class 1	Gosford	Soft 5		Dead 5	Dead
Class 1	Tip Top Timing	Maiden	Goulburn	Heavy 10		Dead 6	Dead
Class 1	Tip Top Timing	Maiden	Hawkesbury	Soft 7		Slow 7	Slow
Class 1	Speedzone	Bench. 64	Kembla Grange	Good 3		Slow 8	Slow
Class 1	Speedzone	Class 1	Wyong	Soft 6		Slow 9	Slow

 

[Akuini]

Hi, @JeffGrant
Your sample data in post #2, is that your actual data?
So the content in col E is a SINGLE WORD followed by numbers, and you just want to remove the number part?
If yes, then there's a faster way than using Replace from a list.
If not, then using Dictionary object would be faster.
How many rows is your data?

 

[JeffGrant]

Hi Akuini,

I never thought about it like that, but you are correct. In this particular Find & Replace, only the number needs to be stripped away.

However, there are four other Find & Replaces that actually do involve find and replacing text.

Being - this block is run over two separate columns for example.

The number of records can be anywhere between 1,000 and 5,000 on any particular day.

If I run this over a week for example, the number of records can be up to 50,000.

Which is why I would like to do this in memory rather than reading/writing to a sheet.

Find What	Replace With
Maiden	Mdn
Rest 0 Metro Wins	RS0MW
Rest 1 Metro Win	RS1MW
Rest 2 Metro Wins	RS2MW
Rst 0 Met Win-LY	RS0LY
Rst 1 Met Win-LY	RS1LY
Rst 2 Met Win-LY	RS2LY
Rst 3 Met Win-LY	RS3LY
Restricted	RST
Rest.	RST
Cls	Class
56+	BM56+
58+	BM58+
64+	BM64+
Bench.	BM
Benchmark	BM
BMBM	BM

 

[Akuini]

However, there are four other Find & Replaces that actually do involve find and replacing text.

Ok, so there are data that just need the number to be removed & data that need Find-Replace.
Are they in separate column or sheet?
If not, can you easily separate them?

 

[JeffGrant]

Yes, everything is in separate columns.

For the purpose of this example.....
From reply #2 above.
The number is separated off the data in Column 5, as you have identified.

From Reply # 4 above.
The Find and Replace needs to be applied to Columns 1 and 3.

Then there is another Find and Replace Text list, that I have not put up here, that is applied to Column 4.

Then there is another Find & Replace list made up of silly odd bits to clean the data up.

Unfortunately, the input data comes from a variety or sources, and the purpose of these Find and Replace routines is to normalize the data.

Effectively, I run the Find and Replace Sub four times on different columns, which is why I didn't notice what you identified about the number separation in column 5.

These Find and Replace are the slowest part of the whole model, which is why I am looking to speed it up anywhere I can.

 

[Akuini]

Ok, try this:
1. Sub remove_number : it's for col E, to remove the number , assuming the value are only a single word and number
2. Sub to_FR: it's for Find-Replace, you need to adjust the code:
Call to_Replace("G", "D") 'G means list of find-replace is in col G:H, D means column where data is located
and because you have multiple column of data then add some other line accordingly.

Note: I put the result in col J:K so you can see the result without changing the data, but of course you need to adjust it.

Sub remove_number()
Dim i As Long
Dim va, t
t = Timer
va = Range("E3", Cells(Rows.Count, "E").End(xlUp))
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"

End Sub

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

Sub to_Replace(a As String, b As String)

Dim i As Long
Dim va, vb, t
Dim d As Object
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)
    If d.Exists(vb(i, 1)) Then vb(i, 1) = d(vb(i, 1))
Next

Range("J3").Resize(UBound(vb, 1), 1) = vb

'use this one:
'Range(b & 3).Resize(UBound(vb, 1), 1) = vb
Debug.Print "It's done in: " & Timer - t & " seconds"
End Sub

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Good					Find & Replace - Form Track Condition
2	Soft	horse name	form class	form track	form track condition		Find What	Replace With
3	Heavy	Bazooka	Bench. 72	Randwick	Good 4		Gosford	G		I	Good
4	Good	Bazooka	Bench. 72	Rosehill	Soft 5		Goulburn	N		Z	Soft
5	Soft	Bazooka	Bench. 72	Randwick	Heavy 9		Hawkesbury	U		I	Heavy
6	Soft	Mantinello	Class 1	Scone	Good 4		Kembla Grange	R		W	Good
7	Good	Mantinello	Class 1	Wyong	Soft 6		Newcastle	B		H	Soft
8	Good	Mantinello	Maiden	Newcastle	Soft 5		Randwick	I		B	Soft
9	Good	Fine Ruby	Maiden	Hawkesbury	Good 4		Rosehill	Z		U	Good
10	Good	Fine Ruby	Maiden	Wyong	Good 4		Scone	W		H	Good
11	Soft	Fine Ruby	Maiden	Newcastle	Good 4		Wyong	H		B	Good
12	Heavy	Chateaux Park	Bench. 64	Newcastle	Good 4					B	Good
13	Soft	Chateaux Park	Class 1	Gosford	Soft 5					G	Soft
14	Heavy	Chateaux Park	Class 1	Hawkesbury	Heavy 8					U	Heavy
15	Soft	Tip Top Timing	Class 1	Gosford	Soft 5					G	Soft
16	Good	Tip Top Timing	Maiden	Goulburn	Heavy 10					N	Heavy
17	Soft	Tip Top Timing	Maiden	Hawkesbury	Soft 7					U	Soft
18	Class 1	Speedzone	Bench. 64	Kembla Grange	Good 3					R	Good
19	Class 1	Speedzone	Class 1	Wyong	Soft 6					H	Soft
20
Sheet1

 

[JeffGrant]

Hi Akuini,

Um--I am blown away, gob smacked, surprised, in total Awe....

Thank you so much...

I wish I knew more like you.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[JeffGrant]

Akuini, thank you again for such a fantastic response. As you would appreciate it has sped up the calculations immensly.

Unfortunately, I have found one minor glitch. Using CompareMode like this is comparing the whole cell and does not do a partial comparison.

For example, if we pick any Find value, say 56 as part of the string.

In the original Find & Replace, I would have:

Find values
Restricted -> Replace with RST
RST. -> Replace with RST
Bench. -> Replace with BM
Benchmark -> Replace with BM

with the CompareMode based on entire cell value, I have to effectively quadruple the size of the FIND List to be in the order of some 500 entries, just for this sequence of numbers.

Given that exponential increase in processing speed, it is not such a big deal, but I was just wondering if there a more efficient way to handle the partial string search?

cheers