HELP! Reading range into array
 

Hi,

I've been working on some code, which seems to have stopped working
for reasons which are beyond me. I'm trying to read a range from the
worksheet into an array using the code:-

interpArray = Worksheets(sheetName(j)).Range(Cells(row1, col1),
Cells(row2, col2)).Value

This is pulling up a Run-time Error 1004 "Application-defined or
object-defined error". If I hard code in the array i.e.

interpArray = Worksheets(sheetName(j)).Range(Cells("a6:o22").Val ue

then it works fine, but I need to be able to move down the sheet as
the macro loops through, as I need a different range to be read with
each loop of the array.

Can anyone help me work out how to fix this?

Thanks in advance!
Kate

HELP! Reading range into array
 

Kate,

It is quite possible that the problem lies in your use of the Cells
property. In your code

Worksheets(sheetName(j)).Range(Cells(row1, col1),Cells(row2, col2)).Value)

the Cells do not necessarily point to cells on sheetName(j). Since they are
unqualifed by a worksheet reference, they will refer to the active sheet,
which is likely not the same sheet as sheetName(j). Try using code like

With ThisWorkbook.Worksheets(sheetName(j))
interpArray = .Range(.Cells(row1, col1), .Cells(row2, col2)).Value
End With


Note the period character before the Range and the two Cells properties.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)




"katem" wrote in message
oups.com...
Hi,

I've been working on some code, which seems to have stopped working
for reasons which are beyond me. I'm trying to read a range from the
worksheet into an array using the code:-

interpArray = Worksheets(sheetName(j)).Range(Cells(row1, col1),
Cells(row2, col2)).Value

This is pulling up a Run-time Error 1004 "Application-defined or
object-defined error". If I hard code in the array i.e.

interpArray = Worksheets(sheetName(j)).Range(Cells("a6:o22").Val ue

then it works fine, but I need to be able to move down the sheet as
the macro loops through, as I need a different range to be read with
each loop of the array.

Can anyone help me work out how to fix this?

Thanks in advance!
Kate

HELP! Reading range into array
 

Interesting!! Thanks SO much for your help, it's much appreciated -
your code worked a treat (and saved my hair from being pulled out!)

Cheers,
Kate

On Jun 7, 9:33 am, "Chip Pearson" wrote:
Kate,

It is quite possible that the problem lies in your use of the Cells
property. In your code

Worksheets(sheetName(j)).Range(Cells(row1, col1),Cells(row2, col2)).Value)

the Cells do not necessarily point to cells on sheetName(j). Since they are
unqualifed by a worksheet reference, they will refer to the active sheet,
which is likely not the same sheet as sheetName(j). Try using code like

With ThisWorkbook.Worksheets(sheetName(j))
interpArray = .Range(.Cells(row1, col1), .Cells(row2, col2)).Value
End With

Note the period character before the Range and the two Cells properties.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLCwww.cpearson.com
(email on the web site)

"katem" wrote in message

oups.com...

Hi,

I've been working on some code, which seems to have stopped working
for reasons which are beyond me. I'm trying to read a range from the
worksheet into an array using the code:-

interpArray = Worksheets(sheetName(j)).Range(Cells(row1, col1),
Cells(row2, col2)).Value

This is pulling up a Run-time Error 1004 "Application-defined or
object-defined error". If I hard code in the array i.e.

interpArray = Worksheets(sheetName(j)).Range(Cells("a6:o22").Val ue

then it works fine, but I need to be able to move down the sheet as
the macro loops through, as I need a different range to be read with
each loop of the array.

Can anyone help me work out how to fix this?

Thanks in advance!
Kate