Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: weird character in cell using vbCrLf

  1. #1
    Board Regular
    Join Date
    Aug 2009
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default weird character in cell using vbCrLf

    In the following code snippet;

    strFill = "Query for" & strCommName
    strFill = strFill & vbCrLf & strPitHrs
    strFill = strFill & vbCrLf & "This query has a rating of"

    The cell output is formatted this way;
    "Query for SP500
    9:30-4:15 EST
    This query has a rating of"


    And thats how the text shows if you copy and paste it BUT in the cell there is a square box at the end of each of the first two lines. Is the vbCrLf causing that? If so what should I be using instead so the text in the cell shows exactly like the bolded text above
    Now using Excel 2010 and Win 7
    **************
    Thomas Donino
    BATL Management LP

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,868
    Post Thanks / Like
    Mentioned
    55 Post(s)
    Tagged
    5 Thread(s)

    Default Re: weird character in cell using vbCrLf

    Thomas

    If you select the cell and goto Format>Cells and check Word Wrap on the Alignment tab the square will go away.

    The text should also look like you want it, but the vbCrLf will still be there.
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    Aug 2009
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: weird character in cell using vbCrLf

    and to set that via vba?
    .wraptext = true?
    Now using Excel 2010 and Win 7
    **************
    Thomas Donino
    BATL Management LP

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,868
    Post Thanks / Like
    Mentioned
    55 Post(s)
    Tagged
    5 Thread(s)

    Default Re: weird character in cell using vbCrLf

    Yep, well that's what I got when I turned on the macro recorder and did it manually.
    If posting code please use code tags.

  5. #5
    Board Regular
    Join Date
    Aug 2009
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: weird character in cell using vbCrLf

    Setting the wrap text to checked does not remove the square, which by the way is a square with a little ? in it
    Now using Excel 2010 and Win 7
    **************
    Thomas Donino
    BATL Management LP

  6. #6
    Board Regular
    Join Date
    Aug 2009
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: weird character in cell using vbCrLf

    That square is coming from the text grabbed by the vlookups, as that is the text it is after each time. I just added 3 other vlookup grabs and each has the square after it. Why is the text returned from the vlookup function adding the square?
    Now using Excel 2010 and Win 7
    **************
    Thomas Donino
    BATL Management LP

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,868
    Post Thanks / Like
    Mentioned
    55 Post(s)
    Tagged
    5 Thread(s)

    Default Re: weird character in cell using vbCrLf

    Thomas

    Probably because the cell/range the vlookups are returning the values to aren't formatted to wrap text.

    VLOOKUP returns only the value, not the formatting.
    If posting code please use code tags.

  8. #8
    Board Regular
    Join Date
    Aug 2009
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: weird character in cell using vbCrLf

    Nope, that didnt work either. I reset the formatting in the table to wrap text checked and running my sub still put the cell text with the little square
    Now using Excel 2010 and Win 7
    **************
    Thomas Donino
    BATL Management LP

  9. #9
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,868
    Post Thanks / Like
    Mentioned
    55 Post(s)
    Tagged
    5 Thread(s)

    Default Re: weird character in cell using vbCrLf

    Try formatting after you put the value in.

    I've no idea how you are putting the values in but you might be wiping out any existing formatting if you are just doing some straightforward copying.
    If posting code please use code tags.

  10. #10
    Board Regular
    Join Date
    Aug 2009
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: weird character in cell using vbCrLf

    The correct answer is the vbCrLf needed to be changed to vbLf. The vbCrLf is what was adding the little square character
    Now using Excel 2010 and Win 7
    **************
    Thomas Donino
    BATL Management LP

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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