Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-10-2014, 10:21 PM
excelledsoftware excelledsoftware is offline .Replace does not always work Windows 7 64bit .Replace does not always work Office 2003
IT Specialist
.Replace does not always work
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default .Replace does not always work

Hi everyone,

So I have some code that takes all HTML tags out of a memo like string of text. Here is the interesting thing. This code works fine one workbook but will not work at all on a different workbook. The only difference I have between the 2 is option explicit.

Here is the code that I use. Again this has worked for other workbooks.
Code:
Sub StripOutHTML ()
    'Takes out HTML elements of a text string
    Dim CheckRow as integer
    Dim TempString as string
    Dim WS as worksheet
    Dim WB as workbook
    
        Set WB = ThisWorkbook
        Set WS = WB.Worksheets("Sheet1")
        For CheckRow = 2 to 10
            WS.Range("A" & CheckRow).replace "<li>", "- "
            WS.Range("A" & CheckRow).replace "<*>", " "
            WS.Range("A" & CheckRow).replace "*>", " "
            WS.Range("A" & CheckRow).replace "<*>", " "
            WS.Range("A" & CheckRow).replace "&*;", " "
            TempString = WS.Range("A" & CheckRow).value
            WS.Range("A" & CheckRow).value = Trim(TempString)
        Next CheckRow
    
       msgbox "Done"
End Sub
Has anybody ever experienced the replace function to have problems. If I use a worksheetfunction.substitute it also works but does not support wildcards.

Thanks in advance.
Reply With Quote
  #2  
Old 04-12-2014, 05:07 AM
p45cal's Avatar
p45cal p45cal is offline .Replace does not always work Windows 7 32bit .Replace does not always work Office 2010 32bit
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

You don't explicitly supply a number of the .replace parameters and perhaps the wrong ones are being assumed?
From help:
"The settings for LookAt, SearchOrder, MatchCase, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method."
Reply With Quote
  #3  
Old 04-12-2014, 05:59 PM
excelledsoftware excelledsoftware is offline .Replace does not always work Windows 7 64bit .Replace does not always work Office 2003
IT Specialist
.Replace does not always work
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

Hmmmn that makes sense but I actually tried doing that already. I know my example code does not show that but I did try to add those parameters in and it still did not work. Im doing a different procedure that is not ideal right now I just find it odd that it only works some of the time. I will give what you are saying another try and see if I am over looking something.

Thanks
Reply With Quote
  #4  
Old 04-14-2014, 05:48 PM
excelledsoftware excelledsoftware is offline .Replace does not always work Windows 7 64bit .Replace does not always work Office 2003
IT Specialist
.Replace does not always work
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

Here is an update on this problem. I tried setting the parameters and it still did not work. I actually forgot something very important when it comes to .replace. The reason it only works half the time is due to the length of the string. If the string is over 912 characters it simply will not work. I will be modifying my current code to store the string in separate variables of 900 characters. I wrote a simple code below to prove this theory. Thanks everyone for their help.
Code:
Option Explicit
Sub ReplaceTest()
    ' checks to see what limit a .replace can handle.
    Dim CheckAmount As Integer
    Dim Success As Boolean
    CheckAmount = 2000
    Success = False
    Do Until Success = True
        Range("a1").Value = WorksheetFunction.Rept("x", CheckAmount)
        Range("a1").Replace "x", "s"
        If Left(Range("a1").Value, 1) = "s" Then
            Success = True
            Exit Do
        End If
        CheckAmount = CheckAmount - 1
    Loop
    
    Range("B1").Value = CheckAmount + 1 ' Add one since the loop took one away.
    MsgBox "The replace works with " & CheckAmount + 1 & " characters max."
       
        
End Sub
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
.Replace does not always work Multiple replace does not work with 2013 fprm67 Word VBA 11 04-15-2014 03:14 PM
Find and replace No longer work TJH Word 3 03-25-2014 11:33 PM
.Replace does not always work work of labor vs work of excavator ketanco Project 1 02-11-2014 08:53 AM
.Replace does not always work work vs regular work. and how regular work works user0044 Project 5 03-06-2012 07:28 AM
.Replace does not always work Help with find and replace or query and replace shabbaranks Excel 4 03-19-2011 08:38 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:43 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft