Splitting a document into multiple documents

[RonMcK]

I have 56 documents each of which contains between 5 and 12 "activities" for a grade and unit. I need to split these out into new individual files/documents by lesson. Some lessons contain 2 activities and the remainder have only one activity.

Within the document each activity is formatted in the following manner:

----------------------------------------
NL_G5_LC_00202_LessonObjectTitle
Grade 5, Unit 3, Lesson 1
Activity Title
Inquiry Flipchart p. 12

Directed Inquiry

<snip -- removed 2-3 pp of activity text>



My Notes
<page break>
------------------------------------------

I looked at Tinbender's solution for Michelle_S (http://www.vbaexpress.com/forum/showthread.php?t=35900&highlight=split+document), however, I have the same text denoting each break ("My Notes" or the <page break>) instead of separate codes.

Some other requirements. I need to use the first line of text as the new filename; to that I need to append the grade, unit, and lesson numbers from the second line in the form "G5 U3 L1", so the filename reads: "NL_G5_LC_00202_LessonObjectTitle G5 U3 L1". Finally, the first line needs to be deleted.

When the lesson object code has a "C" in the 8th position (as above), the inquiry has two activities. When that code is an "I" there is only one activity.

Is it easier for the VBA to test the code and if a "C" is detected then find the 2nd instance of the "end of activity" to set the range? Or, is there a better way to handle that?

Let me know if you need more information.

Thanks in advance!

[RonMcK]

Hi, All,

Well, to help all y'all help me, I recorded a macro and then annotated it so you can see the process that I'm trying to automate.

[vba]Sub Macro5()
'
' Macro5 Macro
'
'
ChangeFileOpenDirectory _
"C:\Documents and Settings\mckenzier\Desktop\Inquiry Support\G5\U07\"
Documents.Open FileName:="G5U07_InquirySupport.doc", ConfirmConversions:= _
True, ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
ActiveWindow.ActivePane.View.Zoom.Percentage = 100

' The first lesson is a "C", so, I page down until I find the 2nd instance of
' My Notes. In wdVBA how do I read the first line and test the 8th character
' to see if it's a C or an I?
Selection.MoveDown Unit:=wdScreen, Count:=15, Extend:=wdExtend
Selection.Copy
Selection.Copy
Documents.Add DocumentType:=wdNewBlankDocument
Selection.PasteAndFormat (wdPasteDefault)
' I go back to the top to get the first line
Selection.HomeKey Unit:=wdStory
' There is a caption that I don't need ( LO name: ) so I highlight and delete it.
Selection.MoveRight Unit:=wdWord, Count:=3, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1
' Then I highlight and cut the portion I need for the filename.
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.Cut
' Since there are two activities, I next page down to the next instance of the
' name and delete it.
Selection.MoveDown Unit:=wdScreen, Count:=9
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.Delete Unit:=wdCharacter, Count:=1
' The name I cut I now paste into the File Save dialog and select RTF for the
' file type.
ActiveDocument.SaveAs FileName:= _
"G5 EC 00216 Activity Title 1 G5 U7 L1.rtf", FileFormat:=wdFormatRTF, _
LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False
' I close the new document and proceed to repeat the process; in this case, the
' 2nd lesson is also a "C" type, so, it will look very much like the above.
ActiveDocument.Close
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.HomeKey Unit:=wdLine
Selection.MoveDown Unit:=wdScreen, Count:=11, Extend:=wdExtend
Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
Selection.Copy
Documents.Add DocumentType:=wdNewBlankDocument
Selection.PasteAndFormat (wdPasteDefault)
Selection.MoveUp Unit:=wdParagraph, Count:=2
Selection.HomeKey Unit:=wdStory
Selection.MoveRight Unit:=wdWord, Count:=3, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.Cut
Selection.MoveDown Unit:=wdScreen, Count:=6
Selection.EndKey Unit:=wdLine
Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1
ActiveDocument.SaveAs FileName:= _
"G5 EC 00217 Activity Title 2 G5 U7 L2.rtf", FileFormat:= _
wdFormatRTF, LockComments:=False, Password:="", AddToRecentFiles:=True, _
WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False
ActiveDocument.Close
' The 3rd Lesson is an "I" so there is only one activity to process; note that this
' is also the last activity in the file. In VBA, how do I detect the EOF and close
' things gracefully?
Selection.MoveDown Unit:=wdScreen, Count:=1
Selection.HomeKey Unit:=wdLine
Selection.MoveDown Unit:=wdScreen, Count:=6, Extend:=wdExtend
Selection.Copy
Documents.Add DocumentType:=wdNewBlankDocument
Selection.PasteAndFormat (wdPasteDefault)
Selection.HomeKey Unit:=wdStory
Windows("G5U07_InquirySupport.doc [Compatibility Mode]").Activate
Selection.HomeKey Unit:=wdStory
Windows("Document13 [Compatibility Mode]").Activate
Selection.HomeKey Unit:=wdLine
Selection.MoveRight Unit:=wdWord, Count:=3, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.Cut
ActiveDocument.SaveAs FileName:="G5_EI_00218_Activity Title 3 G5 U7 L3.rtf", _
FileFormat:=wdFormatRTF, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
:=False, SaveAsAOCELetter:=False
If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
ActiveWindow.Panes(2).Close
End If
If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
ActivePane.View.Type = wdOutlineView Then
ActiveWindow.ActivePane.View.Type = wdPrintView
End If
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.Delete Unit:=wdCharacter, Count:=1
ActiveDocument.Save
ActiveDocument.Close
ActiveDocument.Close
End Sub
[/vba]

