Glossary Generating Macro

Thank you for letting me know. I have updated the OP.

Originally Posted by macropod

Cross-posted at: http://www.vbaexpress.com/forum/show...ing-Macro-Help
For cross-posting etiquette, please read FORUM RULE 8: http://www.excelforum.com/forum-rule...rum-rules.html

Originally Posted by 6StringJazzer

Thanks to macropod for pointing this out and providing the link. I will emphasize that we have an actual rule about this:

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Thank you very much Bernie, but I have more than 5 items for some of the words/phrases. I should also clarify that the list above is just an example, the actual list is longer and comprises of phrases and sentences, along with just words. Also, I would like the output like this:

Word 1 - suggestion 1, suggestion 2, ...... suggestion n
word 5 - suggestion 1, suggestion 2, ...... suggestion n
.
.
word n - suggestion 1, suggestion 2, ...... suggestion n

And all the words identified from the list should be highlighted in document as well and suggestions should be summarized at the bottom.

Originally Posted by Bernie Deitrick

I'm sure Word VBA experts will be aghast at my code, but it worked for me

Please Login or Register  to view this content.

Also, I received the following response from link posted in post#1. However, this does not work as I will be sending this to other users who may or may not have a separate file. Also, I don't know how to use another word file in this.

Please let me know if you have any other suggestions than mentioned in this post

Please Login or Register  to view this content.

Originally Posted by macropod

It would be helpful if you actually specified you needs at the outset, rather that having people waste time working on the matter only to find out after the event you haven't bothered to include something you regard as important. If the entries aren't in a file someone can edit, how is anyone except a programmer supposed to maintain the list? Besides which, why wouldn't the other users have a separate glossary file? Surely, if you intend them to use a particular glossary, you could send them that with the macro...

Sorry, I indicated I am new at this. I did not know that it will be important for the query.
Yes I would prefer to have it in the macro itself rather than having a separate file. I have the data in a word file and it is sent to users. However, the macro is supposed to eliminate the use of the file and do the "search and suggest" for them. Below is a screenshot of some of the items.

http://oi61.tinypic.com/29kzajq.jpg

Summary for those who cannot see the link:

Words: Ensure, guarantee, satisfy
Suggestions: Support, facilitate, advise, assist, assess
Words: Maximize, optimize, minimize
Suggestions: Increase, improve, decrease, reduce



Here is what I think we can do (atleast thats what I would have done in C++)

Create a 2D array with n rows. The first few elements in Array based on list above can be

[1,1] = ["ensure", "Support, facilitate, advise, assist, assess"]
[2,1] = ["guarantee", "Support, facilitate, advise, assist, assess"]
[3,1] = ["satisfy", "Support, facilitate, advise, assist, assess"]
and so on


And once the macro runs, it looks for the words in first row and if it is in the document, it gets highlighted and both the word and the suggestions are posted at the end of the document.



So for example, if a document has ensure, satisfy and maximize in it. then those words will be highlighted in the document and the following will be displayed at the bottom of the document:

Ensure : Support, facilitate, advise, assist, assess
Satisfy: Support, facilitate, advise, assist, assess
Maximize: Increase, improve, decrease, reduce

and so on.


Hope this helps!

Thank you for the help so far.

That does work but only for the first instance. So whatever word/phrase it finds first it is highlighted and then it does create a line at the bottom, but no other words are picked up.

Originally Posted by macropod

Try:

Please Login or Register  to view this content.

Although not strictly necessary, each glossary entry is on a separate line.

That did work, but when I try to add all the items (words/sentences), it gives this error:

"Too many line continuations"

Originally Posted by Bernie Deitrick

Try it like this:

Please Login or Register  to view this content.

Thank you for the help!
I get the following error - Run-time error '9'
Subscript out of range

Here is what I have:



Sub CompileDocumentGlossary2()
Application.ScreenUpdating = False
Dim ArrTerms(1 To 45) As String, StrTmp As String, StrOut As String, i As Long
ArrTerms(1) = "Audit - Comment on, analyse, study, read"
ArrTerms(2) = "Review - Comment on, analyse, study, read"
ArrTerms(3) = "Compile - Comment on, analyse, study, read"
.
..
...


to ArrTerms(44) = "........."



For i = 0 To 45
With ActiveDocument.Range
With .Find
.MatchWholeWord = True
.MatchCase = False
.Replacement.Highlight = True
StrTmp = ArrTerms(i)
.Text = Split(StrTmp, " - ")(0)
.Execute Replace:=wdReplaceAll

If .Found = True Then StrOut = StrOut & vbCr & ArrTerms(i)
End With
End With
Next i

ActiveDocument.Range.InsertAfter StrOut

Application.ScreenUpdating = True
End Sub

Originally Posted by Bernie Deitrick

Change to this style to do away with the continuations.

If you have, say, 100 terms:

Dim ArrTerms(1 To 100) As String
ArrTerms(1) = "Paper - report, transcript, summary"
ArrTerms(2)= "Financial documents - balance-sheet, income statement, forecast, budget"
ArrTerms(3) = "Expert - professional, teacher, experienced"
.....
ArrTerms(100) = "Excel VBA - Hard to use, Need lots of experience"

and change

For i = 0 To UBound(ArrTerms)

to

For i = 1 To 100

Yes, I updated all the terms. i just didnt paste it as it is lot of data.

I updated to "for i = 0 to Ubound(Arrterms)"

However, I am still getting the same error and when I hit Debug, it takes me to the following line:

StrTmp = ArrTerms(i)

Originally Posted by macropod

You can't use terms like:
ArrTerms(44) = "........."
All of the terms you use must be populated as per terms 1 - 3 and the 45 in:
For i = 0 To 45
cannot exceed the highest array element. A simpler way of ensuring that is to use:
For i = 1 To UBound(ArrTerms)
There is no need to hard-code the number here as Bernie seems to suggest in post #16

Thank you that worked. The only little bug remaining is that it is using the last used colour to highlight instead of Yellow.

Originally Posted by Bernie Deitrick

Sorry - I was brain-dead:

For i = 0 To UBound(ArrTerms)

should have been

For i = 1 To UBound(ArrTerms)

so change

For i = 0 To 45

to

For i = 1 To 45

It worked! Thank you very much for all your help and patience!

Originally Posted by Bernie Deitrick

We had the line

Options.DefaultHighlightColorIndex = wdYellow

Did you not include that?