Replace Error String with " "

[CatLadee]

Hi friends

Brand new to coding. In a VBA class as we speak so be easy!

I have pieced together code that checks if a long list of website links that are managed by external parties are still working. If there's an error code associated with the site, it returns the code and puts it in the cell next to the cell with the web address. Problem is it's returning codes to sites that work, even if there is an error associated with them. I don't want to see these codes. I've rigged my way through hiding most of them but can't seem to hide "An error occurred in the secure channel support".


I've tried adding it to the list of other errors that are hidden - didn't work. I tried doing a separate If Then statement - didn't work.
It seems that the error message has a return at the end of it. Like "An error occurred in the secure channel support (RETURN)" because when I click on the cell the message is in it goes to the line below the message. I tried to "trim" the error message column to get rid of that then run it - didn't work.

Any ideas? Maybe a code that says if it contains some of the words then make the cell blank? Appreciate the help - CatLadee

  Set Wks = ActiveSheet
        Set Rng = Wks.Range("F2")
       ' Set RNG2 = Wks.Range("F3") [B]' Doesn't work[/B]
        
        Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
        If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)
        
            For Each Cell In Rng
            
                If IsEmpty(Cell) = True Then
                    Cell.Offset(0, 1).Value = "Link Missing"
                ElseIf InStr(1, Cell.Value, "mailto:") > 0 Then
                    Cell.Offset(0, 1).Value = ""
                ElseIf InStr(1, Cell.Value, "[URL]https://www.state.gov/travel/[/URL]") > 0 Then
                    Cell.Offset(0, 1).Value = ""
                Else
                Status = GetURLStatus(Cell)
                If (Status <> "200 - OK") And (Status <> "301 - Moved Permanently") And (Status <> "302 - Moved Temporarily") And (Status <> "401 - Unauthorized") And (Status <> "302 - Found") Then
                    Cell.Offset(0, 1) = Status
                End If
                End If
                                         
        Next Cell
           
             
        If Status = "The operation timed out" Then
            Cell.Offset(0, 0).Value = ""
        End If
        
             
        'If RNG2 = "An error occurred in the secure channel support" Then [B]'Does[/B]
           ' Cell.Offset(0, 0).Value = "" [B]'[/B][B]Not[/B]
        'End If [B]'work[/B]

 

[Norie]

What words do you want to check for?

 

[CatLadee]

For "An error occurred in the secure channel support" or a derivation of that - maybe "secure channel support"? There's no error code that's close to it that could possibly get pulled in mistakenly - thank you

 

[Norie]

I'm not sure exactly what you mean by this,

Problem is it's returning codes to sites that work, even if there is an error associated with them.

but if you wanted to check if Status contained the text 'secure channel support' you could use InStr.

If InStr(Status, "secure channel support")>0 Then
    Cell.Offset(,1).Value = ""
End If

 

[CatLadee]

I mean that it was returning 200 - OK, 302 - Moved Temporarily, etc and I'm looking to make it an discrepancy report - so to show sites that you can't access. Maybe "error code" is the wrong terminology.

I added your code - copy and pasted - under the code above and it ran but didn't work

 

[Norie]

I suppose you could add the check for 'secure channel support' to your current If statement like this,

                If InStr(Status, "secure channel support")=0 And (Status <> "200 - OK") And (Status <> "301 - Moved Permanently") And (Status <> "302 - Moved Temporarily") And (Status <> "401 - Unauthorized") And (Status <> "302 - Found") Then
                    Cell.Offset(0, 1) = Status
                End If

but that If statement's going to keep on growing if you want to check for other text/messages.

What exactly does the sub/function GetURLStatus do to check the status of the URL(s)?

 

[CatLadee]

That worked Previously I had the whole string in there and it didn't work. I wasn't sure how to integrate the InStr. Thanks so much - you've brightened my day! Cat Ladee