I'm somewhat adept in Excel VBA but this is my first venture into the jungle of Word VBA.

Thanks,

[RonMcK]

Any recommended texts on Word VBA programming? Other resources, beyond VBAExpress?

A question I forgot to ask, above.

' And, in VBA, how do I drop an anchor (begin of range) and then find the end
' of the range by searching for the 'n-th' occurrence of 'My Notes' where
' n= 1 or 2 ??


Thanks,

[mdmackillop]

Hi Ron,
One of my few ventures into Word!
Can you mock up a small document on which we can run your code and see the output?
Regards
Malcolm

[RonMcK]

Malcolm,

I'll mock up a file, create a macro that works with it, and post those for your use. I have to deal with a conference call, right now.

[Frosty]

Sorry, that recorded macro was kind of making my eyes bleed. I think you need to look up working with ranges in Word. There are some concepts in the help files which will help. .MoveEnd/.MoveStart and using InStr may help-- but a lot of times that's a lot less efficient than simply using the Find object.

My advice (even a mock up seems like it's going to be pretty complicated):

Start by re-recording that macro and throwing out the idea that you can make decisions based on what you see... the whole Selection.MoveDown Unit:=wdScreen is going to be pretty useless in terms of translating what we're seeing... 15 screens is not something you'd ever program in a macro, as your screen resolution is probably different than mine, as well as your zoom percentage.

You're really performing a "Find", and then selecting something with the mouse (which is not recorded) and copying it. Except for opening functions or clicking on buttons... using the mouse to select stuff is a no-no when you're recording a macro in Word. That action is often skipped, and the translation is tough to wade through.

I'd also take breaks when you're recording a long macro like that, so that you (and others) can more easily determine your "functions"

Record
Open your document
Stop record

Record
Find the text, select it, copy it
Stop record

Then you'll probably be able to, with familiarity in Excel VBA, to start to identify the different objects/concepts in the Word object model vs. the Excel object model. You'll also be able to do a little bit of basic clean up to the recorded macro (especially one of this length) in terms of just doing some With... End With statements.

But take heart, the Word jungle is no scarier than the Excel jungle... the tigers are just different colors.

Grin.

[Frosty]

Re-read a couple of the comments/questions in the code.

1. String functions can help (look up InStr, Mid, Left, Right, Len, etc), but I still think you want to investigate Find and Range objects.

2. Moving down line by line with the selection object is not something you should generally rely on when recording a macro. Better to hold down the CTRL key when you move down, since you can duplicate moving your "range" by paragraph, but moving by line is much less trustworthy, especially if you are deleting words in the middle of your move down process).

Hope that helps.

[RonMcK]

Frosty,

Thanks for the advice and guidance. I'll use that and the mock up I'm creating to see if I can clarify the process and perhaps even get the beginning of a 'real' VBA solution for you, Malc, and the lurkers to work with.

Thanks, again!

[fumei]

My two cents.

jason is right, this needs some clarity of purpose. Tell us what you want to happen. Every step.

Avoid using View (ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader). Especially with headers. You can fully action in a header without ever viewing it.

Avoid using Activate. It is rarely, if ever, actually needed..

REALLY avoid using Selection.

Try working it out on paper - yes paper - step by step, making no assumptions.

"The first lesson is a "C", so, I page down until I find the 2nd instance of
' My Notes."

My first lesson is a "C". WE have no idea what that means. What it seems to means is:

The first paragraph has the letter "C" as character #8.

Do you require the orginal document to be maintained? By that I mean could you work thusly?

