adding a row to an array

[Oberon70]

Hi,

I am still trying to get my head around arrays, but it is slow going.

I am wanting to add a row to an array when the reference number is 8 characters long.

Sub Macro1 ()

Dim wb As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet


Dim i As Double
Dim LastRow As Double
Dim lo As ListObject
Dim loClm As ListColumn
Dim rng As Range
Dim TBLArray()
Dim RowNum As Double


Set wb = ThisWorkbook
Set ws2 = wb.Sheets("Receipting")
Set lo = ws2.ListObjects("Table3")

LastRow = ws2.Cells.SpecialCells(xlCellTypeLastCell).Row

With ws2.Range("f1")
        .EntireColumn.Insert
        .EntireColumn.NumberFormat = "0.00"
End With

ws2.Range("F1").Value = "Inc Comm"
Set loClm = lo.ListColumns("Inc Comm")
Set rng = loClm.DataBodyRange

rng.FormulaR1C1 = "=RC4+RC5"


For i = 2 To LastRow
    RowNum = ws2.Cells(i, 1).Row
    If Len(ws2.Cells(i, 1)) = 8 Then TBLArray() = ws2.Cells(i, 1).Row
Next i

End Sub

 

[BSALV]

i don't know what you try to do, but normally it's better to oversize your array then modify the dimensions later.
If you do so, you have to use "redim" and in case there is already data in the array "preserve".
Also what kind of array do you want, a 1D or a 2D, is there more then 1 variable you want to store in it ???

this is just a guess, because i don't know what you intend to do

     ReDim tblArray(LastRow)                                    ' a 1D-array starting with lastrow+1 elements because starting  with element 0 (oversized!)

     For i = 2 To LastRow
          RowNum = ws2.Cells(i, 1).Row
          If Len(ws2.Cells(i, 1)) = 8 Then tblArray(ptr) = ws2.Cells(i, 1).Row: ptr = ptr + 1
     Next i

     If ptr <> 0 Then Range("A1").Resize(ptr).Value = Application.Transpose(tblArray)     'copy part of tblarray to the sheet
End Sub