Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2005

    Question Unanswered: 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.


  2. #2
    Join Date
    Feb 2004
    Quote Originally Posted by dpt
    thanks in advance, Awaiting some quick replies.

    Looks like the problem is in the Replace function. See the Replace function in Excel Help.
    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),"@@@")


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts