software

I have a need to be able to create a funtion/expression that can issue a
'hard carriage return amp; line feed'. On the keyboard this would be 'Alt'
'Enter'. I want to take two (or more) cells and combine them into one cell
with each value on it's own 'line' in the receiving cell.
============================
Receiving Cell Cell 1 Cell 2
Smith John Smith
John
============================
I am trying to create a Membership Directory for print and distribution. I
do having it working by determining the length of the Sending Cell and then
padding the end with 'n' spaces depending on the size of the Receiving Cell
and the length of the Sending Cell. This works but is rather arbitrary
unless using fixed fonts which is not reasonable.
Could I capture this 'special' character by recording a macro (which I will
try)? Or does someone know what this 'special' character might be?

Thanks for any help you might provide,
DannyDont

In excel:
=a1amp;char(10)amp;b1

in VBA:
with activesheet
.range(quot;c1quot;).value = .range(quot;a1quot;).value amp; vblf amp; .range(quot;b1quot;).value
end with

alt-enter = char(10) = vblf = chr(10)

These are line feeds.

Carriage returns are char(13), vbCR, or Chr(13). But don't use them in your
cells. You want alt-enters.

DannyDont wrote:
gt;
gt; I have a need to be able to create a funtion/expression that can issue a
gt; 'hard carriage return amp; line feed'. On the keyboard this would be 'Alt'
gt; 'Enter'. I want to take two (or more) cells and combine them into one cell
gt; with each value on it's own 'line' in the receiving cell.
gt; ============================
gt; Receiving Cell Cell 1 Cell 2
gt; Smith John Smith
gt; John
gt; ============================
gt; I am trying to create a Membership Directory for print and distribution. I
gt; do having it working by determining the length of the Sending Cell and then
gt; padding the end with 'n' spaces depending on the size of the Receiving Cell
gt; and the length of the Sending Cell. This works but is rather arbitrary
gt; unless using fixed fonts which is not reasonable.
gt; Could I capture this 'special' character by recording a macro (which I will
gt; try)? Or does someone know what this 'special' character might be?
gt;
gt; Thanks for any help you might provide,
gt; DannyDont

--

Dave Peterson

Well, I answered my own question by recording a Macro. This macro replaced
the 'Alt' 'Enter' with a Chr(10). Of course this was VB for Excel and
wouldn't you know that the command was different in an Excel Expression.
But, it was an easy find to go from Chr(10) to CHAR(10). Thanks for all your
help. Sometimes it just takes explaining something to others to turn the
light on!!!!

DannyDontquot;DannyDontquot; wrote:

gt; I have a need to be able to create a funtion/expression that can issue a
gt; 'hard carriage return amp; line feed'. On the keyboard this would be 'Alt'
gt; 'Enter'. I want to take two (or more) cells and combine them into one cell
gt; with each value on it's own 'line' in the receiving cell.
gt; ============================
gt; Receiving Cell Cell 1 Cell 2
gt; Smith John Smith
gt; John
gt; ============================
gt; I am trying to create a Membership Directory for print and distribution. I
gt; do having it working by determining the length of the Sending Cell and then
gt; padding the end with 'n' spaces depending on the size of the Receiving Cell
gt; and the length of the Sending Cell. This works but is rather arbitrary
gt; unless using fixed fonts which is not reasonable.
gt; Could I capture this 'special' character by recording a macro (which I will
gt; try)? Or does someone know what this 'special' character might be?
gt;
gt; Thanks for any help you might provide,
gt; DannyDont

Dave,

Thank you very much. As you were providing the answer, I was recording a
Macro and discovering the information that you provided. When using Help in
Excel, I just didn't think about the CHAR worksheet function. Perhaps if I
had asked a question rather than using the index it would have been easier.

Thanks for your RAPID response.

DannyDont
-------------------------------------------------------------------quot;Dave Petersonquot; wrote:

gt; In excel:
gt; =a1amp;char(10)amp;b1
gt;
gt; in VBA:
gt; with activesheet
gt; .range(quot;c1quot;).value = .range(quot;a1quot;).value amp; vblf amp; .range(quot;b1quot;).value
gt; end with
gt;
gt; alt-enter = char(10) = vblf = chr(10)
gt;
gt; These are line feeds.
gt;
gt; Carriage returns are char(13), vbCR, or Chr(13). But don't use them in your
gt; cells. You want alt-enters.
gt;
gt; DannyDont wrote:
gt; gt;
gt; gt; I have a need to be able to create a funtion/expression that can issue a
gt; gt; 'hard carriage return amp; line feed'. On the keyboard this would be 'Alt'
gt; gt; 'Enter'. I want to take two (or more) cells and combine them into one cell
gt; gt; with each value on it's own 'line' in the receiving cell.
gt; gt; ============================
gt; gt; Receiving Cell Cell 1 Cell 2
gt; gt; Smith John Smith
gt; gt; John
gt; gt; ============================
gt; gt; I am trying to create a Membership Directory for print and distribution. I
gt; gt; do having it working by determining the length of the Sending Cell and then
gt; gt; padding the end with 'n' spaces depending on the size of the Receiving Cell
gt; gt; and the length of the Sending Cell. This works but is rather arbitrary
gt; gt; unless using fixed fonts which is not reasonable.
gt; gt; Could I capture this 'special' character by recording a macro (which I will
gt; gt; try)? Or does someone know what this 'special' character might be?
gt; gt;
gt; gt; Thanks for any help you might provide,
gt; gt; DannyDont
gt;
gt; --
gt;
gt; Dave Peterson
gt;

What's wrong with just using the Excel worksheet for a directory listing.

Sounds more like you are formatting for printing labels, in which case
you would use Excel as the database and print with MS Word.
www.mvps.org/dmcritchie/excel/mailmerg.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;DannyDontquot; gt; wrote in message ...
gt; Well, I answered my own question by recording a Macro. This macro replaced
gt; the 'Alt' 'Enter' with a Chr(10). Of course this was VB for Excel and
gt; wouldn't you know that the command was different in an Excel Expression.
gt; But, it was an easy find to go from Chr(10) to CHAR(10). Thanks for all your
gt; help. Sometimes it just takes explaining something to others to turn the
gt; light on!!!!
gt;
gt; DannyDont
gt;
gt;
gt; quot;DannyDontquot; wrote:
gt;
gt; gt; I have a need to be able to create a funtion/expression that can issue a
gt; gt; 'hard carriage return amp; line feed'. On the keyboard this would be 'Alt'
gt; gt; 'Enter'. I want to take two (or more) cells and combine them into one cell
gt; gt; with each value on it's own 'line' in the receiving cell.
gt; gt; ============================
gt; gt; Receiving Cell Cell 1 Cell 2
gt; gt; Smith John Smith
gt; gt; John
gt; gt; ============================
gt; gt; I am trying to create a Membership Directory for print and distribution. I
gt; gt; do having it working by determining the length of the Sending Cell and then
gt; gt; padding the end with 'n' spaces depending on the size of the Receiving Cell
gt; gt; and the length of the Sending Cell. This works but is rather arbitrary
gt; gt; unless using fixed fonts which is not reasonable.
gt; gt; Could I capture this 'special' character by recording a macro (which I will
gt; gt; try)? Or does someone know what this 'special' character might be?
gt; gt;
gt; gt; Thanks for any help you might provide,
gt; gt; DannyDont