Counting spefici text within a range

[cameron213]

I am wanting to count the number of times a specific word appears within a range and then assign that value to a cell. I am not sure of the best way to go about this as the code seems to work but I get errors like "Method 'Value' of object 'Range' failed".

This is what I have thus far:

Please Login or Register  to view this content.

I've then called the function.

Is it possible to do this without specifying a static range? For example, as long as each row has a value in column A, then count the number of times the word "pending" appears in those rows?

Thanks for any input

[alansidman]

This will look in Column A for the last record. It will then search column O as required for the range O12 to last record in column O. I hope that is what you meant.

Please Login or Register  to view this content.

[sktneer]

@alansidman

If I am not wrong, you probably did not notice the error in the OP's code in the line Range("D4").Value = wordcount as wordcount is not calculated in the code so will have a value equal to 0. I think the little modification is needed.

Please Login or Register  to view this content.

[cameron213]

That works to a degree as long as you make some changes.

Please Login or Register  to view this content.

It works fine until the number of times the word appears increases or decreases and then it throws this error at me:
Method 'Value' of object 'Range' failed

[sktneer]

I got no error while I ran this code. Unable to understand your issue. Probably more information is required. Better you upload a sample workbook.

[alansidman]

@Cameron213: I ran sktneer's code and it works for me. Make a change and run it again. No issues.

@sktneer: Thanks. Yeah, I overlooked that completely. Went straight to last line issue.

Alan

[cameron213]

Not sure exactly the best way to go about uploading a workbook. The contained information is highly confidential. Should be easy, I know. It throws that error and then another one saying "Not enough system resources to display completely"

[alansidman]

Suggest you copy the file, remove sensitive data or dummy up the sensitive data and then post the file. You do not need to post the entire file, but only enough records to make it clear what you have.

Here is how to post the file.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

BTW:

Here is my file with sample data that works.

[cameron213]

I've created a sample workbook. It contains three columns. Name, Age, and ***. The *** column is a drop down that allows a choice of M or F. The number of Women and Men is then totaled. I am still getting the error. I am assuming it is with the way I am calling the function.

test.xlsmtest.xlsm

[cameron213]

I've downloaded the book you uploaded. I do not see anything happening when I create a new row and insert the word pending. The number isn't increasing even if I close and re-open it. I've even saved it as macro-enabled.

[sktneer]

Make sure you also insert a value in col. A in the new row because if there is no value in col. A when new row is inserted it will not count the value pending in col. O.

[cameron213]

I did add a value to the A column and then put a value in O. Even if I changed some of the previous values around nothing happened.

[sktneer]

That is so strange! What value you are putting in col. O exactly? Are you still getting an error while running the code? You can upload the workbook which you have downloaded with some new rows added in it. Lets see then what's wrong there.

Note: After making changes, you have to run the code again as this is not a formula so will not automatically reflect the new value in D4.

[cameron213]

test.xlsm

Maybe I am not understanding correctly or explaining correctly. I did upload a workbook for you to have a look at.

I download the one you uploaded. I inserted values in Column A and within the same row in Column O inserted Pending or Closed or any word. Once doing so the count never increases or decreases.

Is the count value supposed to increase on the fly? The only way I found it to work was going to the developer tab, clicking Macros and then running the Test macro.

[sktneer]

The workbook you uploaded had no data in it except headers so useless for us.

As I mentioned in my last post #13, you need to run the macro again after making any change in the workbook to reflect new value in D4.

[alansidman]

If you want it to change every time you make a change in Column O, then put this VBA in the worksheet event

Please Login or Register  to view this content.

Open the VBE
On the left open the VBA project window if not already open
Click on the Worksheet that you want this code for.
In the right window, insert the code.
Save and close the VBE

[sktneer]

Please find the attached sheet.

[cameron213]

Well, having to re-run the macro is pointless for what I am needing. Needs to be able to update on the fly. It doesn't have any data because once you start entering anything it throws that error. If you take a look at the worksheet code you will see the function and the function call within "Worksheet Change"

I think I may have solved the problem.

Please Login or Register  to view this content.