CODEWITHSUNDEEP
excelvbscriptvba
Claus
Claus

Reputation: 550

Inserting a line return in Excel VBscript / VBA

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 enter image description here

This is the desired output enter image description here

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.

Upvotes: 3

Views: 9404

Answers (2)

John Coleman
John Coleman

Reputation: 51998

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.

Upvotes: 4

JNevill
JNevill

Reputation: 50119

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))

Upvotes: 6

Related Questions