Adding a Comment Box

**clpickett3** · 03-02-2015, 02:45 PM

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.

**walruseggman** · 03-02-2015, 03:59 PM

Sub clpickett3()

Set ws1 = ActiveSheet
Set ws2 = Worksheets("FY_15_Hiring")

Set c = ws1.Cells.Find("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_Hiring)" & Chr(10) & "column r, row " & d.Row
    End If
    Set c = ws1.Cells.Find("Vacancy", lookat:=xlPart, after:=c)
    Loop While Not c Is Nothing And c.Address <> FirstAdd
End If

End Sub

**clpickett3** · 03-02-2015, 05:51 PM

Sub clpickett3()

Set ws1 = ActiveSheet
Set ws2 = Worksheets("Sheet3")

Set c = ws1.Cells.Find("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 Not c Is Nothing And c.Address <> FirstAdd
End If

End Sub

I am getting a runtime error stating subscript out of ranger

**walruseggman** · 03-02-2015, 06:16 PM

Edit: Also just noticed you changed d.Row to b.Row that's probably your error.

I thought you wanted to search FY_15_Hiring?

ws1 is whatever sheet has the "Vacancy" s on it
ws2 is whatever sheet you want to search for the matching number

If that doesn't help, please post your workbook or an example workbook that replicates the error.

**clpickett3** · 03-02-2015, 06:37 PM

They changed the name of the 1 worksheet to FY_15_Staffing. Column R is still being searched for the number that is after the word vacancy on WS1. Column B houses the information they wish to be displayed correlating to the row found in column r.ws1.jpg ws2.jpg