Assign array

[welchs101]

Hi, I need help assigning an array.

In the past i have used something like the following to define a multi-dimensional array

Please Login or Register  to view this content.

But in this case i cant use this defn/assignment. See enclosed file. I am trying to assign to an array the shaded cells. I have a for loop and the first time through i want to assign the area in red to a multidimensional array. I will do "something" with this array and then the the next time through the loop i will assign the area in purple.

Problem i am having is assigning the shaded cells to muli-dimensional array using the type of defn above. Any suggestions?

[StephenR]

Maybe this?

Please Login or Register  to view this content.

[welchs101]

Thanks for the response.

Here is what i came up with but for some reason i am getting a type mismatch error

Please Login or Register  to view this content.

any ideas why?

[welchs101]

ok...i think i fixed it ............

Please Login or Register  to view this content.

still working on it but this seems to work ..........if not i will let you guys know.

actually the above did not work..........................below works ( i think)

active_array = ws.Range(Cell.Address(0, 0), ws.Cells(Cell.Row, Cell.Column).End(xlDown).Offset(, 1))

note: cell is a defined range

[Mordred]

Did you try StephenR's solution?

[welchs101]

I have not tried that soln yet but i will later today. Had to work on something else that came up. But i will try it!

[welchs101]

the code from StephenR works great.......exactly what i wanted it to do. wish i had seen this before i spent so much time trying to figure it out on my own.

thanks for the help!

How does it know what cells to put into the array? I mean it puts exactly the cells into the array that i was looking for.......i think it has to do with this

SpecialCells(xlCellTypeConstants).Areas


But i am not sure...............can someone explain?

[Leith Ross]

Hello Welchs101,

A Range object always has at least one Area object. The Areas collection holds each Area of Range where an Area is a group of one or more contiguous cells. Each Area is itself a Range object.

For Example:

Range("A1") would have a single Area that contains the cell A1.

Range("A1:A10") would be single Area that contains cells A1 through A10.

Range("A1:A10, J2:J6") would have 2 Areas. One for cells A1:A10 and the second for cells J2:J6.

[welchs101]

thanks Leith Ross.......but how did the code know correct area? I still dont understand how only the area i wanted got selected/determined?

[Leith Ross]

Hello Welchs101,

I will have to look at the complete macro to give you more details.

[Leith Ross]

Hello Welchs101,

Here is the address string returned from by the method SpecialCells(xlCellTypeConstants):

Please Login or Register  to view this content.

Only the header rows in column "C" (C1 and C18) are shown as single cells because the other cells contain formula results and not constants. Columns "A" and "B" cells are all constant values.

Column "C" values are not included because their row count is only 1, due to it being a header. Only columns "A" and "B" cells of each row are saved to the active_array .

[welchs101]

i see...........i get it now. thanks!!!!!!!!!!!!