Find next cell with more than 1024 characters
 

Good morning all:

I have a table with a few cells with about 2000 characters each . Of
course, Excel will not show the text after 1024. I would like to do a
macro that would find the next cell with more than 1024 characters, in
order to format the cell with the macro found on this page:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=421

I could do it manually but it is a merged document and I remerge this
document at least once a week.

Any help?

Thank you!

Souriane

Find next cell with more than 1024 characters
 

change just the main routine

from
Sub DisplayLongText()
'Adds line feed characters as required on cells in selection that are
longer than 1024 characters
Dim cel As Range
Dim col As Long
For Each cel In Selection
AddLineFeeds cel, col
Next
col = 0 'Force line length dialog to display the next time sub runs
End Sub

to
Sub DisplayLongText()
'Adds line feed characters as required on cells in selection that are
longer than 1024 characters
Dim cel As Range
Dim col As Long
For Each cel In Selection
if len(cel) 1024 then
AddLineFeeds cel, col
end if
Next
col = 0 'Force line length dialog to display the next time sub runs
End Sub


"Souriane" wrote:

Good morning all:

I have a table with a few cells with about 2000 characters each . Of
course, Excel will not show the text after 1024. I would like to do a
macro that would find the next cell with more than 1024 characters, in
order to format the cell with the macro found on this page:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=421

I could do it manually but it is a merged document and I remerge this
document at least once a week.

Any help?

Thank you!

Souriane

Find next cell with more than 1024 characters
 

Thanks! Works great! Except for one thing :

If my sheet has 2 rows in a row that has a cell with more than 1024,
then the first letter of the text in the second row will be alone on
the first line like this:

ROW 1:
Servez à ce monsieur une bière et des kiwis. Servez à ce monsieur une
bière et des kiwis. Servez à ce monsieur une bière et des kiwis.
Servez à ce monsieur une bière et des kiwis. Servez à ce monsieur une

ROW 2 :
S
ervez à ce monsieur une bière et des kiwis. Servez à ce monsieur une
bière et des kiwis. Servez à ce monsieur une bière et des kiwis.
Servez à ce monsieur une bière et des kiwis. Servez à ce monsieur une

Any idea?...

On 28 avr, 12:29, joel wrote:
change just the main routine

from
Sub DisplayLongText()
* * *'Adds line feedcharactersas required on cells in selection that are
longerthan1024characters
* * Dim cel As Range
* * Dim col As Long
* * For Each cel In Selection
* * * * AddLineFeeds cel, col
* *Next
* * col = 0 'Force line length dialog to display thenexttime sub runs
End Sub

to
Sub DisplayLongText()
* * *'Adds line feedcharactersas required on cells in selection that are
longerthan1024characters
* * Dim cel As Range
* * Dim col As Long
* * For Each cel In Selection
* * * if len(cel) 1024then
* * * * AddLineFeeds cel, col
* * * end if
* *Next
* * col = 0 'Force line length dialog to display thenexttime sub runs
End Sub



"Souriane" wrote:
Good morning all:

I have a table with a few cells with about 2000characterseach . *Of
course, Excel will not show the text after1024. *I would like to do a
macro that wouldfindthenextcellwithmorethan1024characters, in
order to format thecellwith the macro found on this page:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=421

I could do it manually but it is a merged document and I remerge this
document at least once a week.

Any help?

Thank you!

Souriane- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

Find next cell with more than 1024 characters
 

Not sure what length yo are using in the code. The line you posted only had
204 characters. Try changing the 1024 to 1023 or 1022. The comments in the
website code sometimes references 1022 and other times 1024. I believe the
code has to put two character (return and linefeed) in each line which
accounts for two characters. Therefore tthe code will only work when the
line is 1022 and not 1024.

"Souriane" wrote:

Thanks! Works great! Except for one thing :

If my sheet has 2 rows in a row that has a cell with more than 1024,
then the first letter of the text in the second row will be alone on
the first line like this:

ROW 1:
Servez Ã* ce monsieur une bière et des kiwis. Servez Ã* ce monsieur une
bière et des kiwis. Servez Ã* ce monsieur une bière et des kiwis.
Servez Ã* ce monsieur une bière et des kiwis. Servez Ã* ce monsieur une

ROW 2 :
S
ervez Ã* ce monsieur une bière et des kiwis. Servez Ã* ce monsieur une
bière et des kiwis. Servez Ã* ce monsieur une bière et des kiwis.
Servez Ã* ce monsieur une bière et des kiwis. Servez Ã* ce monsieur une

Any idea?...

On 28 avr, 12:29, joel wrote:
change just the main routine

from
Sub DisplayLongText()
'Adds line feedcharactersas required on cells in selection that are
longerthan1024characters
Dim cel As Range
Dim col As Long
For Each cel In Selection
AddLineFeeds cel, col
Next
col = 0 'Force line length dialog to display thenexttime sub runs
End Sub

to
Sub DisplayLongText()
'Adds line feedcharactersas required on cells in selection that are
longerthan1024characters
Dim cel As Range
Dim col As Long
For Each cel In Selection
if len(cel) 1024then
AddLineFeeds cel, col
end if
Next
col = 0 'Force line length dialog to display thenexttime sub runs
End Sub



"Souriane" wrote:
Good morning all:

I have a table with a few cells with about 2000characterseach . Of
course, Excel will not show the text after1024. I would like to do a
macro that wouldfindthenextcellwithmorethan1024characters, in
order to format thecellwith the macro found on this page:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=421

I could do it manually but it is a merged document and I remerge this
document at least once a week.

Any help?

Thank you!

Souriane- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -