Macro assistance?

[joanietv]

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

 

[GS]

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?

 

[joanietv]

joanietv brought next idea :



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?

 

[joanietv]

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

 

[GS]

joanietv has brought this to us :

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

 

[joanietv]

joanietv has brought this to us :











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

 

[GS]

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...

 

[joanietv]

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...






         'If a subtopic then move it to ColB, clear ColA


--
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

 

[GS]

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.

 

[joanietv]

joanietv wrote :


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.


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.

 

[GS]

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

 

[Gord Dibben]

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

 

[GS]

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...