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

    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.

    -dpt

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by dpt
    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
    ~

    Bill

Posting Permissions

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