Here is some code you can use that will allow you to uses Replace even with
97

#If VBA6 Then
#Else
'-----------------------------------------------------------------
Function Replace(expression As String, _
find_string As String, _
replacement As String)
'-----------------------------------------------------------------
Dim i As Long
Dim iLen As Long
Dim iNewLen As Long
Dim sTemp As String


sTemp = expression
iNewLen = Len(find_string)
For i = 1 To Len(sTemp)
iLen = Len(sTemp)
If Mid(sTemp, i, iNewLen) = find_string Then
sTemp = Left(sTemp, i - 1) & replacement & Right(sTemp, iLen -
i - iNewLen + 1)
i = i + iNewLen - 1
End If
Next i

Replace = sTemp

End Function
#End If

--
__________________________________
HTH

Bob

wrote in message
...
Thanks, Bob,

It works fine with Replace, though not in Excel 97.

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 -