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?

 

[6StringJazzer]

Yes it's possible but there is no shortcut. You have loop through each element of the array and have an inner loop to replace each string in the list. Or vice versa.

 

[Peter_SSs]

Could you give us a bit more detail?
How big is the array?
How big is the list of strings to replace?
Could we have an example of such an array, the strings to replace, the replacement string(s) & the final resultant array?

 

[Alex Blakenburg]

Yes it's possible but there is no shortcut. You have loop through each element of the array and have an inner loop to replace each string in the list. Or vice versa.

You do need a loop for each string but you don't need to loop through the data set.
This will work.
PS: I use it to remove non-breaking spaces (Application.Trim can handle a whole array without looping too)

    arr = Application.Substitute(arr, "old_value", "new_value")

 

[6StringJazzer]

you don't need to loop through the data set

Thanks for that, it's a new on one on me. When I type Application, Substitute is not available in Intellisense. I only get it by using Application.WorksheetFunction. I thought the first argument had to be a String. Did not know it would work on an array.

 

[Alex Blakenburg]

@6StringJazzer, someone else suggested to me once to use WorksheetFunction while building the function so you get the intellisense but then swapping to just Application which can handle arrays and allows the use of IsError and IfError.

 

[Fractalis]

Could you give us a bit more detail?
How big is the array?
How big is the list of strings to replace?
Could we have an example of such an array, the strings to replace, the replacement string(s) & the final resultant array?

Hi Peter_SSs,

The actual input has about 6K strings and list of strings to search has about 2k strings.

Below is a little example:

Source = WorksheetFunction.Transpose(Range("A2:A20").Value)

ToSearch = WorksheetFunction.Transpose(Range("C2:C4").Value)
ToReplace = WorksheetFunction.Transpose(Range("D2:D4").Value)

Book1
	A	B	C	D	E	F
1	input		Search	Replace		output
2	apples		apples	grapes		grapes
3	oranges		peaches	watermelon		oranges
4	peaches		lemon	pear		watermelon
5	apples					grapes
6	apples					grapes
7	apples					grapes
8	oranges					oranges
9	oranges					oranges
10	peaches					watermelon
11	mango					mango
12	lemon					pear
13	avocado					avocado
14	avocado					avocado
15	bananas					bananas
16	bananas					bananas
17	lemon					pear
18	lemon					pear
19	apricot					apricot
20	cherry					cherry
Sheet1

 

[Fractalis]

You do need a loop for each string but you don't need to loop through the data set.
This will work.
PS: I use it to remove non-breaking spaces (Application.Trim can handle a whole array without looping too)

    arr = Application.Substitute(arr, "old_value", "new_value")

Thank you. This seems to be the shortest/fastest solution possible.

 

[Alex Blakenburg]

Thanks for getting back to us. Glad we could help.
PS: Just be careful,this does not do whole word replacements so replacing apple will change both apple and pineapple

 

[Peter_SSs]

This seems to be the shortest/fastest solution possible.

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?