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, 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.

"Bob Phillips" wrote:

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)

"xgirl" wrote in message
...
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?

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)

"xgirl" wrote in message
...
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.

"Bob Phillips" wrote:

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)

"xgirl" wrote in message
...
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?

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

"xgirl" wrote:

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.

"Bob Phillips" wrote:

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)

"xgirl" wrote in message
...
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?

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.

"xgirl" wrote:

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?

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



"xgirl" wrote in message
...
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.

"Bob Phillips" wrote:

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)

"xgirl" wrote in message
...
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?

char(10) is same as Alt + Enter


Gord Dibben MS Excel MVP

On Wed, 4 Oct 2006 17:00:03 -0700, JLGWhiz
wrote:

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.

"xgirl" wrote:

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?