Line Break in VB scripting is not working when multiple rows combined into a cell

Question

I am trying to concatenate values of an array(data2) values to an variable (oldValue) and pasting that in a cell of an workbook. I used chr(13) as line break while doing concatenation.It is perfectly working when am trying to display the concatenated Value(OldValue) in Message Box. But when am trying to assign to a cell value, the Line breaks is not working. Is there any option to make the Line breaks work inside the cell?

For i = 2 to iMax
        fc=data1(i,4)
        Dim oldValue
        oldValue="" 
        For k = 1 to fc
            oldValue = oldValue & chr(13) & data2(i,k,1)&"-"&data2(i,k,2)&chr(13)
            'objExcel1.Cells(i,j).Value = oldValue & chr(13) & data2(i,k,1)&"-"&data2(i,k,2)&chr(13)
        Next            
        For j = 1 to 6
        If (j=5) then               
            Msgbox(oldValue)
            objExcel1.Cells(i,j).Value = oldValue           
        else
            objExcel1.Cells(i,j).Value = data1(i,j) 
        End If
    Next
Next

Perfect!!!.It worked .. Thanks BruceWayne:) – suresh LakshmiNarayanan Oct 18 '18 at 18:54 — suresh LakshmiNarayanan, Oct 18 '18 at 18:54

score 2 · Answer 1 · answered Oct 18 '18 at 18:51

2

To force a return in the cell, switch CHR(13) with vbCrLf.

answered Oct 18 '18 at 18:51

BruceWayne

22,923
15
65
110

Line Break in VB scripting is not working when multiple rows combined into a cell

1 Answers1