Something in text is killing my macro

[mayday1]

The macro reads in about 30,000 records does some simple, very simple, editing and that's it. One column sometimes has missing data after the macro runs. Sometimes many of the rows are affected, sometime all of them.

I'm pretty sure the problem is something in that column. I've tried my macro with just a few rows and haven't been able to narrow down the culprit yet. I've run it with just 1000 rows and caused the error but I can't see which row(s) have something wrong with them.

If I included in my macro some commands that take focus of that column and do a mass change from the problem character to a space I'd be good. I've tried manually doing this with ~@$%^&*, etc with no luck. How do I do a replace on line breaks and other special characters that might be in the text?

 

[shg]

The constant vbLf is a line feed character.

 

[Michael M]

Maybe some sample data AND the macro might help ??

 

[Fazza]

what about this?

'this is the function
Sub ChangeSomeCharactersInColumn(ByVal InColumnNumber As Long, ByRef ChangeTheseArrayItems As Variant, ChangeToThisCharacter As String)


    Dim i As Long
    
    For i = LBound(ChangeTheseArrayItems) To UBound(ChangeTheseArrayItems)
        Columns(InColumnNumber).Replace What:=ChangeTheseArrayItems(i), Replacement:=ChangeToThisCharacter, LookAt:=xlPart
    Next i
    
End Sub




Sub UseItLikeThis()


    Call ChangeSomeCharactersInColumn(InColumnNumber:=1, ChangeTheseArrayItems:=Array("!", "@", "#", Chr(13), Chr(145)), ChangeToThisCharacter:=" ")


End Sub

 

[mayday1]

Yeah I'm not that good with macros but this looks like a macro doing what I've been trying to do manually - replacing different characters with a space. Is that right? This would work if I knew what the offending character was. That's my problem. I could record a macro and replace all of those characters in the column if I knew what to replace. And from the earlier post, I'm not sure what a vbLf is.

How about this: If I were to do a find/replace manually, what character or string or whatever would I maybe need to replace with a space and how would I put that character in the Find field. Like in Word I know I can do a find on all ^P to find line breaks. I think my problem might be things like that. The column contains free form text - sometimes with obvious line breaks, sometimes the text is copied/pasted from other screens with who knows what formatting.

what about this?

'this is the function
Sub ChangeSomeCharactersInColumn(ByVal InColumnNumber As Long, ByRef ChangeTheseArrayItems As Variant, ChangeToThisCharacter As String)


    Dim i As Long
    
    For i = LBound(ChangeTheseArrayItems) To UBound(ChangeTheseArrayItems)
        Columns(InColumnNumber).Replace What:=ChangeTheseArrayItems(i), Replacement:=ChangeToThisCharacter, LookAt:=xlPart
    Next i
    
End Sub




Sub UseItLikeThis()


    Call ChangeSomeCharactersInColumn(InColumnNumber:=1, ChangeTheseArrayItems:=Array("!", "@", "#", Chr(13), Chr(145)), ChangeToThisCharacter:=" ")


End Sub

 

[Fazza]

as my code shows, characters can be defined as familiar "a", "b", "C", "$", "%" and also by Chr(the ASCII code for the character)
so uncommon/tricky control/whatever characters can be entered as Chr(13), Chr(65), etc. These are decimal values, you could use hex or whatever

how about this.
get a full column of your data
have some code go through and identify all the distinct different characters
maybe print them in a column in a new worksheet, and with their ASCII values next to them
so likely the result could return letters A through Z and the digits and all the unusual characters

then look at the odd ones, the linefeeds, unusual spaces/dashes/whatever
select the ones you want removed & put their codes in the array in the above posted function