My code skip the first condition check and GoTo next code, how can I fix it?-CodePudding

I have a set of data in Column A and B that may contain various data format ranging from letters, digits to special characters. I wrote the code as below, my data contains special characters.

Dim Sheet6 As Worksheet
Dim MyFilesPath As String
Dim cel As Range
Dim displaytext As String
Dim textmsg As String

            For Each cel In Worksheets("Sheet6").Range("A1").CurrentRegion
                If cel Like "*[!A-Za-z0-9]*" Then   'This line of code is skipped
                    cel.Interior.Color = vbYellow
                    textmsg = "There is non-alphanumeric character in cell:"
                    displaytext = displaytext & vbCrLf & cel.Address
                Else
                    GoTo Savefile
                End If
                
            Next cel
            
                MsgBox textmsg & displaytext, vbCritical, "Please amend."
            
            Exit Sub
            
Savefile:
            Worksheets("Sheet6").Cells.ClearFormats
        
            Sheets("Sheet6").Copy
            ChDir MyFilesPath
            ActiveWorkbook.SaveAs Filename:=MyFilesPath & "LIST_" & Format(CDate(Evaluate _
                ("WORKDAY(TODAY(), -1)")), "yyyymmdd") & ".txt", FileFormat:=xlUnicodeText
            
            ActiveWorkbook.Close

The outcome for data with special characters should be stopped with a msgbox but it didn't work, instead it jumped straight into 'Savefile' line. How can I fix this?

CodePudding user response：

There are some issues with your code, but the issue you're describing (that the code doesn't pick up non-alphanumeric characters) is not re-creatable. It might be the use of CurrentRegion causing it to miss some cells that you're expecting it to highlight?

That said, your code does skip straight to Save immediately after finding its first compliant cell. I'd suggest a different method - something like this:

Dim MyFilesPath As String
Dim cel As Range
Dim displaytext As String
Dim textmsg As String

displaytext = ""
textmsg = "There is non-alphanumeric character in cell:"

For Each cel In Worksheets("Sheet6").Range("A1").CurrentRegion
    If cel Like "*[!A-Za-z0-9]*" Then
        cel.Interior.Color = vbYellow
        displaytext = displaytext & vbCrLf & cel.Address
    End If
Next cel
                      
If displaytext = "" Then
    Worksheets("Sheet6").Cells.ClearFormats
    Sheets("Sheet6").Copy
    ChDir MyFilesPath
    ActiveWorkbook.SaveAs Filename:=MyFilesPath & "LIST_" & Format(CDate(Evaluate _
    ("WORKDAY(TODAY(), -1)")), "yyyymmdd") & ".txt", FileFormat:=xlUnicodeText
    ActiveWorkbook.Close
Else
    MsgBox textmsg & displaytext, vbCritical, "Please amend."
End If