Hello,
I'm having trouble how to automatically correct this problem.

The output from a macro looks like this: 2.167�ª1.033�ª9.604
where the �ª's are "alt-enter", which is to say that this should look like:
2.167
1.033
9.604

If I...
1) double click in the cell, as if to edit it, and then hit the "enter" key,
it's corrected
2) (similarly) hit the "F2" key, and then "enter" it's also corrected

Is there something that I can add to the macro to correct this in the macro?

Many thanks,
M John

Make .wraptext = true

(format|cells|alignment tab if you want to record a macro when you do it)

M John wrote:

Hello,
I'm having trouble how to automatically correct this problem.

The output from a macro looks like this: 2.167�ª1.033�ª9.604
where the �ª's are "alt-enter", which is to say that this should look like:
2.167
1.033
9.604

If I...
1) double click in the cell, as if to edit it, and then hit the "enter" key,
it's corrected
2) (similarly) hit the "F2" key, and then "enter" it's also corrected

Is there something that I can add to the macro to correct this in the macro?

Many thanks,
M John

--

Dave Peterson

I John,
What about setting the Wrap Text through code:
Dim cell as range
...
cell.WrapText = true
(whether cell is a single cell or a multi-cell range)
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"M John" wrote:

Hello,
I'm having trouble how to automatically correct this problem.

The output from a macro looks like this: 2.167�ª1.033�ª9.604
where the �ª's are "alt-enter", which is to say that this should look like:
2.167
1.033
9.604

If I...
1) double click in the cell, as if to edit it, and then hit the "enter" key,
it's corrected
2) (similarly) hit the "F2" key, and then "enter" it's also corrected

Is there something that I can add to the macro to correct this in the macro?

Many thanks,
M John

Excellent! That solved the problem. Many thanks. Most appreciated.

M John

"sebastienm" wrote:

I John,
What about setting the Wrap Text through code:
Dim cell as range
...
cell.WrapText = true
(whether cell is a single cell or a multi-cell range)
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"M John" wrote:

Hello,
I'm having trouble how to automatically correct this problem.

The output from a macro looks like this: 2.167�ª1.033�ª9.604
where the �ª's are "alt-enter", which is to say that this should look like:
2.167
1.033
9.604

If I...
1) double click in the cell, as if to edit it, and then hit the "enter" key,
it's corrected
2) (similarly) hit the "F2" key, and then "enter" it's also corrected

Is there something that I can add to the macro to correct this in the macro?

Many thanks,
M John

Sébastien,

Thank you for all your help thus far. My latest difficulty is how to make
the code below work for any selected cell.....not just data in the e1:e500
range? Is there a way to make this code work for any/all cells currently
selected?
Thanks again. You've been an enormous help.

M John


Dim rg As Range, cell As Range
Dim txtpos As Long, txtlen As Long

Set rg = Range("E1:E500")
For Each cell In rg.Cells
txtpos = 1
txtlen = Len(cell.Offset(0, -3).Text) 'length of text in A
cell.Characters(1, txtlen).Font.ColorIndex = 3 'color in red
txtpos = txtpos + txtlen + 1 ' +1 for the newline character
txtlen = Len(cell.Offset(0, -2).Text) 'length of text in B
cell.Characters(txtpos, txtlen).Font.ColorIndex = 5 'search for blue,
i don't know
txtpos = txtpos + txtlen + 1 ' +1 for the newline character
txtlen = Len(cell.Offset(0, -1).Text) 'length of text in C
cell.Characters(txtpos, txtlen).Font.ColorIndex = 10 'search for
green, i don't know
txtpos = txtpos + txtlen + 1
cell.WrapText = True
Next

Hi John,

Replace the line:
Set rg = Range("E1:E500")
with :
Set rg = Selection
If rg is Nothing the exit sub 'no selection
If typename(Selection)<"Range" then exit sub 'selection is not range

'Then one *ONLY* of the following section depending on
'what you are looking for:

'Section1: use the first column of the selection even if not column E
' ie Say user selects J20:K50 then use 1st column J20:J50
Set rg= rg.columns(1)

'Section2: use selection projected on column E
' ie Say user selects J20:K50 then use E20:E50 -- same rows on col E
Set rg=application.intersect(rg.Range("E:E"), rg.EntireRow)

I hope this helps,
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"M John" wrote:

Sébastien,

Thank you for all your help thus far. My latest difficulty is how to make
the code below work for any selected cell.....not just data in the e1:e500
range? Is there a way to make this code work for any/all cells currently
selected?
Thanks again. You've been an enormous help.

M John


Dim rg As Range, cell As Range
Dim txtpos As Long, txtlen As Long

Set rg = Range("E1:E500")
For Each cell In rg.Cells
txtpos = 1
txtlen = Len(cell.Offset(0, -3).Text) 'length of text in A
cell.Characters(1, txtlen).Font.ColorIndex = 3 'color in red
txtpos = txtpos + txtlen + 1 ' +1 for the newline character
txtlen = Len(cell.Offset(0, -2).Text) 'length of text in B
cell.Characters(txtpos, txtlen).Font.ColorIndex = 5 'search for blue,
i don't know
txtpos = txtpos + txtlen + 1 ' +1 for the newline character
txtlen = Len(cell.Offset(0, -1).Text) 'length of text in C
cell.Characters(txtpos, txtlen).Font.ColorIndex = 10 'search for
green, i don't know
txtpos = txtpos + txtlen + 1
cell.WrapText = True
Next

As per usual, the solution worked excellently. Most appreciated.

M John

"sebastienm" wrote:

Hi John,

Replace the line:
Set rg = Range("E1:E500")
with :
Set rg = Selection
If rg is Nothing the exit sub 'no selection
If typename(Selection)<"Range" then exit sub 'selection is not range

'Then one *ONLY* of the following section depending on
'what you are looking for:

'Section1: use the first column of the selection even if not column E
' ie Say user selects J20:K50 then use 1st column J20:J50
Set rg= rg.columns(1)

'Section2: use selection projected on column E
' ie Say user selects J20:K50 then use E20:E50 -- same rows on col E
Set rg=application.intersect(rg.Range("E:E"), rg.EntireRow)

I hope this helps,
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"M John" wrote:

Sébastien,

Thank you for all your help thus far. My latest difficulty is how to make
the code below work for any selected cell.....not just data in the e1:e500
range? Is there a way to make this code work for any/all cells currently
selected?
Thanks again. You've been an enormous help.

M John


Dim rg As Range, cell As Range
Dim txtpos As Long, txtlen As Long

Set rg = Range("E1:E500")
For Each cell In rg.Cells
txtpos = 1
txtlen = Len(cell.Offset(0, -3).Text) 'length of text in A
cell.Characters(1, txtlen).Font.ColorIndex = 3 'color in red
txtpos = txtpos + txtlen + 1 ' +1 for the newline character
txtlen = Len(cell.Offset(0, -2).Text) 'length of text in B
cell.Characters(txtpos, txtlen).Font.ColorIndex = 5 'search for blue,
i don't know
txtpos = txtpos + txtlen + 1 ' +1 for the newline character
txtlen = Len(cell.Offset(0, -1).Text) 'length of text in C
cell.Characters(txtpos, txtlen).Font.ColorIndex = 10 'search for
green, i don't know
txtpos = txtpos + txtlen + 1
cell.WrapText = True
Next