I have a spreadsheet with a list of strings on the first tab and a list of
search and replace strings on the second tab. I'm writing both to arrays
(searchArray and replaceArray) and then looping through them to do some
complicated search and replace functions. The following code usually runs
fine. However once I get up to around 10,000 strings to search and 1000
search and replace strings, Excel is locking up in the middle of the
function. I'm wondering if there's some upper limit on the amount of data I
can process in arrays and how I could get around this.

'initialize variable to record the number of search/replaces that are made
NoCorrections = 0

'iterate through the searchArray and search/replace with the strings in
replaceArray
For i = 1 To UBound(searchArray, 1)
For j = 1 To UBound(searchArray, 2)
For k = 2 To UBound(replaceArray, 1)

'only allow search/replace loop if search/replace terms are
not identical
'to prevent infinite looping

If Trim(LCase(replaceArray(k, iSearch))) <
Trim(LCase(replaceArray(k, iReplace))) Then
'keep looping through until ALL occurrences of search string
in search text have been replaced
Do Until InStr(LCase(searchArray(i, j)),
LCase(replaceArray(k, iSearch))) = 0
NoCorrections = NoCorrections + 1
'replace search string with replace string
searchArray(i, j) = Replace(searchArray(i, j),
replaceArray(k, iSearch), replaceArray(k, iReplace), 1, -1, vbTextCompare)
Loop
End If
Next
Next
Next

'write search/replaced searchArray back to worksheet
Range(searchRangeStr) = searchArray

'return no. of corrections made
SearchReplaceArrays = NoCorrections


Thanks,
Wayne Cressman