Substitute

  • Thread starter Thread starter gordonr60
  • Start date Start date
G

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
 
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
 
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
 
Back
Top