UDF to make a kind of Substitute Array-Function

Question

I'm trying to make a function that not only substitute one text with another, but substitute a set of values from a range with another lateral set of values.

I have this:

Public Function SubstituteRange(RangeWithText As Range, TwoColumnMatrix As Range) As String
Dim Text As String
Text = "/" & RangeWithText.Value & "/"
'as example st like this: "/" & "1/2/3/4/5/6/7/8" & "/" = "/1/2/3/4/5/6/7/8/"
Dim SearchForRange As Range
Set SearchForRange = TwoColumnMatrix.Columns(1)
'let us say "A1:A4" with /2/ /3/ /4/ /5/ in each cell    
Dim ReplaceWithRange As Range
Set ReplaceWithRange = TwoColumnMatrix.Columns(2)
'let us say "B1:B4" with /9/ /10/ /11/ /12/ in each cell
Dim i As Integer
SubstituteRange = Text
For i = 1 To SearchForRange.Rows.Count '4 rows
SubstituteRange = Application.WorksheetFunction.Substitute(SubstituteRange, _
SearchForRange.Item(i), ReplaceWithRange.Item(i))
Next i
End Function

but this return an "#Value!" error, can somebody help me with this ? I expected to get from this example something like "/1/9/10/11/12/6/7/8/" but I didn't get it. thank you in advance.

VBA has `Replace()`, which will do the work of `Application.WorksheetFunction.Substitute()` — Tim Williams, Oct 02 '15 at 22:00
`Replace()` is about 6-7 times faster in the quick test I just ran. — Tim Williams, Oct 05 '15 at 16:16

score 1 · Accepted Answer · answered Oct 02 '15 at 22:08

1

Function MultiReplace(v, rng)
    Dim rw As Range, rv
    rv = "/" & v & "/"
    For Each rw In rng.Rows
        rv = Replace(rv, rw.Cells(1).Value, rw.Cells(2).Value)
    Next rw
    MultiReplace = rv
End Function

answered Oct 02 '15 at 22:08

Tim Williams

154,628
8
97
125

thank you Tim, this surpasses what I did,. you combine speed and easiness-to-read. I will change my code for this one – JoeJoe Oct 05 '15 at 13:15

score 0 · Answer 2 · answered Oct 02 '15 at 21:59

0

Try changing the substitute statement into this (tested):

SubstituteRange = Application.Substitute(SubstituteRange, _
SearchForRange.cells(i).Text, ReplaceWithRange.Cells(i).Text)

answered Oct 02 '15 at 21:59

A.S.H

29,101
5
23
50

you're right!! thank you very much, I did these changes and it works perfectly, but why works with cells(I) and not with item(I) ?? and another one, why works with application.substitute and not with application.worksheetfunction.substitute ?? – JoeJoe Oct 05 '15 at 13:06
for the second question, it is simply shorter, both work. For the first question, both should work, but I and most developers are used to use cells. – A.S.H Oct 05 '15 at 13:15
I'm thinking that my code didn't work because I use item(I) and it's used more in the environment of OOP, may because it hasn't only one value (as does the default value of Cell, that is Value) – JoeJoe Oct 05 '15 at 14:05

UDF to make a kind of Substitute Array-Function

2 Answers2