Replace C/R with @@@
 

Hi all,

In my excel sheet in column A1 I have to allow users to input 1000
chrs.They can give a C/R and enter upto 10 lines in one cell.After a
click of button the cell contents will saved in a CSV file. When it is
saving into the csv file the carriages returns should be replaced with
the @@@. I am using the below code.
Cells.Replace What:=Chr(10), Replacement:="@@@", LookAt:=xlPart,
SearchOrder:= _
xlByRows, MatchCase:=False

The code works fine. but the problem is the C/R will not be replaced
with @@@ if the number of chrs exceed 893. After 893 chrs there will be
a carriage return in the .csv file which the user has entered in the
excel sheet.
So atlast the total max chars with @@@ is 911.

Is there a way to resolve this. Kindly give me your invaluable
suggestion to help me.

thanks in advance, Awaiting some quick replies.

-dpt


--
dpt
------------------------------------------------------------------------
dpt's Profile: http://www.excelforum.com/member.php...o&userid=25861
View this thread: http://www.excelforum.com/showthread...hreadid=482142

Replace C/R with @@@
 

the alternative would be to loop through the cells, then loop through the
string in the cell and make the replacement for each chr(10) found.

--
Regards,
Tom Ogilvy


"dpt" wrote in message
...

Hi all,

In my excel sheet in column A1 I have to allow users to input 1000
chrs.They can give a C/R and enter upto 10 lines in one cell.After a
click of button the cell contents will saved in a CSV file. When it is
saving into the csv file the carriages returns should be replaced with
the @@@. I am using the below code.
Cells.Replace What:=Chr(10), Replacement:="@@@", LookAt:=xlPart,
SearchOrder:= _
xlByRows, MatchCase:=False

The code works fine. but the problem is the C/R will not be replaced
with @@@ if the number of chrs exceed 893. After 893 chrs there will be
a carriage return in the .csv file which the user has entered in the
excel sheet.
So atlast the total max chars with @@@ is 911.

Is there a way to resolve this. Kindly give me your invaluable
suggestion to help me.

thanks in advance, Awaiting some quick replies.

-dpt


--
dpt
------------------------------------------------------------------------
dpt's Profile:
http://www.excelforum.com/member.php...o&userid=25861
View this thread: http://www.excelforum.com/showthread...hreadid=482142

Replace C/R with @@@
 

Interesting: sounds like a limitation of the .Replace method in terms of max
# of chars it looks at. Have not heard of or run across this before and
don't see anything in documentation about it. But I can't think of any way
to make cells.replace work if this is so. You may need instead to iterate
through the cells yourself and use the Replace function to replace the CR
with @@@ as in the example below (note: not tested!):

Dim FoundCell as Cell, FirstFound as String

With Range("A:A")
Set FoundCell = .Find(Chr(10), lookin:=xlValues)
If Not FoundCell Is Nothing Then
FirstFound = FoundCell.Address
Do
FoundCell.Value = Replace(FoundCell.Value, Chr(10), "@@@")
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing And FoundCell.Address <
FirstFound
End If
End With
--
- K Dales


"dpt" wrote:


Hi all,

In my excel sheet in column A1 I have to allow users to input 1000
chrs.They can give a C/R and enter upto 10 lines in one cell.After a
click of button the cell contents will saved in a CSV file. When it is
saving into the csv file the carriages returns should be replaced with
the @@@. I am using the below code.
Cells.Replace What:=Chr(10), Replacement:="@@@", LookAt:=xlPart,
SearchOrder:= _
xlByRows, MatchCase:=False

The code works fine. but the problem is the C/R will not be replaced
with @@@ if the number of chrs exceed 893. After 893 chrs there will be
a carriage return in the .csv file which the user has entered in the
excel sheet.
So atlast the total max chars with @@@ is 911.

Is there a way to resolve this. Kindly give me your invaluable
suggestion to help me.

thanks in advance, Awaiting some quick replies.

-dpt


--
dpt
------------------------------------------------------------------------
dpt's Profile: http://www.excelforum.com/member.php...o&userid=25861
View this thread: http://www.excelforum.com/showthread...hreadid=482142

Replace C/R with @@@
 

Maybe you can try this:

http://groups.google.co.uk/groups?th...apeXSPAM.c om

It tries to do the mass change. Then comes back to get the cells that failed.

You'll have to change the strings ("1st Qtr" and "2nd Qtr") and the range to
inspect (the code did the whole worksheet--not just column A.)



dpt wrote:

Hi all,

In my excel sheet in column A1 I have to allow users to input 1000
chrs.They can give a C/R and enter upto 10 lines in one cell.After a
click of button the cell contents will saved in a CSV file. When it is
saving into the csv file the carriages returns should be replaced with
the @@@. I am using the below code.
Cells.Replace What:=Chr(10), Replacement:="@@@", LookAt:=xlPart,
SearchOrder:= _
xlByRows, MatchCase:=False

The code works fine. but the problem is the C/R will not be replaced
with @@@ if the number of chrs exceed 893. After 893 chrs there will be
a carriage return in the .csv file which the user has entered in the
excel sheet.
So atlast the total max chars with @@@ is 911.

Is there a way to resolve this. Kindly give me your invaluable
suggestion to help me.

thanks in advance, Awaiting some quick replies.

-dpt

--
dpt
------------------------------------------------------------------------
dpt's Profile: http://www.excelforum.com/member.php...o&userid=25861
View this thread: http://www.excelforum.com/showthread...hreadid=482142

--

Dave Peterson

Replace C/R with @@@
 

It tries to do the mass change.

Sure there isn't a typo the

.Replace what:=BeforeStr, Replacement:=BeforeStr, _
lookat:=xlPart, SearchOrder:=xlByRows

Replacing BeforeStr with BeforeStr seems like a lot of work with negligible
results. <g

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
Maybe you can try this:


http://groups.google.co.uk/groups?th...apeXSPAM.c om

It tries to do the mass change. Then comes back to get the cells that
failed.

You'll have to change the strings ("1st Qtr" and "2nd Qtr") and the range
to
inspect (the code did the whole worksheet--not just column A.)

Replace C/R with @@@
 

Ah, that was probably the 15 version of the same stuff--modified every so
slightly each time. (and ever so slightly is pronounced wrong!)

Thanks for the correction.

Next time, it'll be the 16th time (maybe with a different error!).

Tom Ogilvy wrote:

It tries to do the mass change.

Sure there isn't a typo the

.Replace what:=BeforeStr, Replacement:=BeforeStr, _
lookat:=xlPart, SearchOrder:=xlByRows

Replacing BeforeStr with BeforeStr seems like a lot of work with negligible
results. <g

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
Maybe you can try this:


http://groups.google.co.uk/groups?th...apeXSPAM.c om

It tries to do the mass change. Then comes back to get the cells that
failed.

You'll have to change the strings ("1st Qtr" and "2nd Qtr") and the range
to
inspect (the code did the whole worksheet--not just column A.)




--

Dave Peterson

Replace C/R with @@@
 

hi all,

Thanks a lot for your time and inputs, the problem has finally been
resolved. As some one said we need to iterate using loops.Cells.Replace
function has some limitation take note only 893 chars entered in a cell
can be replaced incase the replace char is @@@

---dpt


--
dpt
------------------------------------------------------------------------
dpt's Profile: http://www.excelforum.com/member.php...o&userid=25861
View this thread: http://www.excelforum.com/showthread...hreadid=482142