Combine data in cells

[Guest]

Hi, I'm trying to write a macro that will combine the contents of 2 cells in
to one with a line feed between each.

I tried to use vblf or chr(10) but it doesn't work.

ActiveCell.Formula = "=$n$1&H5" & vbLf & "$0$1&H4"

Any suggestions?

 

[PCLIVE]

Try using:

Chr(13)

Regards,
Paul

 

[Bob Phillips]

With ActiveCell
.Formula = "=R1C14&R[" & 5 - .Row & "]C[" & 8 - .Column & "]&" & _
Chr(10) & "R1C15&R[" & 4 - .Row & "]C[" & 8 - .Column &
"]"
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[acampbell012]

ActiveCell.Value = "=$N$1&H5&" & Chr(10) & "$O$1&H4"

 

[Guest]

Bob, Thank you for your help. However, the formula does not enter a "line
feed" to pull down to the next line. It appears as wrapped text:

1)Incorrect maturity date on TIL2)Incorrect
maturity date on Note

I've also tried chr(13) in place of chr(10) but I get a runtime error when I
use that.

I have the 1) in cell n1 the 2) in cell o1 and text in cells H4 and H5. I
want it to appear as:

1)Incorrect maturity date on TIL
2)Incorrect maturity date on Note

all in the same cell.

Thanks in advance.

With ActiveCell
.Formula = "=R1C14&R[" & 5 - .Row & "]C[" & 8 - .Column & "]&" & _
Chr(10) & "R1C15&R[" & 4 - .Row & "]C[" & 8 - .Column &
"]"
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi, I'm trying to write a macro that will combine the contents of 2 cells in
to one with a line feed between each.

I tried to use vblf or chr(10) but it doesn't work.

ActiveCell.Formula = "=$n$1&H5" & vbLf & "$0$1&H4"

Any suggestions?

 

[Bob Phillips]

Do you mean you want it on two lines?

With ActiveCell
.Formula = "=$n$1&H5"
.Offset(1,0).Formula = "$0$1&H4"
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob, Thank you for your help. However, the formula does not enter a "line
feed" to pull down to the next line. It appears as wrapped text:

1)Incorrect maturity date on TIL2)Incorrect
maturity date on Note

I've also tried chr(13) in place of chr(10) but I get a runtime error when I
use that.

I have the 1) in cell n1 the 2) in cell o1 and text in cells H4 and H5. I
want it to appear as:

1)Incorrect maturity date on TIL
2)Incorrect maturity date on Note

all in the same cell.

Thanks in advance.

With ActiveCell
.Formula = "=R1C14&R[" & 5 - .Row & "]C[" & 8 - .Column & "]&" & _
Chr(10) & "R1C15&R[" & 4 - .Row & "]C[" & 8 - ..Column &
"]"
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi, I'm trying to write a macro that will combine the contents of 2

cells
in

to one with a line feed between each.

I tried to use vblf or chr(10) but it doesn't work.

ActiveCell.Formula = "=$n$1&H5" & vbLf & "$0$1&H4"

Any suggestions?

 

[Guest]

I don't believe you can get a linefeed within a cell. wrapped text is about
the best you will get.

Bob, Thank you for your help. However, the formula does not enter a "line
feed" to pull down to the next line. It appears as wrapped text:

1)Incorrect maturity date on TIL2)Incorrect
maturity date on Note

I've also tried chr(13) in place of chr(10) but I get a runtime error when I
use that.

I have the 1) in cell n1 the 2) in cell o1 and text in cells H4 and H5. I
want it to appear as:

1)Incorrect maturity date on TIL
2)Incorrect maturity date on Note

all in the same cell.

Thanks in advance.

With ActiveCell
.Formula = "=R1C14&R[" & 5 - .Row & "]C[" & 8 - .Column & "]&" & _
Chr(10) & "R1C15&R[" & 4 - .Row & "]C[" & 8 - .Column &
"]"
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi, I'm trying to write a macro that will combine the contents of 2 cells in
to one with a line feed between each.

I tried to use vblf or chr(10) but it doesn't work.

ActiveCell.Formula = "=$n$1&H5" & vbLf & "$0$1&H4"

Any suggestions?

 

[Guest]

You can use Alt + Enter to insert a manual line break, but I am not sure how
that would be coded in VB unless it would be SendKeys.

 

[Tom Ogilvy]

You have to use wrap text, but make the column wide enough so it doesn't
wrap before the char(10)
Sub ABC()
With ActiveCell
.EntireColumn.ColumnWidth = "100"
.Formula = "=N1&H4&char(10)&O1&H4"
.WrapText = True
.EntireColumn.AutoFit
End With
End Sub

worked for me.

--
regards,
Tom Ogilvy

Bob, Thank you for your help. However, the formula does not enter a
"line
feed" to pull down to the next line. It appears as wrapped text:

1)Incorrect maturity date on TIL2)Incorrect
maturity date on Note

I've also tried chr(13) in place of chr(10) but I get a runtime error when
I
use that.

I have the 1) in cell n1 the 2) in cell o1 and text in cells H4 and H5. I
want it to appear as:

1)Incorrect maturity date on TIL
2)Incorrect maturity date on Note

all in the same cell.

Thanks in advance.

With ActiveCell
.Formula = "=R1C14&R[" & 5 - .Row & "]C[" & 8 - .Column & "]&" &
_
Chr(10) & "R1C15&R[" & 4 - .Row & "]C[" & 8 - .Column
&
"]"
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi, I'm trying to write a macro that will combine the contents of 2
cells in
to one with a line feed between each.

I tried to use vblf or chr(10) but it doesn't work.

ActiveCell.Formula = "=$n$1&H5" & vbLf & "$0$1&H4"

Any suggestions?

 

[Gord Dibben]

char(10) is same as Alt + Enter


Gord Dibben MS Excel MVP