How to put line break in Concat fuction in VBA

Question

I have very specific issue, I am trying to concat values to string using line break, I tried all possibilities, nothing works. I tried vbnewline, vbLf, CHR(10).

Range("M2:M" & AfterDuplastRow).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
Range("M1").Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = _
"=IF(F2=""" & meal & """," & _
"IF(F1<>F2,B2,Concat(M1,CHR(10),B2)),"""")"

also I tried like this

ActiveCell.Formula = _
"=IF(F2=""" & meal & """," & _
"IF(F1<>F2,B2,Concat(M1," & CHR(10) & ",B2)),"""")"

Thank you for your help

From memory you need a combination of vbCR & vbLF, vbNewLine is the same as vbCR & vbLF. — SPlatten, Nov 23 '18 at 11:16

score 0 · Answer 1 · answered Nov 23 '18 at 11:13

0

Use constant vbNewLine:

ActiveCell.Formula = "=IF(F2=""" & meal & """,IF(F1<>F2,B2,Concat(M1," & vbNewLine & ",B2)),"""")"

answered Nov 23 '18 at 11:13

LS_ᴅᴇᴠ

10,823
1
23
46

Hello, unfortunatell, it doesn't help. after running code run time error 1004 appears: application-defined or object defined error – Juraj Kopecky Nov 23 '18 at 11:25

score 0 · Answer 2 · answered Nov 23 '18 at 11:23

This sounds like it may be an XY Problem...

Even if you include a New Line (vbNewLine), a Carriage Return (vbCr), a Line Feed (vbLf), or a Carriage Return and a Line Feed (vbCrLf), it will only be visible in a cell if Wrap Text is turned on for that cell.

As such, try this simple change:

Range("M2:M" & AfterDuplastRow).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
Range("M1").Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=IF(F2=""" & meal & """," & _
    "IF(F1<>F2,B2,Concat(M1,CHR(10),B2)),"""")"
Range("M1").WrapText = True 'This line should fix your issue

(Also, you may want to read up on How to avoid using Select in Excel VBA)

How to put line break in Concat fuction in VBA

2 Answers2