JeffGrant
Well-known Member
- Joined
- Apr 7, 2021
- Messages
- 519
- Office Version
- 365
- Platform
- 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
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