Help with this code

[matelot]

I must be blind. Please help me find what's the problem with this code.
I get the following error when I run it.
"Run-Time error 1004: Application-defined or object-defined error"
My code is really simple and yet I don't know what's wrong with it.

Dim myArray(1,1) as string
c= 1
set s = workbooks("test.xls").sheets(2)
Set theRange = s.Range(Cells(1, c), Cells(UBound(myArray), c)) <-run time
error on this line

Please help.

[Dave Peterson]

Cells() refers to the activesheet. If S isn't the activesheet, then kablewie!!!

Set theRange = s.Range(Cells(1, c), Cells(UBound(myArray), c))
could be
Set theRange = s.Range(s.Cells(1, c), s.Cells(UBound(myArray), c))

or

with s
Set theRange = .Range(.Cells(1, c), .Cells(UBound(myArray), c))
end with

The dots mean that that thing belongs to the previous With object.

matelot wrote:
>
> I must be blind. Please help me find what's the problem with this code.
> I get the following error when I run it.
> "Run-Time error 1004: Application-defined or object-defined error"
> My code is really simple and yet I don't know what's wrong with it.
>
> Dim myArray(1,1) as string
> c= 1
> set s = workbooks("test.xls").sheets(2)
> Set theRange = s.Range(Cells(1, c), Cells(UBound(myArray), c)) <-run time
> error on this line
>
> Please help.

--

Dave Peterson

[Dave Peterson]

I'm betting that you did put something in that array.

And maybe...

with s
Set theRange = .Range(.Cells(1, c), .Cells(UBound(myArray,1), c))
end with

Did you really mean to use myArray(1,1)?



matelot wrote:
>
> I must be blind. Please help me find what's the problem with this code.
> I get the following error when I run it.
> "Run-Time error 1004: Application-defined or object-defined error"
> My code is really simple and yet I don't know what's wrong with it.
>
> Dim myArray(1,1) as string
> c= 1
> set s = workbooks("test.xls").sheets(2)
> Set theRange = s.Range(Cells(1, c), Cells(UBound(myArray), c)) <-run time
> error on this line
>
> Please help.

--

Dave Peterson

[Mark Lincoln]

Have you dimmed theRange? If not, is Option Explicit declared in this
code module?

[avveerkar]

I must be blind. Please help me find what's the problem with this code.
I get the following error when I run it.
"Run-Time error 1004: Application-defined or object-defined error"
My code is really simple and yet I don't know what's wrong with it.

Dim myArray(1,1) as string
c= 1
set s = workbooks("test.xls").sheets(2)
Set theRange = s.Range(Cells(1, c), Cells(UBound(myArray), c)) <-run time
error on this line

Please help.

VBA will not permit you to set range on a sheet which is not active. Perhaps you are on sheet1 ( sheet1 is active ) and then trying to set range in sheet2. Be on sheet2 ( sheet2 is active) and run the macro, you will not get the error. You could have "s.activate" before your "set theRange" statement.

A V Veerkar

[Dave Peterson]

You can set a range on an inactive sheet--but you can't select that range.

avveerkar wrote:
>
> matelot Wrote:
> > I must be blind. Please help me find what's the problem with this code.
> > I get the following error when I run it.
> > "Run-Time error 1004: Application-defined or object-defined error"
> > My code is really simple and yet I don't know what's wrong with it.
> >
> > Dim myArray(1,1) as string
> > c= 1
> > set s = workbooks("test.xls").sheets(2)
> > Set theRange = s.Range(Cells(1, c), Cells(UBound(myArray), c)) <-run
> > time
> > error on this line
> >
> > Please help.
> VBA will not permit you to set range on a sheet which is not active.
> Perhaps you are on sheet1 ( sheet1 is active ) and then trying to set
> range in sheet2. Be on sheet2 ( sheet2 is active) and run the macro,
> you will not get the error. You could have "s.activate" before your
> "set theRange" statement.
>
> A V Veerkar
>
> --
> avveerkar
> ------------------------------------------------------------------------
> avveerkar's Profile: http://www.excelforum.com/member.php...o&userid=30338
> View this thread: http://www.excelforum.com/showthread...hreadid=503167

--

Dave Peterson