Can not get the right syntax for nested array limit

JeffGrant

Well-known Member
Joined
Apr 7, 2021
Messages
510
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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
 
Your welcome, and you owe me a cup of coffee.:biggrin:
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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