Something in text is killing my macro

mayday1

Board Regular
Joined
Oct 5, 2007
Messages
241
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?
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
The constant vbLf is a line feed character.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe some sample data AND the macro might help ??
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
what about this?
Code:
'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

Board Regular
Joined
Oct 5, 2007
Messages
241
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?
Code:
'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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,914
Messages
5,598,840
Members
414,261
Latest member
KatieBsc

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
Top