I'd appreciate some insight about setting ranges with Excel 2003 VBA.
In particular, why does THIS work

Dim fromRange as Range
....
Worksheets("AAdata").Activate
Set fromRange = Range(Cells(2, 4), Cells(2, 11))



And THIS give a Run-time "Application-defined or object-defined error",
#1004, error:

Dim fromRange as Range
....
Set fromRange = Worksheets("AAdata").Range(Cells(2, 4), Cells(2, 11))


TIA
R. Spleen

Because Cells, unless otherwise told, references cells on the
ActiveSheet. So you are tell it in effect:
Set fromRange = Worksheets("AAdata").Range(ActiveSheet.Cells(2, 4), _
ActiveSheet.Cells(2, 11))
What you really want to tell it is this:
Set fromRange =
Worksheets("AAdata").Range(Worksheets("AAdata").Ce lls(2, 4), _
Worksheets("AAdata").Cells(2, 11))
Which could be shortened to this:
With Sheets("AAdata")
Set fromRange = .Range(.Cells(2,4),.Cells(2,11))
End With

Clear as mud?

Charles Chickering
Randy Spleen wrote:
I'd appreciate some insight about setting ranges with Excel 2003 VBA.
In particular, why does THIS work

Dim fromRange as Range
...
Worksheets("AAdata").Activate
Set fromRange = Range(Cells(2, 4), Cells(2, 11))



And THIS give a Run-time "Application-defined or object-defined error",
#1004, error:

Dim fromRange as Range
...
Set fromRange = Worksheets("AAdata").Range(Cells(2, 4), Cells(2, 11))


TIA
R. Spleen

because cells refers to the activesheet and range refers to AAdata.

Just change it to be expressed properly
with Worksheets("AAdata")
Set fromRange = .Range(.Cells(2, 4), .Cells(2, 11))
End with

then everything is pointing to the same sheet.

that said, since you are using fixed references

set fromRange = Wroksheets("AAdata").Range("D4:K4")

would work as well.

--
Regards,
Tom Ogilvy


"Randy Spleen" wrote:


I'd appreciate some insight about setting ranges with Excel 2003 VBA.
In particular, why does THIS work

Dim fromRange as Range
....
Worksheets("AAdata").Activate
Set fromRange = Range(Cells(2, 4), Cells(2, 11))



And THIS give a Run-time "Application-defined or object-defined error",
#1004, error:

Dim fromRange as Range
....
Set fromRange = Worksheets("AAdata").Range(Cells(2, 4), Cells(2, 11))


TIA
R. Spleen

If your code is in a general module, it should work.

But I'm guessing that your code is behind a worksheet. And it's not behind
AAdata.

Unqualifed ranges in a general module will refer to the activesheet.
Unqualified ranges behind a worksheet will belong to the worksheet that owns the
code.

I'd use:

with worksheets("aadata")
set fromrange=.range(.cells(2,4),.cells(2,11))
'or just
'set fromrange = .range("D2:K2")
end with



Randy Spleen wrote:

I'd appreciate some insight about setting ranges with Excel 2003 VBA.
In particular, why does THIS work

Dim fromRange as Range
...
Worksheets("AAdata").Activate
Set fromRange = Range(Cells(2, 4), Cells(2, 11))

And THIS give a Run-time "Application-defined or object-defined error",
#1004, error:

Dim fromRange as Range
...
Set fromRange = Worksheets("AAdata").Range(Cells(2, 4), Cells(2, 11))

TIA
R. Spleen

--

Dave Peterson

Die_Another_Day wrote:
Because Cells, unless otherwise told, references cells on the
ActiveSheet. So you are tell it in effect:
Set fromRange = Worksheets("AAdata").Range(ActiveSheet.Cells(2, 4), _
ActiveSheet.Cells(2, 11))
What you really want to tell it is this:
Set fromRange =
Worksheets("AAdata").Range(Worksheets("AAdata").Ce lls(2, 4), _
Worksheets("AAdata").Cells(2, 11))
Which could be shortened to this:
With Sheets("AAdata")
Set fromRange = .Range(.Cells(2,4),.Cells(2,11))
End With

Or shortened further to

Set rng = Sheets("AAdata").Range("A1")
Set fromRange = Range(rng(2, 4), rng(2, 11))

Alan Beban