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?

Try using:

Chr(13)

Regards,
Paul

"xgirl" <(E-Mail Removed)> wrote in message
news:3E12B640-1448-4AD2-9C40-(E-Mail Removed)...
> 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?

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" <(E-Mail Removed)> wrote in message
news:3E12B640-1448-4AD2-9C40-(E-Mail Removed)...
> 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?

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

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?

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" <(E-Mail Removed)> wrote in message
> news:3E12B640-1448-4AD2-9C40-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:FA8E01E1-11C1-406A-AAFB-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:3E12B640-1448-4AD2-9C40-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > news:3E12B640-1448-4AD2-9C40-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
news:FA8E01E1-11C1-406A-AAFB-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:3E12B640-1448-4AD2-9C40-(E-Mail Removed)...
>> > 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 <(E-Mail Removed)>
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?