Problem with Remove LF/CR Macro

J

Jeff Granger

Please excuse my ignorance.
I'm trying to use the following macro to remove carriage returns ( and other
characters) from a list of addresses.

Carriage Return & Line Feed Clean-up Macro

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in CellView?

myGoodChars = Array("","|") '<--the new characters?

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

The problem is that when I try and edit the macro to put specific cell
references instead of "Array" and run the macro I get a VB Compile Error and
Syntax error.

eg.
Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

" myBadChars = A3:A20(Chr(10), Chr(13)) '<--What showed up in CellView?"

" myGoodChars = B3:B20("""",""|"") '<--the new characters?"

If UBound(myGoodChars) <> UBound(myBadChars) Then
" MsgBox ""Design error!"""
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
" ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _"
" Replacement:=myGoodChars(iCtr), _"
" LookAt:=xlPart, SearchOrder:=xlByRows, _"
MatchCase:=False
Next iCtr

End Sub

Can anyone tell me what I'm doing wrong?

Jeff
 
D

Dave Peterson

What are you trying to do?

Do you have a table (two columns) of data where the first column was the bad
character and the second column was the replacement character, you could pick up
those values in the code.

I'd put that table on a different sheet and then do something like:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Range
Dim myCell As Range
Dim iCtr As Long

With Worksheets("myTableSheetNameGoesHere")
'with headers in A1 and B1
Set myBadChars = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myBadChars.Cells
ActiveSheet.Cells.Replace What:=myCell.Value, _
Replacement:=myCell.Offset(0, 1).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next myCell

End Sub

And make sure the sheet to fix is active. You wouldn't want to "fix" the table.
 
D

Dave Peterson

If you turn formatting to Wrap Text
(Format|cells|alignment tab|check wrap text)

Does the square disappear and you end up with multiple lines in the cell?

If yes, then you can select the column
data|text to columns
delimited
by other
and type ctrl-j
in that other box

Then finish up.

If that doesn't help, what do you get back from the CellView addin for that
character?

And change these lines in the original code to something like:

myBadChars = Array(Chr(10))
myGoodChars = Array("|")




Jeff said:
Dave

I believe it was you who kindly provided me with the macro some months ago.
At the time I got it to work. Now I can't.

I have a column of data which consists of addresses which have been exported
from another database, and as a result the elements of the address are
separated by "square" characters, e.g. 21 Acacia Avenue[]Birmingham[]UK.

I'm trying to split out each element into a separate column.

I'm using CellView to work out what the [] character actually represents,
then hopefully using the macro to change the []characters into commas. or
some other character I can then split out using Text to Columns.

Regards

Jeff

Dave Peterson said:
What are you trying to do?

Do you have a table (two columns) of data where the first column was the
bad
character and the second column was the replacement character, you could
pick up
those values in the code.

I'd put that table on a different sheet and then do something like:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Range
Dim myCell As Range
Dim iCtr As Long

With Worksheets("myTableSheetNameGoesHere")
'with headers in A1 and B1
Set myBadChars = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myBadChars.Cells
ActiveSheet.Cells.Replace What:=myCell.Value, _
Replacement:=myCell.Offset(0, 1).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next myCell

End Sub

And make sure the sheet to fix is active. You wouldn't want to "fix" the
table.
Click to expand...
Click to expand...
 
J

Jeff Granger

Dave

Thanks for persevering. I'm probably being exceptionally thick here, but
referring back to the original macro, am I correct in assuming I am supposed
to change the first word "Array" to "A1:A10" or wherever the original list
is, and the second word "Array" to "B1:B10", or wherever I want the same
list with the substituted characters?

It's just that when I do this in the Module some lines of the macro get
double quotes (see original post) and go red. So I'm assuming I'm doing
something wrong somewhere.

I'd like to get it to work because I've got other lists with similar, but
different characters, so if I can get it to work for this one I can get it
to work for all.

Jeff


Dave Peterson said:
If you turn formatting to Wrap Text
(Format|cells|alignment tab|check wrap text)

Does the square disappear and you end up with multiple lines in the cell?

If yes, then you can select the column
data|text to columns
delimited
by other
and type ctrl-j
in that other box

Then finish up.

If that doesn't help, what do you get back from the CellView addin for
that
character?

And change these lines in the original code to something like:

myBadChars = Array(Chr(10))
myGoodChars = Array("|")




Jeff said:
Dave

I believe it was you who kindly provided me with the macro some months
ago.
At the time I got it to work. Now I can't.

I have a column of data which consists of addresses which have been
exported
from another database, and as a result the elements of the address are
separated by "square" characters, e.g. 21 Acacia Avenue[]Birmingham[]UK.

I'm trying to split out each element into a separate column.

I'm using CellView to work out what the [] character actually represents,
then hopefully using the macro to change the []characters into commas. or
some other character I can then split out using Text to Columns.

Regards

Jeff

Dave Peterson said:
What are you trying to do?

Do you have a table (two columns) of data where the first column was
the
bad
character and the second column was the replacement character, you
could
pick up
those values in the code.

I'd put that table on a different sheet and then do something like:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Range
Dim myCell As Range
Dim iCtr As Long

With Worksheets("myTableSheetNameGoesHere")
'with headers in A1 and B1
Set myBadChars = .Range("a2", .Cells(.Rows.Count,
"A").End(xlUp))
End With

