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?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
The constant vbLf is a line feed character.
 
Upvote 0
Maybe some sample data AND the macro might help ??
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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
Back
Top