Thanks Bob,


I didn't know about the Replace Function. I chose Substitute rather
than the Replace Method for two reasons: I sometimes use it on a
computer with Excel 97, and I don't know where in the string the text
will appear.

I'll see what I can do with the Replace Function, but nevertheless it
seems odd to me that I've encountered the below behaviour with one
instance of Substitute but not with another.


Gordon


On Apr 28, 11:59*am, "Bob Phillips" wrote:
Why use WorksheetFunction.Substitute? VBA has its own Replace function.

--
__________________________________
HTH

Bob

wrote in message

...



I have an excel file that uses VBA to convert the information in
various other files into HTML. It works fine, including the following
lines which convert "&" symbols to their html form:

For j = 8 To lastrow
* *rankList = rankList & WorksheetFunction.Substitute(shRanks.Cells
(j, i).Text, " & ", " &amp; ") & "<br /"
Next j

However, when I try to use the same technique to avoid surplus
instances of "</p" tags by using the following code:

traveller(colNo) = WorksheetFunction.Substitute(traveller(colNo), "</
p</p", "</p")

it gives me the following error: Run-time error '1004' *Unable to get
the Substitute property of the WorksheetFunction class.

I've tried doing this in various other ways, but it always produces
the same error. What am I doing wrong?

Gordon Rainsford- Hide quoted text -

- Show quoted text -