|
Macro assistance?
I am working with an index that contains topics and subtopics. I need
to separate the topics and subtopics into column A and B while retaining their order in terms of row position. No delimiters, only difference is subtopics are indented by .25". I have very little experience with macros but tried to record something, but it just keeps moving the same line over to the next column, doesn't seem to find specific formatting. Your assistance is most appreciated, joanietv |
Macro assistance?
joanietv brought next idea :
I am working with an index that contains topics and subtopics. I need to separate the topics and subtopics into column A and B while retaining their order in terms of row position. No delimiters, only difference is subtopics are indented by .25". I have very little experience with macros but tried to record something, but it just keeps moving the same line over to the next column, doesn't seem to find specific formatting. Your assistance is most appreciated, joanietv You're going to have to provide more info if you expect anyone to be able to help you with this. For instance... Where is the data located? If in a spreadsheet, what is the layout? Where did the data come from? Can you post a sample for a couple of topics and their respective subtopics? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Macro assistance?
On May 6, 12:29*am, GS wrote:
joanietv brought next idea : I am working with an index that contains topics and subtopics. I need to separate the topics and subtopics into column A and B while retaining their order in terms of row position. No delimiters, only difference is subtopics are indented by .25". I have very little experience with macros but tried to record something, but it just keeps moving the same line over to the next column, doesn't seem to find specific formatting. Your assistance is most appreciated, joanietv You're going to have to provide more info if you expect anyone to be able to help you with this. For instance... Where is the data located? * If in a spreadsheet, what is the layout? Where did the data come from? Can you post a sample for a couple of topics and their respective subtopics? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Sorry. Didnt want to get too wordy. Text originally in Word 31 pages like this: above-ground caring for children See Rule 747.4015 accidents: records* See Subchapter C, Division 2 creating and keeping a written record See Rules 747.701, 747.705, 747.709 providing a copy to parents See Rule 747.707 required form See Rule 747.703 active play pre-kindergarten See Rule 747.2507 school-age See Rule 747.2607 toddlers See Rule 747.2407 water served after active play See Rule 747.3101 active play equipment Tried working on it there, but no luck so I transferred to Excel to see if idented lines would come into Column B, but all text copies into Column A. I need to translate the text and then re-alphabetize according to the new language, but have to be able to separate topics and subtopics. Maybe place a delimiter at the beginning of indented lines? But haven't figured out (in Word or Excel) how to do that. Let me know if you need more info. joanietv Does that help? |
Macro assistance?
On May 6, 8:38*am, joanietv wrote:
On May 6, 12:29*am, GS wrote: joanietv brought next idea : I am working with an index that contains topics and subtopics. I need to separate the topics and subtopics into column A and B while retaining their order in terms of row position. No delimiters, only difference is subtopics are indented by .25". I have very little experience with macros but tried to record something, but it just keeps moving the same line over to the next column, doesn't seem to find specific formatting. Your assistance is most appreciated, joanietv You're going to have to provide more info if you expect anyone to be able to help you with this. For instance... Where is the data located? * If in a spreadsheet, what is the layout? Where did the data come from? Can you post a sample for a couple of topics and their respective subtopics? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Sorry. Didnt want to get too wordy. Text originally in Word 31 pages like this: above-ground caring for children See Rule 747.4015 accidents: records* See Subchapter C, Division 2 * * creating and keeping a written record See Rules 747.701, 747.705, 747.709 * * providing a copy to parents See Rule 747.707 * * required form See Rule 747.703 active play * * pre-kindergarten See Rule 747.2507 * * school-age See Rule 747.2607 * * toddlers See Rule 747.2407 * * water served after active play See Rule 747.3101 active play equipment Tried working on it there, but no luck so I transferred to Excel to see if idented lines would come into Column B, but all text copies into Column A. I need to translate the text and then re-alphabetize according to the new language, but have to be able to separate topics and subtopics. Maybe place a delimiter at the beginning of indented lines? But haven't figured out (in Word or Excel) how to do that. Let me know if you need more info. joanietv Does that help?- Hide quoted text - - Show quoted text - Another detail I just remembered... indented text is formatted in different "style" than text that is not indented. Possible to insert delimiter such as "-" at beginning of any text string with that style? Just a thougt... Thanks, joanietv |
Macro assistance?
joanietv has brought this to us :
On May 6, 12:29Β*am, GS wrote: joanietv brought next idea : I am working with an index that contains topics and subtopics. I need to separate the topics and subtopics into column A and B while retaining their order in terms of row position. No delimiters, only difference is subtopics are indented by .25". I have very little experience with macros but tried to record something, but it just keeps moving the same line over to the next column, doesn't seem to find specific formatting. Your assistance is most appreciated, joanietv You're going to have to provide more info if you expect anyone to be able to help you with this. For instance... Where is the data located? Β* If in a spreadsheet, what is the layout? Where did the data come from? Can you post a sample for a couple of topics and their respective subtopics? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Sorry. Didnt want to get too wordy. Text originally in Word 31 pages like this: above-ground caring for children See Rule 747.4015 accidents: recordsΒ* See Subchapter C, Division 2 creating and keeping a written record See Rules 747.701, 747.705, 747.709 providing a copy to parents See Rule 747.707 required form See Rule 747.703 active play pre-kindergarten See Rule 747.2507 school-age See Rule 747.2607 toddlers See Rule 747.2407 water served after active play See Rule 747.3101 active play equipment Tried working on it there, but no luck so I transferred to Excel to see if idented lines would come into Column B, but all text copies into Column A. I need to translate the text and then re-alphabetize according to the new language, but have to be able to separate topics and subtopics. Maybe place a delimiter at the beginning of indented lines? But haven't figured out (in Word or Excel) how to do that. Let me know if you need more info. joanietv Does that help? You can enter the following formula into ColB for each subtopic. Then select ColB and copy/paste values. Then clear contents in subtopic cells in ColA. =MID(A3,5,LEN(A3)) OR You can run the following macro for each sheet: Sub MoveSubtopics() Dim r As Long, lLastrow As Long lLastrow = ActiveSheet.UsedRange.Rows.Count For r = 1 To lLastrow With Cells(r, "A") If Asc(Left$(.Value, 1)) = 160 Then _ .Offset(, 1) = Mid$(.Value, 5): .ClearContents End With Next 'r End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Macro assistance?
On May 6, 1:00*pm, GS wrote:
joanietv has brought this to us : On May 6, 12:29*am, GS wrote: joanietv brought next idea : I am working with an index that contains topics and subtopics. I need to separate the topics and subtopics into column A and B while retaining their order in terms of row position. No delimiters, only difference is subtopics are indented by .25". I have very little experience with macros but tried to record something, but it just keeps moving the same line over to the next column, doesn't seem to find specific formatting. Your assistance is most appreciated, joanietv You're going to have to provide more info if you expect anyone to be able to help you with this. For instance... Where is the data located? * If in a spreadsheet, what is the layout? Where did the data come from? Can you post a sample for a couple of topics and their respective subtopics? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Sorry. Didnt want to get too wordy. Text originally in Word 31 pages like this: above-ground caring for children See Rule 747.4015 accidents: records* See Subchapter C, Division 2 * * creating and keeping a written record See Rules 747.701, 747.705, 747.709 * * providing a copy to parents See Rule 747.707 * * required form See Rule 747.703 active play * * pre-kindergarten See Rule 747.2507 * * school-age See Rule 747.2607 * * toddlers See Rule 747.2407 * * water served after active play See Rule 747.3101 active play equipment Tried working on it there, but no luck so I transferred to Excel to see if idented lines would come into Column B, but all text copies into Column A. I need to translate the text and then re-alphabetize according to the new language, but have to be able to separate topics and subtopics. Maybe place a delimiter at the beginning of indented lines? But haven't figured out (in Word or Excel) how to do that. Let me know if you need more info. joanietv Does that help? You can enter the following formula into ColB for each subtopic. Then select ColB and copy/paste values. Then clear contents in subtopic cells in ColA. * =MID(A3,5,LEN(A3)) OR You can run the following macro for each sheet: Sub MoveSubtopics() * Dim r As Long, lLastrow As Long * lLastrow = ActiveSheet.UsedRange.Rows.Count * For r = 1 To lLastrow * * With Cells(r, "A") * * * If Asc(Left$(.Value, 1)) = 160 Then _ * * * * .Offset(, 1) = Mid$(.Value, 5): .ClearContents * * End With * Next 'r End Sub -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Garry, Thanks for the quick response. I tried running the macro after copying and pasting one page of index into Excel, but nothing happened. Can you tell me which of the variables/values I may need to tweak to get the macro to "see" my text? Maybe the offset is off? Also, can you tell me what code moves text to column B? Much appreciated, jtv |
Macro assistance?
I copied/pasted your sample text into A1, then ran the macro. Result
was all subtopics were copied to ColB and cleared from ColA. Is your text in ColA? See below for more comments... above-ground caring for children See Rule 747.4015 accidents: recordsΒ* See Subchapter C, Division 2 Β* Β* creating and keeping a written record See Rules 747.701, 747.705, 747.709 Β* Β* providing a copy to parents See Rule 747.707 Β* Β* required form See Rule 747.703 active play Β* Β* pre-kindergarten See Rule 747.2507 Β* Β* school-age See Rule 747.2607 Β* Β* toddlers See Rule 747.2407 Β* Β* water served after active play See Rule 747.3101 active play equipment You can enter the following formula into ColB for each subtopic. Then select ColB and copy/paste values. Then clear contents in subtopic cells in ColA. Β* =MID(A3,5,LEN(A3)) OR You can run the following macro for each sheet: Sub MoveSubtopics() Β* Dim r As Long, lLastrow As Long Β* lLastrow = ActiveSheet.UsedRange.Rows.Count Β* For r = 1 To lLastrow Β* Β* With Cells(r, "A") 'If a subtopic then move it to ColB, clear ColA Β* Β* Β* If Asc(Left$(.Value, 1)) = 160 Then _ Β* Β* Β* Β* .Offset(, 1) = Mid$(.Value, 5): .ClearContents Β* Β* End With Β* Next 'r End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Macro assistance?
On May 6, 2:52*pm, GS wrote:
I copied/pasted your sample text into A1, then ran the macro. Result was all subtopics were copied to ColB and cleared from ColA. Is your text in ColA? See below for more comments... above-ground caring for children See Rule 747.4015 accidents: records* See Subchapter C, Division 2 * * creating and keeping a written record See Rules 747.701, 747.705, 747.709 * * providing a copy to parents See Rule 747.707 * * required form See Rule 747.703 active play * * pre-kindergarten See Rule 747.2507 * * school-age See Rule 747.2607 * * toddlers See Rule 747.2407 * * water served after active play See Rule 747.3101 active play equipment You can enter the following formula into ColB for each subtopic. Then select ColB and copy/paste values. Then clear contents in subtopic cells in ColA. * =MID(A3,5,LEN(A3)) OR You can run the following macro for each sheet: Sub MoveSubtopics() * Dim r As Long, lLastrow As Long * lLastrow = ActiveSheet.UsedRange.Rows.Count * For r = 1 To lLastrow * * With Cells(r, "A") * * * * *'If a subtopic then move it to ColB, clear ColA * * * If Asc(Left$(.Value, 1)) = 160 Then _ * * * * .Offset(, 1) = Mid$(.Value, 5): .ClearContents * * End With * Next 'r End Sub -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - I see... Unfortunately, the text I copied and pasted in the message box did not indent the subtopics, so I manually indented them by inserting some spaces to the left. Would that make a difference? Can I send you a file with some of the actual Word text copied and pasted into Excel file? Thanks for your help. jtv |
Macro assistance?
joanietv wrote :
I see... Unfortunately, the text I copied and pasted in the message box did not indent the subtopics, so I manually indented them by inserting some spaces to the left. Would that make a difference? The code looks for the first character in the text. If it's a space then it treats it as a subtopic. The sample text you posted had 4 spaces in front of subtopics and no spaces in front of topics. Can I send you a file with some of the actual Word text copied and pasted into Excel file? Thanks for your help. jtv Yes, you can attach a file here if you want. I think you should first copy/paste from Word to A1 in Excel, then try the macro again. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Macro assistance?
On May 6, 3:14*pm, GS wrote:
joanietv wrote : I see... Unfortunately, the text I copied and pasted in the message box did not indent the subtopics, so I manually indented them by inserting some spaces to the left. Would that make a difference? The code looks for the first character in the text. If it's a space then it treats it as a subtopic. The sample text you posted had 4 spaces in front of subtopics and no spaces in front of topics. Can I send you a file with some of the actual Word text copied and pasted into Excel file? Thanks for your help. jtv Yes, you can attach a file here if you want. I think you should first copy/paste from Word to A1 in Excel, then try the macro again. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Yes, my bad. When I pasted text into message box it was all flush so I introduced spaces to show what original looks like. Unfortunately, original has no spaces... it is indented using margins/styles. Doesn't appear to be any way to upload files on this message board. |
Macro assistance?
on 5/6/2011, joanietv supposed :
On May 6, 3:14*pm, GS wrote: joanietv wrote : I see... Unfortunately, the text I copied and pasted in the message box did not indent the subtopics, so I manually indented them by inserting some spaces to the left. Would that make a difference? The code looks for the first character in the text. If it's a space then it treats it as a subtopic. The sample text you posted had 4 spaces in front of subtopics and no spaces in front of topics. Can I send you a file with some of the actual Word text copied and pasted into Excel file? Thanks for your help. jtv Yes, you can attach a file here if you want. I think you should first copy/paste from Word to A1 in Excel, then try the macro again. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Yes, my bad. When I pasted text into message box it was all flush so I introduced spaces to show what original looks like. Unfortunately, original has no spaces... it is indented using margins/styles. Doesn't appear to be any way to upload files on this message board. email it to me at... gesansomATnetscapeDOTnet -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Macro assistance?
You can upload your file to one of these sites.
http://www.savefile.com/ http://freefilehosting.net/ After uploading, you will be given a URL............post that here and someone can download your file to look at it. Gord Dibben MS Excel MVP On Fri, 6 May 2011 13:25:04 -0700 (PDT), joanietv wrote: On May 6, 3:14*pm, GS wrote: joanietv wrote : I see... Unfortunately, the text I copied and pasted in the message box did not indent the subtopics, so I manually indented them by inserting some spaces to the left. Would that make a difference? The code looks for the first character in the text. If it's a space then it treats it as a subtopic. The sample text you posted had 4 spaces in front of subtopics and no spaces in front of topics. Can I send you a file with some of the actual Word text copied and pasted into Excel file? Thanks for your help. jtv Yes, you can attach a file here if you want. I think you should first copy/paste from Word to A1 in Excel, then try the macro again. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Yes, my bad. When I pasted text into message box it was all flush so I introduced spaces to show what original looks like. Unfortunately, original has no spaces... it is indented using margins/styles. Doesn't appear to be any way to upload files on this message board. |
Macro assistance?
Gord Dibben submitted this idea :
You can upload your file to one of these sites. http://www.savefile.com/ http://freefilehosting.net/ After uploading, you will be given a URL............post that here and someone can download your file to look at it. Gord Dibben MS Excel MVP Thanks, Gord! I'm happy there's an alternative to giving out my email address. -Much appreciated... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
| All times are GMT +1. The time now is 04:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com