Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,576
    Thanks
    44
    Thanked 75 Times in 70 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 18:52.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,980
    Thanks
    157
    Thanked 773 Times in 705 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
    3,412
    Thanks
    164
    Thanked 641 Times in 609 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,576
    Thanks
    44
    Thanked 75 Times in 70 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
  •