Issue with HTML Macro

[hairball101]

Greetings,

I'm attempting to create a macro that will search a worksheet for potential issues with HTML code. Our systems recognize &# 174; and &# 153; (sans spaces) as ® and ™ respectively, but if we attempt to use &174; or #153; it won't load properly. I've been able to create a set of code that will find issues like &174;, but I'm having trouble with finding legit #174; errors. The problem with just using a find function is that (I think) it would also find correct HTML and mark it as faulty, such as ® which would load fine. As such, I'm trying to use two integers to track how many times a &# 174; (represented as Ltotal) appears in a cell and how many times #174; (represented as Lhtml) appears. If Lhtml > Ltotal, then it should treat it as an error. The whole thing is rather lengthy, but below is the code for this specific section:

Dim oldTxt As String
    Dim nError As Long
    Dim nFixed As Long
    Dim cont As Boolean
    Dim rowSize As Long
    Dim columnSize As Long
    Dim c As Range
    Dim firstAddress As String
    Dim Lhtml As Integer
    Dim Ltotal As Integer

' ... more code above, but it's not related to this error

With Selection        Set c = .Find("#174;", LookIn:=xlFormulas, lookat:=xlPart)
        If Not c Is Nothing Then
            cont = True
            firstAddress = c.Address
        Ltotal = 0
        Lhtml = 0
        ' find out # of []
        Ltotal = ((Len(c.Value) - Len(Replace(Replace(c.Value, "&", "-"), "-#174;", ""))) / 6)
        ' find out # of [#xxx;]
        Lhtml = ((Len(c.Value) - Len(Replace(c.Value, "#174;", ""))) / 5)
        If Lhtml > Ltotal Then
            Do
                If c.Comment Is Nothing Then
                    c.AddComment "HF:" & Chr(10) & "HTML error found here"
                    c.Comment.Visible = False
                Else
                    c.Comment.Visible = False
                    oldTxt = c.Comment.Text
                    c.Comment.Text oldTxt & Chr(10) & "There are multiple here."
                End If
                Set c = .FindNext(c)
                nError = nError + 1
                
                If c Is Nothing Then
                    cont = False
                ElseIf c.Address = firstAddress Then
                    cont = False
                End If
            Loop While cont
        End If
        End If
    End With
If nError = 0 Then
        MsgBox "No HTML problems found!"
    Else
    If nFixed = 0 Then
    MsgBox nError & " HTML problems found!"
    Else
    MsgBox (nError & " HTML problems found and " & nFixed & " were fixed.")
    End If
    End If
 
    End Sub

For some reason it's not counting the number of times &#174 ; and #174; appear properly. I think it is treating the & as an operator when I just want it to treat it like text. I've tried embedding a replace within the replace to sub out & for - or something else to no avail.

Any thoughts or suggestions would be much appreciated

 

[daverunt]

I think you need to use & as an escape so &&#174

 

[hairball101]

Thank you for the reply

Unfortunately using another & as an escape character isn't working for me. I've altered the Ltotal calculation to be as follows:

Ltotal = ((Len(c.Value) - Len(Replace(c.Value, "&®", ""))) / 6)

but whenever I tweak the output to test how many it's counting, it doesn't count any instances of "®".

When I run the macro on a cell containing "test text ® #174; #174; testing.", it should come back as Ltotal = 1 and Lhtml = 3 since there is only one instance of "® but three instances of #174; (including the correct one). Instead, when I use the following formulas:

Ltotal = ((Len(c.Value) - Len(Replace(c.Value, "&®", ""))) / 6)
Lhtml = ((Len(c.Value) - Len(Replace(c.Value, "#174;", ""))) / 5)

the output is Ltotal = 0 and Lhtml = 1