Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts

    VB - run time error 9 - why?

    I'm writing some VB code to manipulate cells in spreadsheets. But when I change spreadsheet tabs, I get "Runtime error 9 - subscript out of range" Anyone got any idea why? A code fragment is below. [Incidently, further up the page, I change tabs to do other things as well - which works OK]

    Cheers

    Alan


    Set wsdata = Worksheets("Data Entry") <================ This line works fine ==================
    Set wbData = ActiveWorkbook

    For j = 1 To NumRows
    wsdata.Cells(j + 1, 2) = Cells(assetFound, rec_Location(j))
    Next j
    OriginalAssetNumber = Cells(assetFound, 1) ' Save this for later

    Set wsdata = Worksheets("Data Entry2") <=============== error is on this line - even if I change it to "Data Entry" it still fails =================
    Set wbData = ActiveWorkbook

    For j = 1 To NumRows2
    wsdata.Cells(j + 1, 2) = Cells(assetFound, rec_Location(j))
    Next j

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It means in the activeworkbook (active when the code is run) the worksheet name does not exist...

    Have you changed the activeworkbook somewhere? Worksheets... is implicitly the activeworkbook unless explicitly listed

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Yes, that is exactly what happened. I'd opened another workbook which implicitely made it the active one.

    Many thanks

    Alan

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    To avoid the problem in code you can explicitly list the workbook:

    Set wsdata = ThisWorkbook.Worksheets("Data Entry")
    [Note: thisworkbook is the workbook that the code is in, it may or may not be the activeworkbook]
    OR

    set wbData = Workbooks("Data")
    Set wsdata = wbdata.Worksheets("Data Entry")

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    alan sh (2013-06-06)

  6. #5
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Thanks Steve -I can try that.

    For now, I have set my w(x)data values at the beginning of the code - so they are frozen, no matter what happens. Seems to work OK.

    regards

    Alan

Posting Permissions

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