Code is only running once instead of multiple times, code is also spitting out a runtime e

I have gotten this code to at least find Vacancy once. I added the Text:="TEST" just as a test insert because the code below the add.comment is not inserting any information from the other sheet. I think if I can at least get the code to work perfectly on a single run and then figure out away to make it run until all occurrences of Vacancy are processed might be the best avenue. I am really trying to learn as I go here and was given this code second hand.

Please Login or Register  to view this content.

What is the big picture on what you are trying to accomplish?

Big Picture is taking the occurrence of vacancy along with the number after the word vacancy so for example "Vacancy 65" to search worksheet FY_15_staffing. the occurrence of Vacancy will add a comment box to that cell. In the comment box is whatever word is stored in column b, which is the status of that vacancy (canx, filled, offered, etc) of FY_15_Staffing in the corresponding row that the number appears in from row r (which is where the vacancy number is stored). And to have the code run so that all occurences of the word vacancy are given the comment box and the respective job status.

searching the ActiveSheet for the word Vacancy.
>If Vacancy is found then
>add a comment box with Text
>Text is equal to string value that is located on Sheet("FY_15_Staffing") column.b with row equal to the number following vacancy. That value is stored in row r on sheet FY_15_Sheet

Loop for all occurrence of Vacancy

I think i am starting to get the picture. Could you submit an example workbook which shows what you got going on. That way I also have data to work with.

testcopy.xlsx

This is a test copy I have been trying to get to run so I can then insert the code into the larger file.

Ultimately I think your problem is this line

Please Login or Register  to view this content.

However, I am rewriting it and will get back to you soon.

I have no idea why I had to do it like this. The loop for some reason wasn't working normally for me. This however worked

Please Login or Register  to view this content.

I got this to run for 3 of the fields I have saved the code in the excel file.

Please Login or Register  to view this content.

is spitting out runtime error 9

Correct to above posting-
Code works great as long as the word Vacancy has a number after it. How do I write code to if vacancy has nothing following it to insert "ELT REF Approval # Needed" into the comment box? Also how do I get it not to cause an error once all the Vacancy occurrences are edited?

Please Login or Register  to view this content.

You in my opinion are a god amongst mortals...Thank you so much! Now on to the next search to figure out how to take the Job titles and insert a comment box that will pull the job description

Found 1 dilemma once the comment box is attached it wont update. Example if just Vacancy is present and then they enter a number after vacancy it does not change the ELT REF Approval to what the vacancy status is

Please Login or Register  to view this content.

Once again you are a life saver!

It sort of works, perhaps I am not putting the VBA code in correctly. I have it to run on sheet activation. If Vacancy was there previously and is changed to Vacancy 74 (for example) it will update. But if Vacancy is removed entirely the comment box stays with the last know status represented. I am thinking its me because of this. Do you have a suggestion on a better way to make the vba run then activation?

Please Login or Register  to view this content.

If I enter Vacancy everything works>I add a number everything works>I delete the number but leave vacancy it errors out on this line of code with a runtime error 91

But if Vacancy is removed entirely the comment box stays with the last know status represented

This is a little more challenging. You need to devise a mechanism to tell the code when to check for comment boxes attached to cell then confirm whether said cell has a value. This can be done many ways, I will let you decide upon the timing and implementation. The below code will remove comments that are assigned to any cell which doesn't have the word "Vacancy" in it.

Please Login or Register  to view this content.

I just realized that you have merged cells. No wonder why this has been giving me such fits. Merged cells and VBA do not work well together at all. I am going to step back for a bit on this. I would seriously consider removing the merged cells from your sheet.

I am pretty noob on doing this sort of thing do you have a suggestion on how you would handle it?

After further review I think the merged cells is most likely 100% of the problem. I removed the merge cells and it worked flawlessly. Note I did change one like to correct the comment text in a certain situation.

Please Login or Register  to view this content.

I would love to but the whole excel book is being managed by other people, and I got tasked with creating this mess. Frankly I told them I wanted to scrap the whole thing and got told in not so many words as tough. Which is part of the reason I am trying to make it dummy proof knowing the people that will be in it once I am done