Dear Faraz

Hi again..Try the below.

1. When you are referring a table; you just need to refer that as a single
range.
2. The loop is for each Rows; and you refer the cells using .Cells(row,column)
3. You can use VBA TRIM function instead of Worksheetfucntion

With data in ColA ; your table in C1:D4; try this in Col B, cell B1 =
=textclean(A1,$C$1:$D$4)

Function TEXTCLEAN(Str1 As String, r1 As Range) As String
Dim myC As Range
TEXTCLEAN = Str1
For Each myC In r1.Rows
TEXTCLEAN = Replace(TEXTCLEAN, myC.Cells(1, 1), myC.Cells(1, 2).Text)
Next myC
TEXTCLEAN = Trim(TEXTCLEAN)
End Function

If this post helps click Yes
---------------
Jacob Skaria


"FARAZ QURESHI" wrote:

I often come across to make several changes in long data columns. Find &
Replace would completely make the changes but in a final form.

Could any of you experts please help me in designing a UDF so as to make all
the characters/combinations of the same, as inserted in one array, be
replaced with the respective entries in another.

Example is as below:

The Data Column is like:
First
Second
Third
Fourth

First Array has entries like:
ir
s
d

Second Array has respective entries as follows:
aa
bb
cc

What sort of UDF would, when applied upon the data, would lead to the
following result:
Faabbt
bbeconcc
Thaacc
Fourth

I have tried the following code but can't seem to figure out a proper
looping so as to refer to the entries in the second range:

Function TEXTCLEAN(Str1 As String, r1 As Range, r2 As Range) As String
Dim myC As Range
TEXTCLEAN = Str1
For Each myC In r1
TEXTCLEAN = Replace(TEXTCLEAN, myC.Value, <<How 2 refer r2?)
Next myC
TEXTCLEAN = Application.WorksheetFunction.Trim(TXTCLN)
End Function

Thanx in advance.