VBA Syntax Error when replacing

[loribear180]

Hello everyone,

Last year I received help in creating the code attached. My export changed and now I need to update the replace parameters, however, are there limits to what can be replaced? I'm trying to replace an entire paragraph with a simple 'Y' but I keep receiving a 'Syntax Error' when I try to run the code.

My export has 6 different answers that I need to replace with simple Y or N. The specific language I'm struggling with reads:
Veteran. The individual is defined as an individual who has served in (and has been honorably discharged from) the following branches of service: The U.S. Army, Navy, Air Force, Marine Corps, or Coast Guard or the U.S. Public Health; Service under Title 42, United States Code, Section 201; The Texas Military Forces as defined by Texas Government Code, Section 437.001; or an auxiliary service of one of the branches of the U.S. Armed Forces.

Am I having this issue because it's too long of an answer and contains parentheses, colons, semi-colons, and every other punctuation mark? This is obviously not my expertise and I can't figure out what's wrong, I tried setting MatchCase to True hoping that would possibly fix it, but nothing. Again, I really appreciate your assistance and patience.?

 

[MARK858]

Can you post your code directly in the thread, then select it and click the VBA icon in the Reply window, then we can copy the code and have a look at it.

 

[loribear180]

Of course! The code is below, I was reading through other threads and some of them mention a character limit of 255, my paragraph (with spaces) currently stands at 443 characters In case that's what's causing the issue.

Sub OpenCleanFill()
Dim WB, OpenWB As Workbook
Dim FileToOpen As String

Set WB = ActiveWorkbook

FileToOpen = Application.GetOpenFilename(Title:="Select File to Open where data will be added", _
                 FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen = "False" Then Exit Sub
Set OpenWB = Workbooks.Open(Filename:=FileToOpen, ReadOnly:=False)

With OpenWB.Sheets("Sheet2")

    Rows("1:7").Delete Shift:=xlUp
    Columns("B:J").Delete Shift:=xlToLeft
    Columns("C:R").Delete Shift:=xlToLeft
    Columns("E:Q").Delete Shift:=xlToLeft
    Columns("C:C").Select
Selection.Replace What:="Disabled Veteran. The individual is defined as a veteran (1) who is classified as disabled by the U.S. Department of Veterans Affairs or the branch of the service in which the veteran served and (2) whose disability is service-connected", Replacement:="Y", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="None of the above", Replacement:="N", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="Decline to Respond", Replacement:="N", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="Surviving Spouse of a Veteran. The individual is a surviving spouse of a veteran who has not remarried.", Replacement:="Y", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="Orphan of a Veteran. The individual is an orphan of a veteran if the veteran was killed on active duty.", Replacement:="Y", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="Veteran. The individual is defined as an individual who has served in (and has been honorably discharged from) the following branches of service: The U.S. Army, Navy, Air Force, Marine Corps, or Coast Guard or the U.S. Public Health; Service under Title 42, United States Code, Section 201; The Texas Military Forces as defined by Texas Government Code, Section 437.001; or an auxiliary service of one of the branches of the U.S. Armed Forces.", Replacement:="Y", LookAt:=xlPart,
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("D:D").Select
    Selection.Replace What:="Yes", Replacement:="Y", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="No", Replacement:="N", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Rows("1:1").AutoFilter
    ActiveSheet.AutoFilter.Sort.SortFields.Clear
    ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range _
        ("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .Apply
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    End With
    
    Range(Cells(2, 1), Cells(Cells(.Rows.Count, 1).End(xlUp).Row, 5)).Copy
End With

WB.Sheets("MATRIX").[B28].PasteSpecial xlPasteValues
Application.DisplayAlerts = False
OpenWB.Close True
Application.DisplayAlerts = True
End Sub

 

[JoeMo]

I believe the character limit is 255, but exceeding that generates a Run-Time error 13: Type Mismatch, not the Syntax error you're seeing. Have you tried shortening the string to less than 256 characters to see if you still get that message?

 

[Fluff]

You are getting the error, because you do not have a line continuation at the end of line.
However as you have noted & JoeMo has mentioned, you are limited to 255 characters.

 

[loribear180]

I believe the character limit is 255, but exceeding that generates a Run-Time error 13: Type Mismatch, not the Syntax error you're seeing. Have you tried shortening the string to less than 256 characters to see if you still get that message?

Hi JoeMo! Unfortunately the string comes directly from an export, I'm trying to avoid manipulating the export and doing a simple import (which is not so simple to me ?).

 

[JoeMo]

Hi JoeMo! Unfortunately the string comes directly from an export, I'm trying to avoid manipulating the export and doing a simple import (which is not so simple to me ?).

I wasn't trying to suggest trying a shorter string as a solution. Just as a test to see if it still generates a syntax error message.

 

[loribear180]

I wasn't trying to suggest trying a shorter string as a solution. Just as a test to see if it still generates a syntax error message.

Oh! I haven't. But I do have another string with 236 characters and that one works without issue. It appears I am limited to the 255.

 

[Joe4]

Oh! I haven't. But I do have another string with 236 characters and that one works without issue. It appears I am limited to the 255.

Have you seen fluff's reply?
He has identified your issue!

 

[loribear180]

You are getting the error, because you do not have a line continuation at the end of line.
However as you have noted & JoeMo has mentioned, you are limited to 255 characters.

Hi Fluff, is there a work around to replacing large paragraphs? I've been working on formatting a spreadsheet and creating a simple way to import a mess of an export, the rest of my colleagues are even more technically challenged than I, they won't be able to manipulate a raw export. That is why I was trying to simplify things to just Y or N. I'm open to suggestions and researching what else I could do.