ALT + enter or Char(13) in code
 

I am trying to manipulate strings dumped into Excel from
another system. I am combining strings from column G into
a single cell in column H, which works well. However, I
don't want the strings strung together in the cell. I
would like to do the equivalent of the ALT + enter
function after each string so that they are in their
own "lines". I would think that this would be possible,
but can't figure it out. A portion of my code is below.

Thank you

Dim ct As Integer
ct = 0
test = 0
If ActiveCell.Value < "" Then
loop1:
ct = ct + 1
cmb = ActiveCell.Value
If ActiveCell.Offset(ct, 0).Value = " " Then
Do Until test = ct
cmb = cmb & ActiveCell.Offset(test + 1, 0).Value 'need
help here
test = test + 1
Loop

ActiveCell.Offset(0, 1).Value = cmb
ActiveCell.Offset(ct + 1, 0).Select
GoTo rs
End If


GoTo loop1
End If

"JL" wrote in message
...
I am trying to manipulate strings dumped into Excel from
another system. I am combining strings from column G into
a single cell in column H, which works well. However, I
don't want the strings strung together in the cell. I
would like to do the equivalent of the ALT + enter
function after each string so that they are in their
own "lines". I would think that this would be possible,
but can't figure it out. A portion of my code is below.

Thank you

Dim ct As Integer
ct = 0
test = 0
If ActiveCell.Value < "" Then
loop1:
ct = ct + 1
cmb = ActiveCell.Value
If ActiveCell.Offset(ct, 0).Value = " " Then
Do Until test = ct
cmb = cmb & ActiveCell.Offset(test + 1, 0).Value 'need
help here
test = test + 1
Loop

ActiveCell.Offset(0, 1).Value = cmb
ActiveCell.Offset(ct + 1, 0).Select
GoTo rs
End If


GoTo loop1
End If


The first thing you should look at is the system that creates the file. It
may have an option that results in a file that is easier to import. I think
it would be easier for us if you pasted a represenative portion of the file
contents and how you want it to be interpreted in Excel.

/Fredrik

-----Original Message-----

"JL" wrote in
message
...
I am trying to manipulate strings dumped into Excel from
another system. I am combining strings from column G
into
a single cell in column H, which works well. However, I
don't want the strings strung together in the cell. I
would like to do the equivalent of the ALT + enter
function after each string so that they are in their
own "lines". I would think that this would be possible,
but can't figure it out. A portion of my code is below.

Thank you

Dim ct As Integer
ct = 0
test = 0
If ActiveCell.Value < "" Then
loop1:
ct = ct + 1
cmb = ActiveCell.Value
If ActiveCell.Offset(ct, 0).Value = " " Then
Do Until test = ct
cmb = cmb & ActiveCell.Offset(test + 1,
0).Value 'need
help here
test = test + 1
Loop

ActiveCell.Offset(0, 1).Value = cmb
ActiveCell.Offset(ct + 1, 0).Select
GoTo rs
End If


GoTo loop1
End If


The first thing you should look at is the system that
creates the file. It
may have an option that results in a file that is easier
to import. I think
it would be easier for us if you pasted a represenative
portion of the file
contents and how you want it to be interpreted in Excel.

/Fredrik

The problem is that it can't be exported again because the
system (AS400) was trashed after the dump. I tried to
paste here what I'd like, but the formatting is lost so I
will try to explain.

Cell A1:QAZ
Cell A2:WSX
Cell A3:EDC

Each string has a trailing space. After running the code
cell B1 contains the string: QAZ WSX EDC. I would like it
to appear as: QAZ
WSX
EDC

in cell B1. I hope this clears things up and thank you for
your time.

The character to add is Chr$(10).

On Fri, 8 Apr 2005 06:25:48 -0700, "JL"
wrote:

I am trying to manipulate strings dumped into Excel from
another system. I am combining strings from column G into
a single cell in column H, which works well. However, I
don't want the strings strung together in the cell. I
would like to do the equivalent of the ALT + enter
function after each string so that they are in their
own "lines". I would think that this would be possible,
but can't figure it out. A portion of my code is below.

Thank you

Dim ct As Integer
ct = 0
test = 0
If ActiveCell.Value < "" Then
loop1:
ct = ct + 1
cmb = ActiveCell.Value
If ActiveCell.Offset(ct, 0).Value = " " Then
Do Until test = ct
cmb = cmb & ActiveCell.Offset(test + 1, 0).Value 'need
help here
test = test + 1
Loop

ActiveCell.Offset(0, 1).Value = cmb
ActiveCell.Offset(ct + 1, 0).Select
GoTo rs
End If


GoTo loop1
End If