How to Replace All occurrences in Array?

[Fractalis]

Hi all,

Having a one dimensional VBA array, is possible to find a list of strings and Replace All occurrences of each string within array?

 

[Fractalis]

I'm not so sure. With that method, if you have "pineapples" in the column A list, you will end up with "pinegrapes" in your array. Is that what you want? You did mention 'strings' in your question but I'm wondering if you really meant 'words'?

It is also unclear whether case-sensitivity might come into play with your real data. For example, if "Apples" was in column A that would not get replaced if the column C value is "apples" with the Application.Substitute suggestion. Can you clarify that issue too please?

One more: Is it true with your real data that the original list (column A) consists of single words only, or might some cells have multiple words? If multiple words are possible, would you want "red apples" to become "red grapes" or would the entire cell need to match a column C value?

Hi Peter,

In actual data Column A has single words only and I'd like to replace case-sensitive and complete match. If I have "pineapple" "apple" and want to replace "apple" with "grapes", then output would be "pinapple", "grapes". Thanks for the help all of you

 

[Peter_SSs]

Thanks for the additional information. Given your large actual data, I would use 2 loops but they would be separate loops not outer/inner as suggested earlier.

Give something like this a try. Note that I have changed the variable name 'Source' since that is a word already used in the vba language & so is not a good idea to use as a variable name.

Sub ReplaceValuesInArray()
  Dim d As Object
  Dim vSource As Variant, SearchReplace As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  vSource = Application.Transpose(Range("A2:A20").Value)
  SearchReplace = Range("C2:D4").Value
  For i = 1 To UBound(SearchReplace)
    d(SearchReplace(i, 1)) = SearchReplace(i, 2)
  Next i
  For i = 1 To UBound(vSource)
    If d.exists(vSource(i)) Then vSource(i) = d(vSource(i))
  Next i
End Sub

 

[jolivanes]

Just wondering, for only a few thousand cells, how long does this take?

Sub How_Does_This_Measure_Up()
Dim arrInp, arrSR, i As Long, j As Long
arrInp = Sheets("Sheet2").Range("A2:A" & Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row)
arrSR = Sheets("Sheet2").Range("C2:D" & Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp).Row)
    For j = LBound(arrSR) To UBound(arrSR)
        For i = LBound(arrInp) To UBound(arrInp)
            If arrInp(i, 1) = arrSR(j, 1) Then arrInp(i, 1) = arrSR(j, 2)
        Next i
    Next j
Sheets("Sheet2").Cells(2, 6).Resize(UBound(arrInp)).Value = arrInp
End Sub

 

[Peter_SSs]

Just wondering, for only a few thousand cells, how long does this take?

For me for 6,000 rows in column A and 2000 rows in C:D as mentioned in post #7

post #13 code (excluding the line to write the results back to the worksheet): 2.051 seconds
post #12 code (which didn't write the results back to the worksheet anyway, but adjusted for the larger ranges): 0.012 seconds
So a relative time factor of about 170
The main difference is that one code has about 12,000,000 loops, the other has about 8,000

You could decrease the time considerably by exiting that inner loop as soon as a match was found. The actual time difference would depend on how many matches are found and, if so, how far through the inner loop the code had already gone but might roughly cut the time in half. That is, along these lines ..

For j = LBound(arrSR) To UBound(arrSR)
    For i = LBound(arrInp) To UBound(arrInp)
        If arrInp(i, 1) = arrSR(j, 1) Then
          arrInp(i, 1) = arrSR(j, 2)
          Exit For
        End If
    Next i
Next j

 

[Tom.Jones]

Thanks for the additional information. Given your large actual data, I would use 2 loops but they would be separate loops not outer/inner as suggested earlier.

Give something like this a try. Note that I have changed the variable name 'Source' since that is a word already used in the vba language & so is not a good idea to use as a variable name.

Sub ReplaceValuesInArray()
  Dim d As Object
  Dim vSource As Variant, SearchReplace As Variant
  Dim i As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  vSource = Application.Transpose(Range("A2:A20").Value)
  SearchReplace = Range("C2:D4").Value
  For i = 1 To UBound(SearchReplace)
    d(SearchReplace(i, 1)) = SearchReplace(i, 2)
  Next i
  For i = 1 To UBound(vSource)
    If d.exists(vSource(i)) Then vSource(i) = d(vSource(i))
  Next i
End Sub

Peter, I try your VBA code with data in post nr.7 and nothing happened. What I do wrong?

 

[jolivanes]

Thanks Peter.

 

[jolivanes]

Peter.
If it exits the loop after the first found, it'll miss the 2nd, 3rd etc occurrences.

 

[Fractalis]

Just wondering, for only a few thousand cells, how long does this take?

Sub How_Does_This_Measure_Up()
Dim arrInp, arrSR, i As Long, j As Long
arrInp = Sheets("Sheet2").Range("A2:A" & Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row)
arrSR = Sheets("Sheet2").Range("C2:D" & Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp).Row)
    For j = LBound(arrSR) To UBound(arrSR)
        For i = LBound(arrInp) To UBound(arrInp)
            If arrInp(i, 1) = arrSR(j, 1) Then arrInp(i, 1) = arrSR(j, 2)
        Next i
    Next j
Sheets("Sheet2").Cells(2, 6).Resize(UBound(arrInp)).Value = arrInp
End Sub

Thanks so much @jolivanes and @Peter_SSs. I've tried both of your codes and works perfectly. I think my current data is not big enough to affect the performance.

 

[Peter_SSs]

Peter.
If it exits the loop after the first found, it'll miss the 2nd, 3rd etc occurrences.

True. When I did my test with larger data I didn't have any repeat words and forgot about that possibility.
So we are back to the original (ball-park) relative time difference that I reported.

Peter, I try your VBA code with data in post nr.7 and nothing happened.

Yes it did. The question was about replacing values in a vba array, which is what my code does. The question did not ask for for anything specific to be done with that array after the replacements had been made, so I didn't do anything. If you stop the code at the End Sub and view what is now in the array, you will see that the relevant values have been changed.

Thanks so much @jolivanes and @Peter_SSs. I've tried both of your codes and works perfectly. I think my current data is not big enough to affect the performance.

You're welcome. Glad we could help. Thanks for the follow-up.

 

[Tom.Jones]

Tom.Jones said:
Peter, I try your VBA code with data in post nr.7 and nothing happened.

Yes it did. The question was about replacing values in a vba array, which is what my code does. The question did not ask for for anything specific to be done with that array after the replacements had been made, so I didn't do anything. If you stop the code at the End Sub and view what is now in the array, you will see that the relevant values have been changed.

Peter, can you modify your VBA cod to get results in F2:Fx?
Thank you.