I am just getting back into using VBA to create unique functions for MS Excel. I am trying to create code that will search the entire worksheet that is active for the phrase "Vacancy". For each time "Vacancy" is found the code will insert a comment box. The comment box will then search if after Vacancy a number is entered (so for the example the ActiveCell being searched would contain either "Vacancy 74" or text not containing "Vacancy". For the cells not containing "Vacancy" I do not want anything to be processed on them.)
If the "Vacancy (and a #)" is found a comment box will be inserted on that active cell- the Comment box will take the number search another worksheet (FY_15_Hiring) for the number in column r. If that number is found in column r, then text from(FY_15_Hiring) column d, row (=to the row where the number was found in column r).

I know this is a lot but I am getting stumped on just the search and insert portion. Please help.