1004 App - defined or object-defined error

[Stan Plumber]

When I run a procedure with this line of code I get the 1004 app
define... error. Yet when I place the cursor over the variables it
references the right worksheet, column, and row. and even the value.
What's wrong with this line of code if:

Debug.Print Worksheets(glr_CopyWKS).Range(Cells(lngHeader,
lngX)).Value

glr_copywks = the activeworksheet
lngheader = a row in the activeworksheet
and lngx = a column in the activeworksheet

TIA

Stan

 

[J.E. McGimpsey]

Two problems:

The first bites every coder at least once, and usually multiple
times...

Cells(), when unqualified, defaults to the ActiveSheet, so your
statment is equivalent to

Debug.Print Worksheets(glr_CopyWKS).Range(ActiveSheet.Cells( _
lngHeader, lngX)).Value

So if glr_CopyWKS is not the activesheet, the Range fails since
ranges can only be child objects of one sheet.

The second the Range(Cells(...)) construct. Look at VBA Help (Range
Property) - the syntax for Range() requires either a single A1 cell
reference (syntax 1) or two A1-style cell references or range
objects (syntax 2). In this case, using Range() is redundant. Try:

Debug.Print Worksheets(glr_CopyWKS).Cells(lngHeader, lngX).Value

 

[Stan Plumber]

Thanks. Worked great.

Two problems:

The first bites every coder at least once, and usually multiple
times...

Cells(), when unqualified, defaults to the ActiveSheet, so your
statment is equivalent to

Debug.Print Worksheets(glr_CopyWKS).Range(ActiveSheet.Cells( _
lngHeader, lngX)).Value

So if glr_CopyWKS is not the activesheet, the Range fails since
ranges can only be child objects of one sheet.

The second the Range(Cells(...)) construct. Look at VBA Help (Range
Property) - the syntax for Range() requires either a single A1 cell
reference (syntax 1) or two A1-style cell references or range
objects (syntax 2). In this case, using Range() is redundant. Try:

Debug.Print Worksheets(glr_CopyWKS).Cells(lngHeader, lngX).Value