Inserting a line return in Excel VBscript / VBA

Question

Using Excel v16

I'm trying to format text in a cell by replacing a character like tilde ~ with a carriage return whereby the finished result would be multiple lines. This is actually a merged cell of multiple rows and columns as shown below

This is the input

This is the desired output

Here is the script I'm using in my macro

Sub FindReplaceAll()
    Sheets("Template").Select
    Range("A34").Select

    iStr = ActiveCell.Value

    For i = 1 To Len(iStr)
        If Mid(iStr, i, 1) = "~" Then
            rtStr = rtStr + vbCr
    Else
        rtStr = rtStr + Mid(iStr, i, 1)
    End If

    Next i

    ActiveCell.Value = rtStr

End Sub

Unfortunately all I get is same line with the tilde removed The cell is formatted with the wrap text. Not sure where to go from here.

The entirety of your subroutine can be replaced with just a single line: Sheets("template").Range("A34").Value = Replacc(Sheets("template").Range("A34").value, "~", chr(10)) That will set that cell's value to itself, but replacing the tildes with a carriage return. As mentioned in the answer below you can use vbcrlf for a carriage return/line feed or chr(10) & chr(11) if you want to split hairs. — JNevill
– JNevill, Commented Mar 17, 2017 at 13:48
Wow. Worked. Post it as the answer so I can select it as best response — Claus
– Claus, Commented Mar 17, 2017 at 13:50

JNevill · Accepted Answer · 2017-03-17 13:54:51Z

6

The entirety of your subroutine can be replaced with just a single line:

sub findReplaceAll()
    Sheets("template").Range("A34").Value = Replace(Sheets("template").Range("A34").value, "~", chr(13)) 
end sub

That will set that cell's value to itself, but replacing the tildes with a carriage return. As mentioned in @johncoleman's answer you can use vbcrlf for a carriage return/line feed or chr(13) & chr(10) if you want to split hairs.

This could also be done in a worksheet formula with:

=SUBSTITUTE(A34, "~", CHAR(13))

edited Mar 17, 2017 at 13:54

answered Mar 17, 2017 at 13:51

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stuart Over a year ago

You have a typo, it's Replace, and it's chr(13) for a carriage return.

Claus Over a year ago

One typo I should have mentioned = Replace you have a double cc

JNevill Over a year ago

Thanks both. Updated. I always get those chr() values backwards. Updated the answer fixing the REPLACC, swapped chr(10) with chr(13) so it's a carriage return instead of a line feed.

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

In general, in Windows for newlines you would need vbCrLF rather than vbCr. Multiline strings can be written to an Excel cell using either vbCrLf or simply vbLf. Doing the later corresponds directly with what would happen if you enter such a string from the user interface using Alt+Enter.

Doing this potentially causes problems if you intend to do something else with the string other than display it in a cell, such as write it to a text file or copy it to the clipboard and paste it to a different application. For example, if in cell A1 you type a Alt+Enter b you will see a displayed directly over b, but if you run the following code:

Sub test()
    Dim FSO As Object
    Dim f As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set f = FSO.CreateTextFile("C:/programs/test.txt")
    f.Write Range("A1").Value
    f.Close
End Sub

and then inspect the file in Notepad, the line breaks won't display. For these sorts of reasons, I almost always use vbCrLf when building up multi-line strings in VBA.

Thus -- if you want to simply display the string use either chr(10) or vbLf, but it you might want to later use this string somewhere else, use vbCrLf.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 17, 2017 at 13:48

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

3 Comments

z32a7ul Over a year ago

Wrong. You need vbLf (Chr(10)) in Excel cells, that's also the character inserted when using the UI (Alt+Enter). vbCrLf works only because vbCr does nothing and vbLf does the job.

John Coleman Over a year ago

@z32a7ul This seems to be a general VBA strings vs. Excel cell issue. For general purpose strings in VBA/VB6 and for things like Access VBA, you would use vbCrLf but you are correct that in Excel cells vbLf is enough. That is probably a space-optimization. It does seem to cause copying problems if you want to take such a multiline string in an excel file and write it in a text file, so I guess it depends partially on your purposes.

John Coleman Over a year ago

@z32a7ul By the way -- thanks for the correction. I edited my post to incorporate your point.

Collectives™ on Stack Overflow

Inserting a line return in Excel VBscript / VBA

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related