I am sure this is a simple fix that I am not seeing but I keep getting runtime error 9 subscript out of range on the following line of code:
Set c = ws1.Cells.Find(What:="Vacancy", LookAt:=xlPart)
This is the full code below. I have run the debugger to cursor and it refuse to go past the above line of code. I am trying to search ws1 for the word Vacancy that can be in any row or any cell; once Vacancy is found I need it to move over 1 space to search if vacancy has a number after it and to add comment box to be inserted-in that comment box I need the following data inserted: the vacancy status which is pulled from ws2 by taking the number following Vacancy and searching ws2 column R for that number. once that number is found the information in column b of the same row needs to be inserted into the comment box on ws1. I also would like to eventually put a search in that would search the cell below Vacancy for a job title and insert a comment box on that cell with either a hyperlink or the text from ws3("job description") that would match the job title in column A of ws3 and then insert the text from column b.
Sub VacancyCheck()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim c
Dim d
Set ws1 = ActiveSheet
Set ws2 = Worksheets("FY_15_Staffing")
Set c = ws1.Cells.Find(What:="Vacancy", LookAt:=xlPart)
If Not c Is Nothing Then
firstAdd = c.Address
Do
If c.Comment Is Nothing Then c.AddComment
c.Comment.Visible = False
Set d = ws2.Range("R:R").Find(Split(c.Value, "Vacancy")(1))
If Not d Is Nothing Then
c.Comment.Text Text:="from (FY_15_Staffing)" & Chr(10) & "column r, row" & b.Row
End If
Set c = ws1.Cells.Find("vacancy", LookAt = xlPart, after:=c)
Loop While c Is Nothing And c.Address <> firstAdd
End If
End Sub
Bookmarks