Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word Wrap with 1024 characters Excel 2003 | Excel Discussion (Misc queries) | |||
Any ideas how to copy more than 1024 characters? | New Users to Excel | |||
Error message is Character Limit is over 1024 characters | Excel Discussion (Misc queries) | |||
how do i get a cell in a sheet to show the last 1024 characters? | Excel Worksheet Functions | |||
Display text 1024 characters in a cell | Excel Worksheet Functions |