+ Reply to Thread
Results 1 to 5 of 5

Works as Macro but not in code

  1. #1
    SueJB
    Guest

    Works as Macro but not in code

    This is really perplexing me! I have a very, very simple action to perform
    (select a worksheet, select column A, clear contents, return to different
    sheet).

    If I record a Macro it works fine. If I take the code from the macro and
    use it as part of a longer sub, it falls over.

    Code:

    Sub ClearA()

    Sheets("Statistics").Select
    Columns("A:A").Select ' // falls over here
    Selection.ClearContents
    Sheets("NameList").Select

    End Sub

    It falls over with the error message :

    Runtime Error '1004'
    Select method of Range class failed


    I must be missing something obvious but I'm darned if I can see why it works
    as a Macro and not in a sub. Can anyone help please?

    This shouldn't have held me up for the last two hours but it has!!

    Thanks in advance
    Sue




  2. #2
    Gary Keramidas
    Guest

    Re: Works as Macro but not in code

    try this and see if it works for you

    Sub ClearA()

    Sheets("Statistics").Columns("A:A").ClearContents
    Sheets("NameList").Select

    End Sub

    --


    Gary


    "SueJB" <[email protected]> wrote in message
    news:[email protected]...
    > This is really perplexing me! I have a very, very simple action to
    > perform
    > (select a worksheet, select column A, clear contents, return to different
    > sheet).
    >
    > If I record a Macro it works fine. If I take the code from the macro and
    > use it as part of a longer sub, it falls over.
    >
    > Code:
    >
    > Sub ClearA()
    >
    > Sheets("Statistics").Select
    > Columns("A:A").Select ' // falls over here
    > Selection.ClearContents
    > Sheets("NameList").Select
    >
    > End Sub
    >
    > It falls over with the error message :
    >
    > Runtime Error '1004'
    > Select method of Range class failed
    >
    >
    > I must be missing something obvious but I'm darned if I can see why it
    > works
    > as a Macro and not in a sub. Can anyone help please?
    >
    > This shouldn't have held me up for the last two hours but it has!!
    >
    > Thanks in advance
    > Sue
    >
    >
    >




  3. #3
    Norman Jones
    Guest

    Re: Works as Macro but not in code

    Hi Sue,

    Try:

    Sub ClearA()

    Sheets("Statistics").Columns("A:A").ClearContents
    Sheets("NameList").Select

    End Sub

    ---
    Regards,
    Norman



    "SueJB" <[email protected]> wrote in message
    news:[email protected]...
    > This is really perplexing me! I have a very, very simple action to
    > perform
    > (select a worksheet, select column A, clear contents, return to different
    > sheet).
    >
    > If I record a Macro it works fine. If I take the code from the macro and
    > use it as part of a longer sub, it falls over.
    >
    > Code:
    >
    > Sub ClearA()
    >
    > Sheets("Statistics").Select
    > Columns("A:A").Select ' // falls over here
    > Selection.ClearContents
    > Sheets("NameList").Select
    >
    > End Sub
    >
    > It falls over with the error message :
    >
    > Runtime Error '1004'
    > Select method of Range class failed
    >
    >
    > I must be missing something obvious but I'm darned if I can see why it
    > works
    > as a Macro and not in a sub. Can anyone help please?
    >
    > This shouldn't have held me up for the last two hours but it has!!
    >
    > Thanks in advance
    > Sue
    >
    >
    >




  4. #4
    SueJB
    Guest

    Re: Works as Macro but not in code

    Hi Gary

    Yes, it worked ... just a case of wrong syntax, I suppose?

    Many thanks for such a quick and helpful reply

    SueJB

    "Gary Keramidas" wrote:

    > try this and see if it works for you
    >
    > Sub ClearA()
    >
    > Sheets("Statistics").Columns("A:A").ClearContents
    > Sheets("NameList").Select
    >
    > End Sub
    >
    > --
    >
    >
    > Gary
    >
    >
    > "SueJB" <[email protected]> wrote in message
    > news:[email protected]...
    > > This is really perplexing me! I have a very, very simple action to
    > > perform
    > > (select a worksheet, select column A, clear contents, return to different
    > > sheet).
    > >
    > > If I record a Macro it works fine. If I take the code from the macro and
    > > use it as part of a longer sub, it falls over.
    > >
    > > Code:
    > >
    > > Sub ClearA()
    > >
    > > Sheets("Statistics").Select
    > > Columns("A:A").Select ' // falls over here
    > > Selection.ClearContents
    > > Sheets("NameList").Select
    > >
    > > End Sub
    > >
    > > It falls over with the error message :
    > >
    > > Runtime Error '1004'
    > > Select method of Range class failed
    > >
    > >
    > > I must be missing something obvious but I'm darned if I can see why it
    > > works
    > > as a Macro and not in a sub. Can anyone help please?
    > >
    > > This shouldn't have held me up for the last two hours but it has!!
    > >
    > > Thanks in advance
    > > Sue
    > >
    > >
    > >

    >
    >
    >


  5. #5
    SueJB
    Guest

    Re: Works as Macro but not in code

    Many thanks Norman

    SJB

    "Norman Jones" wrote:

    > Hi Sue,
    >
    > Try:
    >
    > Sub ClearA()
    >
    > Sheets("Statistics").Columns("A:A").ClearContents
    > Sheets("NameList").Select
    >
    > End Sub
    >
    > ---
    > Regards,
    > Norman
    >
    >
    >
    > "SueJB" <[email protected]> wrote in message
    > news:[email protected]...
    > > This is really perplexing me! I have a very, very simple action to
    > > perform
    > > (select a worksheet, select column A, clear contents, return to different
    > > sheet).
    > >
    > > If I record a Macro it works fine. If I take the code from the macro and
    > > use it as part of a longer sub, it falls over.
    > >
    > > Code:
    > >
    > > Sub ClearA()
    > >
    > > Sheets("Statistics").Select
    > > Columns("A:A").Select ' // falls over here
    > > Selection.ClearContents
    > > Sheets("NameList").Select
    > >
    > > End Sub
    > >
    > > It falls over with the error message :
    > >
    > > Runtime Error '1004'
    > > Select method of Range class failed
    > >
    > >
    > > I must be missing something obvious but I'm darned if I can see why it
    > > works
    > > as a Macro and not in a sub. Can anyone help please?
    > >
    > > This shouldn't have held me up for the last two hours but it has!!
    > >
    > > Thanks in advance
    > > Sue
    > >
    > >
    > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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