Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
"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 |
#3
|
|||
|
|||
-----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. |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) | |||
How do I set up excel so you enter a stock code and it brings up . | Excel Discussion (Misc queries) |