Populating a multi-dimensional array

Hi guys,

Firstly let me point out that my programming skill is pretty basic so I may have made some rookie mistakes in my code below.

I have a spreadsheet which I will be adding to over time. I need a macro that will look for the last 10 rows and then pull together a summary table based on data from several of the columns on each of those rows.

I've tried to go about this by using an array. The array will always be the same size (it will always be 10 by 11, i.e. EngineArray(10,11)-basically the same size as my summary table). I've also set Option Base 1.

What I’ve got so far is below. It isn’t working properly though. To start with I was getting ‘type mismatch errors’. Now instead of reading the numbers from the selected cells into the array, it just changes all the cells I’m trying to read from to ‘TRUE’????!

Can anyone point out what I’ve done wrong? Like I said, I’m pretty new to all this so I’m sure it’s probably something trivial. I’ve not included the pasting part of the macro because I think it’s the part below which is causing the problems.

Please Login or Register  to view this content.

Thanks in advance,

Chris

Please remember to use code tags in future when posting code examples.


It would help if you posted a workbook with example data.
And explained what you intend to do with the contents of EngineArray


for information this is what is causing your TRUE overwrite problem

Please Login or Register  to view this content.

First the Select method is performed and the the result of the implied test "Is the Active cell selected" is performed. By definition the activecell is selected so TRUE is written in to the cell.
You probably meant to just set the active cell, in which case this would do it

Please Login or Register  to view this content.

But selecting cells is not required.

Maybe, using loops, something like this.
It will populate the array as the data is shown on the sheet.

Please Login or Register  to view this content.

That section of code creates an array that contains the column numbers of the ranges that you want to lift data from.

I could have used explicit values but I think this way is easier to relate to the worksheet, assuming you are using A1 notation. Basically those are the columns that contain the data you want.

Try this,

Start at bottom and works up. Only checks column F for empty cells

Please Login or Register  to view this content.