1. Open document (if not open)
2. detect My Notes and page break.
3. extract and delete the chunk from start to My Notes/page break.
4. save chunk in new file (using your filenaming requirements)

repeat steps 2 - 4 until there is nothing left.

[RonMcK]

Gerry,

Thanks for your advice and guidance. I am working on a more cogent explanation, I'll be sure it addresses your questions as well as those of Frosty and Malc. I'm also working on code based on what I'm learning; I appreciate all y'all helping me make it as robust as possible for this task.

I like your suggestion of cutting from an original document and pasting into the target documents.

More news as it develops, film at 11.

I gather that Frosty := Jason since Malcolm is Malcolm?? (One more handle decoded.)

Thanks,

[RonMcK]

Malcolm, Frosty, Fumei, et al,

I have a program that is almost working, it just doesn't know how to gracefully end processing one file and move on to the next.

Below is the new version of my program; attached is a zip file containing that program in a Word file, a Word doc that's my design in pseudo-code, and a sample data file with greeked text.

As I see it, there are 2 presenting problems for which I covet your expertise and advice. Any other guidance on improving the programming will be appreciated, as well. And, yes, I seem to have used Selection a lot. Please explain alternative methods of doing this that rely less heavily on Selection.

First, the program as written, copies each activity for a lesson to the lesson's file (the target file) without difficulty until we get to the last activity in the source file. The problems involve processing the last activity in the Unit. Recall that each Lesson consists of one or more activities. The challenge is one of how to recognize and process being at the end of the file.

First problem: In my Find code block, I first Find the next instance of the phrase My Notes and, then, I do Selection.GoToNext (wdGoToPage) prior to doing HomeKey Unit:=wdStory, Extend:=wdExtend to select the text to cut and paste. When this code executes on the last lesson, because there is no trailing manual page break, the GoToNext goes BACK to the last manual page break causing my code to leave the last page of text in the source file. Bad.

The program then fails at the point it attempts to find Grade, Unit and Lesson numbers in the orphan text. When I add temp patch code to leap over that GoToNext, when I'm on the 6th activity (the last one in the sample file, a temporary patch), the program correctly cuts and pastes the entire last activity.

