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

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

Refer the below link on replace method..

http://msdn.microsoft.com/en-us/libr...00(VS.85).aspx

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


" wrote:

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, " & ", " & ") & "<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


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 -

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 -

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 -