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?

Answer 1

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:[e-mail]...

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