This leads to the 2nd ERROR, my code that attempts to deal with recognizing the end of file keeps finding the one remaining paragraph mark, let's me delete it, but when I loop back, it's obviously still there (and has to be, since there are no zero byte documents; thus it's not closing the Unit source file, and not looping for the next unit's source file to process.

How do I ascertain when the execute.find fails to find my phrase (My Notes). Seems only reasonable that if the last activity is missing that trailer piece that I should still move to end and then grab whatever text is left in the file/document. This is a boundary condition that I don’t want to leave hanging loose.

So, here, you are. Ask for whatever details you need and don't find.

Thanks,
Ron


[vba]
Option Explicit
Sub Split_Activities_Out2()
Dim MyDir As String
Dim NewDir As String
Dim OrigFile As String
Dim MySrcFile As String
Dim MyOutFile As String
Dim MyOldOutFile As String
Dim Grade As Long
Dim Unit As Long
Dim MaxGrade As Long
Dim MyGrade As Long
Dim MyUnit As Long
Dim MaxUnit As Long
Dim MyLesson As String
Dim strText As String
Dim start As Long
Dim msg As String ' delete after testing is done

MyOldOutFile = ""
Grade = 5 ' change to 1 for production
MaxGrade = 5
Unit = 3 ' change to 1 for production

' Do While Grade <= MaxGrade
Select Case Grade
Case 4
MaxUnit = 11
Case 5
MaxUnit = 15
Case Else
MaxUnit = 10
End Select

MyDir = _
"C:\Documents and Settings\mckenzier\Desktop\G1-5 NL 2012 Inquiry Support files for DCD\G5"
' MyDir = "C:\Users\Ron\Desktop" ' for laptop
' Do While Unit <= MaxUnit
If Unit < 10 Then
NewDir = MyDir & "\U0" & Unit
Else
NewDir = MyDir & "\U" & Unit
End If


' ChangeFileOpenDirectory newDir ' for real environment
ChangeFileOpenDirectory MyDir & "\Unit-Test\" ' for development purposes

' OrigFile = "G" & Grade & "U0" & Unit & "_InquirySupport.doc"
OrigFile = "G" & Grade & "U0" & Unit & "_InquirySupport for VBAX.doc"

' Documents.Open FileName:=OrigFile, ConfirmConversions:= _
True, ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
Debug.Print OrigFile
' Make a working copy of the file: (this becomes Documents(2).Name)
MySrcFile = "IS_G" & Grade & "U0" & Unit & "_WorkingCopy.doc"

ActiveDocument.SaveAs FileName:=MySrcFile, _
FileFormat:=wdFormatDocument, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False
Read_Next_Activity:
Documents(MySrcFile).Activate ' return to working file
Get_MyOutFile:
MyOutFile = ActiveDocument.Paragraphs(1).Range.Text
On Error GoTo 0
MyOutFile = Trim(Left(MyOutFile, Len(MyOutFile) - 1))
' we need the following bcs some lines begin with CrLf-pair
If MyLesson = 6 Then Stop
If Len(MyOutFile) = 0 Then
If MyOutFile = vbCrLf Then ' How do I trap and process reaching
GoTo End_of_Unit ' the end of the file? <<< PROBLEM
End If '
Selection.Delete Unit:=wdCharacter, Count:=1
GoTo Get_MyOutFile
End If
strText = ActiveDocument.Paragraphs(2).Range.Text
strText = Trim(Left(strText, Len(strText) - 1))
Debug.Print MyOutFile, strText
' MyOutFile = "LO Name: " & MyOutFile ' used to test the following code
' Debug.Print MyOutFile
If InStr(MyOutFile, "LO Name:") > 0 Then
MyOutFile = Right(MyOutFile, Len(MyOutFile) _
- (InStr(MyOutFile, ":") + 1))
End If
' MyOutFile = "NL_G5_LC_00202_LessonObjectName"
' strText = "Grade 5, Unit 3, Lesson 1"
' note: grade = 1..5, unit = 1..15, lesson = 1..6
'
MyGrade = Mid(strText, InStr(strText, ",") - 1, 1)
Debug.Print strText & "*", Len(strText)
Debug.Print
' If MyGrade <> Grade Then (build an error trap)
MyUnit = Mid(strText, InStr(strText, "Unit") + 5, 2)
If Len(MyUnit) = 2 And Right(MyUnit, 1) = "," Then
MyUnit = Left(MyUnit, 1)
End If
MyLesson = Mid(strText, InStr(strText, "Lesson") + 7, 1)
' And then we put it all back together to name the output file:
MyOutFile = RTrim(MyOutFile) & " G" & MyGrade & " U" & MyUnit & _
" L" & MyLesson
Debug.Print MyOutFile

If MyLesson = 6 Then Stop

' Delete the first line of the activity
With Selection
.Find.ClearFormatting
.HomeKey Unit:=wdStory
.EndKey Unit:=wdLine, Extend:=wdExtend
.Delete
' Get from beginning of doc to 1st instance of Find("My Notes")
.HomeKey Unit:=wdStory
With .Find
.Text = "My Notes"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
.Find.Execute
.EndKey Unit:=wdLine
' If MyLesson = 6 Then GoTo Jump_over
.GoToNext (wdGoToPage) 'will this get me to manual page break? (yes)
Jump_over:
.HomeKey Unit:=wdStory, Extend:=wdExtend
End With
' Is there a way to "drop my anchor" at the beginning of the text and extend the
' selection down to the end of the line where FIND locates the desired phrase,
' highlighting as I go, instead of resorting to the above which is kinda kludgy?

' Cut selected text
Selection.Cut
If MyOutFile <> MyOldOutFile Then
If MyOldOutFile <> "" Then
Documents(MyOldOutFile & ".rtf").Close
End If
Documents.Add DocumentType:=wdNewBlankDocument
ActiveDocument.SaveAs FileName:=MyOutFile & ".rtf", _
FileFormat:=wdFormatRTF, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData:= _
False, SaveAsAOCELetter:=False
End If
Documents(MyOutFile & ".rtf").Activate

Selection.PasteAndFormat (wdPasteDefault)
' Save new doc with MyOutFile name, leave open
ActiveDocument.SaveAs FileName:=MyOutFile & ".rtf", _
FileFormat:=wdFormatRTF, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData:= _
False, SaveAsAOCELetter:=False
MyOldOutFile = MyOutFile
GoTo Read_Next_Activity
End_of_Unit:
' look at Error in Msg Box use for dev education to see Err # for EOF wdFile
If Err.Number <> 0 Then
msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
End If
Debug.Print MyOutFile & ".rtf" & "*"
Err.Clear
' Increment Unit Counter
Unit = Unit + 1
Documents(MySrcFile).Close '// close working copy of unit file
Documents(MyOutFile & ".rtf").Close '// close the last lesson file

' Loop '// get next unit
Grade_Done:
' Increment the Grade Counter
' Grade = Grade + 1

' Loop

End Sub
[/vba]

[mdmackillop]

If MyLesson = 6 Then Stop

Error here. What is MyLesson?

[RonMcK]

Malcolm,

Yikes! Mea culpa. Hmm? Well, the DIM should be to a Long and not a String. This line of code was to alert me when the program started on the 6th (last) Lesson where my trouble is. I wonder why it didn't error for me?

Sorry,

[Frosty]

VBA will attempt to convert the "easy" stuff when it can. So it will evaluate 6 as "6" if it thinks it can avoid causing an error.

[RonMcK]

Perhaps, that explains why it didn't error on me? So, have you had a chance to look at the revised program, see what I learned, and scope out the 2 remaining challenges?



Thanks,

[Frosty]

I think this is a good example of when to modularize your code. I would first simplify the example document (it doesn't need to have *that* much greek).

Honestly, I don't have time to provide you with a full solution, but I think I understand enough to give you some helpful pointers.

It will help, first and foremost, to break this down into workable units. One of the reasons you're feeling overwhelmed is because you've given yourself an overwhelming task (as evidenced by the massively complex single subroutine in your macro document).

It looks to me like you need to develop the following functions (which I will give brief samples of).

1. Identify a range of text in your main document which is a single "Lesson" (these seem to be divided by your manual page breaks). Here is a sample of what I mean... when first learning ranges, it will help you to select and see what you get, but you don't need to select them once you're done
[vba]
'selecting always helps when working with ranges
Sub SelectMyLesson()
Dim rngSearch As Range
Set rngSearch = ActiveDocument.Content
'find it and select it
FindMyLesson(rngSearch).Select
End Sub
'returns an expanded range, selecting the entire document if page break not found
Function FindMyLesson(rngLookWhere As Range) As Range
Dim lStart As Long
lStart = rngLookWhere.start
With rngLookWhere.Find
.Text = "^m"
'if we found our page break,reset the beginning of our found range to the original start
If .Execute = True Then
rngLookWhere.start = lStart
Else
Set rngLookWhere = ActiveDocument.Content
End If
End With
Set FindMyLesson = rngLookWhere
End Function
[/vba]

2. Now that you have a "lesson" range, you need to identify the type of lesson... which is some string manipulation (the 8th character of a particular line, which it looks like you're pretty close on anyway) -- this can probably be broken into multiple functions as well, but you probably just need to start at the beginning of the range you got from #1, and start doing some string manipulation to identify the lesson type.

Of particular note in this is area is the Split() function, which allows you to automatically split a line like:
Grade 5, Unit 3, Lesson 1 into a simple array
myArray = Split("Grade 5, Unit 3, Lesson 1", ",")
And then your array would be
MyArray(1) = "Grade 5"
MyArray(2) = "Unit 3"
MyArray(3) = "Lesson 1"
I think you'll know what to do from there...

3. A function which takes an identified range of text, and saves it into a new document, with all of the information you've gathered (you're already doing the saving stuff, so I won't demo that).

4. Separate out your functions/subroutines to deal with the different lesson types (but which still use a single "save this document to here with this name" function).

Hope this helps. Sorry I can't do more at the moment, but like most people... I have to focus the majority of my time on the coding that pays me

Identifying the individual parts of your lessons by using ranges will help a bunch... as well breaking your functions into very testable units.

Remember that with ranges you can .MoveStart and .MoveEnd (and a host of other things) as as test individual characters with all the string commands (if you can't use the .Find object, which is really a lot faster and more powerful).

And write your routines initially so that you always pass a range in... for example, in the above coding sample I did, rather than just pressing F5 in the SelectMyLesson, you can type FindMyLesson(Selection.Range).select in the immediate window, and move your cursor around in the document to see how it works.

This makes the coding process a lot easier.

Hope that helps!

- Jason

[Frosty]

Quick followup-- if you find yourself using a lot of GoTo commands in your code to jump up and down around your subroutine, it generally means you're going to benefit (in some fashion) from creating additional subroutines.

In this case... it will help others (or, at least, me) help you for free

[RonMcK]

Jason,

Thank you very much for the thorough tutorial and for not simply crafting a complete solution. Your guidance (again) will help me extend my knowledge and improve my coding style.

I'll work more on this over the weekend and post "smaller" questions as they arise.

Again, many thanks for your help.

Regards,

[Frosty]

Just to be really clear, probably should have done something like this in the sample code:
set rngLesson = FindMyRange(rngSearch)
rngLesson.Select

Instead of just selecting the actual function... that would clarify how to proceed.. since you then would need to play around with rngLesson (the results of FindMyRange).

[RonMcK]

Jason,

Thanks, again.