Substitute

[gordonr60]

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

 

[Bob Phillips]

Why use WorksheetFunction.Substitute? VBA has its own Replace function.

 

[Jacob Skaria]

Refer the below link on replace method..

http://msdn.microsoft.com/en-us/library/k9z80300(VS.85).aspx

If this post helps click Yes

 

[gordonr60]

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

 

[gordonr60]

Thanks, Bob,

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

Gordon

 

[Bob Phillips]

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

Thanks, Bob,

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

Gordon