Replace Error String with " "

CatLadee

New Member
Joined
Sep 7, 2018
Messages
29
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


Code:
  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]
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
What words do you want to check for?
 
Upvote 0
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 :)
 
Upvote 0
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.
Code:
If InStr(Status, "secure channel support")>0 Then
    Cell.Offset(,1).Value = ""
End If
 
Upvote 0
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 :(
 
Upvote 0
I suppose you could add the check for 'secure channel support' to your current If statement like this,
Code:
                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)?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top