Range "H2" rDest. Replace s, " " re. PivotFields sWordListTitle. Count - 2. Delete Application. Total number of characters consists of the characters in each word, plus the spaces between each word, in the long string that is generated within the code. There is no error checking done for this, but if you think it will be an issue, we can do it. Right now, you are orders of magnitude below those limits.
As Ron suggsted user need not be familiar with Macro. If you can put the above in macro then it will be faster. If he is willing to use a Macro, there is no need to use Word. Depending on what he wants for output, and the specifications of his data, it is trivial to generate a list of unique words; count them; sort them; and output them in some range. For example "forgo? Here is a macro that assumes all of the strings are in separate rows in column A; and outputs a list of words in column D, with the word counts in column E.
The list is sorted ascending, case-insensitive, alphabetically. It could be easily sorted numerically, either ascending or descending. Because these assumptions may not match the OP's data and requirements, it should be regarded only as a first draft.
A "word" is strictly defined in the macro as a contiguous series of letters, digits or the underscore; but that definition is easily changed, if necessary. Execute c. Text If mc. Count Next c Next i. Thanks Ron and Learning for the quick response.
I just spoke with the customer and he is fine with a macro as long it is simple and automated. Is there a way to make your code shorter? I may not need the sorting code since Excel can do that with a filter unless you think otherwise.
If you are not going to sort the results, the sort routine as well as the part in the main code that calls the sort routine can be eliminated. If your end user wants the results sorted, then running a macro followed by going through the "sort" routine on the worksheet adds a an extra step for your end user.
That would seem to be more complicated than including everything in a single macro, where only one call is required; perhaps with a multi-choice input box for the sorting e. If your installation of Excel cannot sort a row database relatively quickly, there is something strange, indeed, going on either with your installation or your environment. How does sorting "using filters" help that problem for you? Getting back to the macro, the contained sort routine is pretty quick and has nothing to do with Excel's sort routine.
But there are ways to determine where the macro is getting "hung up" and fix it. Generally by using breakpoints. Assuming you have reported your data configuration and set up accurately, and haven't made an inadvertent error in adapting the macro to your specific requirements, I would be concentrating on the word counting routine rather than the sort.
Is it possible for you to share your workbook, or a sanitized version? If so, you can post it at a publically accessible site, such as Skydrive, and post a link here. If that is too public, you can try to send it to me at. As I suspected, when running the macro against a larger sample about rows and unique words , the slowdown is in the word counting routine.
If you are using VBA6. Value If mc. Clear rDest. Columns 1. Thank you for sending me that file. With the revised routine the last one I posted , on my computer, it took about 25 minutes to process the approximately lines.
These generated a string of over one million characters and about 22, unique words. Examining your data a bit, I would suggest reviewing the characters that are part of a word, and letting me know if you want to add anything to what I had written previously: letters, digits and the underscore. I have some ideas for a faster word counting routine if the speed is not satisfactory.
Let me know although I may work on it anyway. On my computer, the last file you sent me took 25 minutes to complete. Glad to help. And, if you are happy with the result, please mark my last version as an answer. Easier than I thought. The following cut the run time of your Book5 sample from 26 min to 15 min.
Further testing to follow, but not today. Replace c. Text, " " re. Thank you for that information. It turns out that both methods were giving incorrect counts for different reasons. The first, regular expression method, was doing a case sensitive count depending on the case of the unique word. This will undercount the number of words.
The second method would give an incorrect result if the unique word was duplicated. In other words " yen yen " would only count as one instance of yen rather than two.
Under these circumstances, I would expect the 2nd method to be give a higher result, which is what you have observed. There was an issue, in the preceding version, with missing a count where the same word was the last word in one row, and the first word in the subsequent row.
This last version corrects that, so I would expect the increased count. This was superfast. In the workbook you sent me, that was one of the worksheets.
Range "A1:B1" or whatever so long as the sheet exists. I was originally going to do it that way; but then changed my mind to put it onto the same sheet as the original data, leaving the other stuff in there to make it easier for you to change it, if you wanted to.
The source worksheet does not have to have a particular name. I only used Color because that was the name it had in the workbook you sent. You can set it to whatever you want.
And if you want it to be the currently active worksheet the sheet that is active at the time you run the macro , merely set it to that. That is the only change you need to make to have the Source data and the results show up on the worksheet that is active at the time you run the macro. Doesn't matter what the name is.
Sorry for not responding earlier. Been busy. When I implement the change you suggested, I get a VB error. This is what I have.
Range "A1". How do you know a word count in Microsoft Word ? Helpful word count tips for translators. Related Posts 20 Jul. How do you figure out a word count in Microsoft Word ? Word count in Microsoft Word Online. Leave a Comment Cancel reply. Find Us On. Recent Posts All the tricks for Trans October 29, What count units do tran October 15, How to recognize text on October 11, Harry Potter Word Count. August 9, Earn Credits.
Step 2: Specify what to count. Step 3: Number of words. The total number of words counted will be displayed in the status bar. Nick42 Apr 28, at pm. Yotta Apr 29, at pm. This office option is very useful in schools to counting the words at examination. ErikN May 12, at pm.
0コメント