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.

Sub LastTenEngines()

Dim EngineArray(10, 11) As Long
Dim i As Integer

For i = 0 To 9

Range("F9").End(xlDown).Select              'Find the last row

ActiveCell = ActiveCell.Offset(-i, 0).Select        'Get onto the row with data

ActiveCell = ActiveCell.Offset(0, -3).Select            
    EngineArray(i + 1, 1) = ActiveCell.Value                 
ActiveCell = ActiveCell.Offset(0, 55).Select
    EngineArray(i + 1, 2) = ActiveCell.Value              
ActiveCell = ActiveCell.Offset(0, 2).Select
    EngineArray(i + 1, 3) = ActiveCell.Value             
ActiveCell = ActiveCell.Offset(0, 2).Select
    EngineArray(i + 1, 4) = ActiveCell.Value                
ActiveCell = ActiveCell.Offset(0, 2).Select
    EngineArray(i + 1, 5) = ActiveCell.Value                
ActiveCell = ActiveCell.Offset(0, 2).Select
    EngineArray(i + 1, 6) = ActiveCell.Value                
ActiveCell = ActiveCell.Offset(0, 2).Select
    EngineArray(i + 1, 7) = ActiveCell.Value                
ActiveCell = ActiveCell.Offset(0, 2).Select
    EngineArray(i + 1, 8) = ActiveCell.Value              
ActiveCell = ActiveCell.Offset(0, 2).Select
    EngineArray(i + 1, 9) = ActiveCell.Value              
ActiveCell = ActiveCell.Offset(0, 2).Select
    EngineArray(i + 1, 10) = ActiveCell.Value               
ActiveCell = ActiveCell.Offset(0, 2).Select
    EngineArray(i + 1, 11) = ActiveCell.Value              

Next i

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

ActiveCell = ActiveCell.Offset(-i, 0).Select

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

ActiveCell.Offset(-i, 0).Select

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.

Sub LastTenEngines()

    Dim EngineArray(10, 11) As Long
    Dim i As Integer
    Dim lngCols(11) As Long
    Dim vntCol As Variant
    Dim lngRow As Long
    Dim lngTopRow As Long
    Dim lngCol As Long

    For Each vntCol In Split("C,BF,BH,BJ,BL,BN,BP,BR,BT,BV,BX", ",")
        lngCol = lngCol + 1
        lngCols(lngCol) = Range(vntCol & "1").Column
    Next

    lngTopRow = Range("F9").End(xlDown).Row - 9
    
    i = 0
    For lngRow = lngTopRow To lngTopRow + 9
        i = i + 1
        For lngCol = 1 To 11
            EngineArray(i, lngCol) = Cells(lngRow, lngCols(lngCol))
        Next
    Next

End Sub

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

Sub LastTenEngines()

    Dim EngineArray(10, 11) As Long
    Dim i As Integer
    Dim lngCols(11) As Long
    Dim vntCol As Variant
    Dim lngRow As Long
    Dim lngTopRow As Long
    Dim lngCol As Long

    For Each vntCol In Split("C,BF,BH,BJ,BL,BN,BP,BR,BT,BV,BX", ",")
        lngCol = lngCol + 1
        lngCols(lngCol) = Range(vntCol & "1").Column
    Next

    lngRow = Cells(Rows.Count, 6).End(xlUp).Row ' get last row
    
    ' work way back up the rows populating array from last element
    i = 10
    Do While lngRow >= 9
        If Len(Cells(lngRow, 6)) > 0 Then
            For lngCol = 1 To 11
                EngineArray(i, lngCol) = Cells(lngRow, lngCols(lngCol))
            Next
            i = i - 1
        End If
        lngRow = lngRow - 1
        If i = 0 Then Exit Do
    Loop

End Sub