+ Reply to Thread
Results 1 to 7 of 7

runtime error '9'

  1. 09-20-2014, 11:51 AM #1
    geekohi
    geekohi is offline
    Registered User
    Join Date
    09-13-2014
    Location
    australia
    MS-Off Ver
    2013
    Posts
    11

    runtime error '9'

    Hi there, thx for your attention.

    I have attached my excel macro enabled spreadsheet for your regard.

    My issue:
    In the 'Stocks' sheet, if you press on button 'Load all Data' , the program will generate runtime error '9'.

    I have this spreadsheet work for other occasions but I don't know why it is not working on this workbook.

    I tried running the single macro itself "sheet22.loaddata3" and it does work when a single macro itself is being run.

    However, when I run the "loadalldata" macro to run all macro at the same time, I have this error.

    I don't know how am I getting the error!

    Any help would be truly appreciated!!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-20-2014, 12:28 PM #2
    AlphaFrog
    AlphaFrog is offline
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: runtime error '9' 

    Please Login or Register  to view this content.
    The code sets W as the active sheet before Stocks3 is activated. Then Stocks3 is activated after.
    The worksheet variable W may not be Stocks3.

    Set W after activating Stocks3, or set it specifically as Stocks3

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.
    Reply With QuoteRegister To Reply
  3. 09-20-2014, 12:33 PM #3
    geekohi
    geekohi is offline
    Registered User
    Join Date
    09-13-2014
    Location
    australia
    MS-Off Ver
    2013
    Posts
    11

    Re: runtime error '9'

    Thx AlphaFrog!!! Appreciate it!
    Reply With QuoteRegister To Reply
  4. 09-20-2014, 12:54 PM #4
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: runtime error '9'

    Simplest idea, move all your macros into standard MODULE, out of the sheet modules.

    Then call them with a standard:
    Please Login or Register  to view this content.


    -------------
    All your LoadData macros look identical. You should be able to converge them into a single macro looping through the desired worksheet names. Also, Activating and Selecting should be eliminated. Something like this, you may need to test and refine:


    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 09-20-2014 at 01:49 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  5. 09-20-2014, 01:48 PM #5
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: runtime error '9'

    Now that I've spent a few more minutes absorbing that main macro, this version should loop through all your sheets a little more efficiently:
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 09-20-2014, 08:29 PM #6
    geekohi
    geekohi is offline
    Registered User
    Join Date
    09-13-2014
    Location
    australia
    MS-Off Ver
    2013
    Posts
    11

    Re: runtime error '9'

    Hi thre Jbeaucaire,

    I insert a module and try executing your code.

    I got error, and the debug is highlighting this line 

    Please Login or Register  to view this content.
    it load till row 83 of "Stocks2" and then this error popped up.
    Reply With QuoteRegister To Reply
  7. 09-21-2014, 01:46 AM #7
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: runtime error '9'

    When you DEBUG and hover your mouse over sLine, what is the value?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA Error: Runtime Error 1004: AutoFilter method of Range class failed
    By jl22stac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2013, 07:27 PM
  2. [SOLVED] Range error in code, runs alone but not inside my full program, giving runtime error 1004
    By charizzardd in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-23-2012, 03:34 PM
  3. Defining Array - Runtime error 9, Subscript out of range error
    By MaartenW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-03-2012, 07:32 AM
  4. runtime error 3265 error in a VBA Query to pull info from ERP Database
    By NBVC in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-12-2010, 03:59 PM
  5. Excel xmlHTTP object error message - system/runtime error
    By Porky2007 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-23-2007, 09:36 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1