none
Run-time error '1004': Application-defined or object-defined error. Using VBA in Excel 2007

    Question

  • Hello,

    I haven't written anything in VBA in a number of years and was running through a tutorial to refresh my basic skills.  I was using Excel 2003 when I last was writing VBA macros. 

    I seem to randomly encounter "Run-time error '1004': Application-defined or object-defined error. "

    As an example (of the less random variety), I tried this simple macro:

    Sub testRange()
        Sheets("Sheet1").Select
        Range("A1").Select 'this works fine
        Sheets("Sheet2").Select
        Range("D5").Select  'this is where the error occurs
    End Sub

    I tried recording the same actions and it worked the first time I ran it, but I encountered the same error during subsequent attempts, as in:

    Sub Macro2()
    '
    ' Macro2 Macro
    '

    '
        Sheets("Sheet1").Select
        Range("A1").Select
        Sheets("Sheet2").Select
        Range("A1").Select 'error here
    End Sub

    Can anyone help?

    Tuesday, October 05, 2010 12:49 AM

All replies

  • You must activate the sheet before you can select a range on it.

    However, you almost never need to select a range.

    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "JensHansen" wrote in message news:1a4571ff-2e3b-4763-9fb9-1739f50897f4@communitybridge.codeplex.com...

    Hello,

    I haven't written anything in VBA in a number of years and was running through a tutorial to refresh my basic skills.  I was using Excel 2003 when I last was writing VBA macros.

    I seem to randomly encounter "Run-time error '1004': Application-defined or object-defined error. "

    As an example (of the less random variety), I tried this simple macro:

    Sub testRange()
        Sheets("Sheet1").Select
        Range("A1").Select 'this works fine
        Sheets("Sheet2").Select
        Range("D5").Select  '*this is where the error occurs
    *End Sub

    I tried recording the same actions and it worked the first time I ran it, but I encountered the same error during subsequent attempts, as in:

    Sub Macro2()
    '
    ' Macro2 Macro
    '

    '
        Sheets("Sheet1").Select
        Range("A1").Select
        Sheets("Sheet2").Select
        Range("A1").Select 'error here
    End Sub

    Can anyone help?


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Tuesday, October 05, 2010 1:42 AM