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

Looks like the problem is in the Replace function. See the Replace function in Excel Help.
Syntax:
Replace(expression, find, replace[, start[, count[, compare]]])

You use it this way with VB and most likely use a for loop to perform Replace on each cell.

For i = 2 to lastRow

Cells(i, 1) = Replace(Cell(i, 1).Value,Chr(10),"@@@")

Next