Results 1 to 30 of 30

Code is giving Runtime Error 9 Subscript out of range

Threaded View

  1. #1
    Registered User
    Join Date
    03-02-2015
    Location
    ridgecrest, ca
    MS-Off Ver
    2010
    Posts
    54

    Unhappy Code is giving Runtime Error 9 Subscript out of range

    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
    Last edited by clpickett3; 03-03-2015 at 03:27 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help with code to hide 2 rows please (banging my head)
    By Darkprophecy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-05-2015, 09:34 AM
  2. How to account for head to head matchup in Excel with other tie breakers
    By Biogeek1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-26-2013, 05:18 PM
  3. Head against a brick wall !!
    By WhytheQ in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2006, 08:10 AM
  4. Banging head against wall with VLOOKUP
    By David in forum Excel General
    Replies: 4
    Last Post: 07-05-2005, 09:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1