For Each myCell In myBadChars.Cells
ActiveSheet.Cells.Replace What:=myCell.Value, _
Replacement:=myCell.Offset(0, 1).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next myCell

End Sub

And make sure the sheet to fix is active. You wouldn't want to "fix"
the
table.

Jeff Granger wrote:

Please excuse my ignorance.
I'm trying to use the following macro to remove carriage returns ( and
other
characters) from a list of addresses.

Carriage Return & Line Feed Clean-up Macro

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in
CellView?

myGoodChars = Array("","|") '<--the new characters?

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

The problem is that when I try and edit the macro to put specific cell
references instead of "Array" and run the macro I get a VB Compile
Error
and
Syntax error.

eg.
Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

" myBadChars = A3:A20(Chr(10), Chr(13)) '<--What showed up in
CellView?"

" myGoodChars = B3:B20("""",""|"") '<--the new characters?"

If UBound(myGoodChars) <> UBound(myBadChars) Then
" MsgBox ""Design error!"""
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
" ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _"
" Replacement:=myGoodChars(iCtr), _"
" LookAt:=xlPart, SearchOrder:=xlByRows, _"
MatchCase:=False
Next iCtr

End Sub

Can anyone tell me what I'm doing wrong?

Jeff
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Nope. Those arrays are holders for the characters that should be modifed (and
the modifying characters). They're not for the range you want to fix.

If you want to fix a certain area, you can select it first and use:

For iCtr = LBound(myBadChars) To UBound(myBadChars)
Selection.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

or you could specify exactly what range you want fixed:

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.range("a:a").Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

It's like doing an edit|replace. The "what's" are in the myBadChars array and
the "with what's" are in the myGoodChars array.


Jeff said:
Dave

Thanks for persevering. I'm probably being exceptionally thick here, but
referring back to the original macro, am I correct in assuming I am supposed
to change the first word "Array" to "A1:A10" or wherever the original list
is, and the second word "Array" to "B1:B10", or wherever I want the same
list with the substituted characters?

It's just that when I do this in the Module some lines of the macro get
double quotes (see original post) and go red. So I'm assuming I'm doing
something wrong somewhere.

I'd like to get it to work because I've got other lists with similar, but
different characters, so if I can get it to work for this one I can get it
to work for all.

Jeff

Dave Peterson said:
If you turn formatting to Wrap Text
(Format|cells|alignment tab|check wrap text)

Does the square disappear and you end up with multiple lines in the cell?

If yes, then you can select the column
data|text to columns
delimited
by other
and type ctrl-j
in that other box

Then finish up.

If that doesn't help, what do you get back from the CellView addin for
that
character?

And change these lines in the original code to something like:

myBadChars = Array(Chr(10))
myGoodChars = Array("|")




Jeff said:
Dave

I believe it was you who kindly provided me with the macro some months
ago.
At the time I got it to work. Now I can't.

I have a column of data which consists of addresses which have been
exported
from another database, and as a result the elements of the address are
separated by "square" characters, e.g. 21 Acacia Avenue[]Birmingham[]UK.

I'm trying to split out each element into a separate column.

I'm using CellView to work out what the [] character actually represents,
then hopefully using the macro to change the []characters into commas. or
some other character I can then split out using Text to Columns.

Regards

Jeff

What are you trying to do?

Do you have a table (two columns) of data where the first column was
the
bad
character and the second column was the replacement character, you
could
pick up
those values in the code.

I'd put that table on a different sheet and then do something like:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Range
Dim myCell As Range
Dim iCtr As Long

With Worksheets("myTableSheetNameGoesHere")
'with headers in A1 and B1
Set myBadChars = .Range("a2", .Cells(.Rows.Count,
"A").End(xlUp))
End With

For Each myCell In myBadChars.Cells
ActiveSheet.Cells.Replace What:=myCell.Value, _
Replacement:=myCell.Offset(0, 1).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next myCell

End Sub

And make sure the sheet to fix is active. You wouldn't want to "fix"
the
table.

Jeff Granger wrote:

Please excuse my ignorance.
I'm trying to use the following macro to remove carriage returns ( and
other
characters) from a list of addresses.

Carriage Return & Line Feed Clean-up Macro

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in
CellView?

myGoodChars = Array("","|") '<--the new characters?

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

The problem is that when I try and edit the macro to put specific cell
references instead of "Array" and run the macro I get a VB Compile
Error
and
Syntax error.

eg.
Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

" myBadChars = A3:A20(Chr(10), Chr(13)) '<--What showed up in
CellView?"

" myGoodChars = B3:B20("""",""|"") '<--the new characters?"

If UBound(myGoodChars) <> UBound(myBadChars) Then
" MsgBox ""Design error!"""
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
" ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _"
" Replacement:=myGoodChars(iCtr), _"
" LookAt:=xlPart, SearchOrder:=xlByRows, _"
MatchCase:=False
Next iCtr

End Sub

Can anyone tell me what I'm doing wrong?

Jeff
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I find and replace a symbol in an excel spreadsheet 0
Resize columns wider for pick list 5
Word Macro for multiple searches, incl. in footnotes 2
macro to select a range from a set cell to a specified cell 6
Array code only works on selected sheet 14
Find text, replace it but now I need to change the height of allpreceding/following text 10
delete code keeps going and coing 5
deleting rows based on criteria 8

Top