HELP! Reading range into array

[katem]

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("a622").Value

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

 

[Chip Pearson]

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]

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