CODEWITHSUNDEEP
vbaexcel
Nid Du
Nid Du

Reputation: 75

VBA how to fix errors for strings starts with special character

I am fairly new in VBA, i am working on a project, there's small problem i am facing. I am taking newLastCmtTypeCol, newLastCmtCol, newLastNoteCol, oldLastCmtTypeCol, oldLastCmtCol, oldLastNoteCol as strings and i am only calling them in this part of code. so the error happend when one of the string start with a special character. I am taking input from sheet with alot of data. there's absolutely no way i can go through all of that data all the time. I just wanna ignore the strings start with starts with special character, so i wouldnt see any error.Here is the part of the code.

Dim newLastCmtTypeCol As String

Dim newLastCmtCol As String
Dim newLastNoteCol As String

Dim oldLastCmtTypeCol As String
Dim oldLastCmtCol As String
Dim oldLastNoteCol As String

newLastCmtTypeCol = "N"
newLastCmtCol = "O"
newLastNoteCol = "P"
oldLastCmtTypeCol = "Q"
oldLastCmtCol = "R"
oldLastNoteCol = "S"

For j = 0 To indexNew(i, 4)
    If (StrComp(ws1.Range(newLastCmtTypeCol & i + j), ws1.Range(oldLastCmtTypeCol & i + j)) = 0) And _
       (StrComp(ws1.Range(newLastCmtCol & i + j), ws1.Range(oldLastCmtCol & i + j)) = 0) And _
       (StrComp(ws1.Range(newLastNoteCol & i + j), ws1.Range(oldLastNoteCol & i + j)) = 0) And categoryCode = 1 Then

        categoryCode = 1
    ElseIf IsEmpty(ws1.Range(oldLastCmtTypeCol & i + j)) And IsEmpty(ws1.Range(oldLastCmtCol & i + j)) And IsEmpty(ws1.Range(oldLastNoteCol & i + j)) Then
        categoryCode = 3
        Exit For
    Else
        categoryCode = 2
    End If
Next j

Any solution?

Upvotes: 0

Views: 1974

Answers (2)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15641

Your issues seems to be with cells containing an error, not special characters. If so, you probably want to filter out such cells.

You could use IsError to wrap your code, e.g.

    If (Not (IsError(ws1.Range(newLastCmtTypeCol & i + j))) and _
        Not (IsError(ws1.Range(oldLastCmtTypeCol & i + j))) and _
        ... _
        ) Then

Then you would be able to compare anything else. You may want to use conversions between String and numbers, if needed.

Upvotes: 2

Doug Coats
Doug Coats

Reputation: 7117

Public Function DelInvalidCharacters(InputString As String) As String
    Dim ModString As String, InvalidChars As String, Char As String
    Dim i As Integer
    InvalidChars = "\/:*?""<>|';#,()%&$+- "
    ModString = vbNullString
    For i = 1 To Len(InputString)
        Char = Mid(InputString, i, 1)

        If InStr(1, InvalidChars, Char) = 0 Then
            ModString = ModString & Char
        End If
    Next i
    DelInvalidCharacters = ModString
End Function

Just call this function for each variable you want to strip bad characters out of

Calling it like this

Dim this As String
this = "*this"
this = DelInvalidCharacters(this)

Upvotes: 1

Related Questions