Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    VBA code in 1 function works; not in another

    As part of a VBA function, I have the following code:

    Code:
    Worksheets("TextFile").Activate
        Set My_Range = Range("A1:L10000")
        My_Range.Parent.Select
    The function goes on to successfully modify data, etc., etc.

    Then, there's another VBA function that has the same code in it, in the same place, but does some other data modification later in the code.

    The issue is that when the 2nd VBA function is called, it bombs with

    Run Time Error '1004'
    Selection method of worksheet class has failed.

    The My_Range.Parent.Select line is highlighted.

    What am I missing?
    Last edited by kweaver; 2016-10-31 at 19:52.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    KW,

    The line My_Range.Parent.Select seems redundant since the TextFile sheet is already the active sheet fromt the previous code line Worksheets("TextFile").Activate

    I would just comment out the line and see if your code runs OK without it. If your intention is to select the range (My_Range), then change the line to My_Range.Select without the ".parent" property

    HTH,
    Maud

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    ..are you really talking about a vba function or do you mean a vba subroutine?
    The function goes on to successfully modify data, etc., etc.
    It's just that a vba function doesn't generally modify other data.

    The error message "Selection method of worksheet class has failed." would generally be shown if that worksheet name was misspelt in the code, or if that worksheet was hidden.

    zeddy

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Sorry, subroutine. I might have hidden the sheet after the first subroutine ran correctly...have to check the code again. Maud's idea is also one I will incorporate.

Posting Permissions

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