Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Dynamic Array

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Winnipeg, Manitoba, CANADA
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Goodday,
    I am trying to load rows into a listbox that meet certain criteria (so not every row will be loaded). What I have right now works but only prints the last row, so is there a way to Redim the Upperbound without erasing the contents of the array?

    Private Sub UserForm_Activate()
    Dim intRow As Integer
    Dim intCol As Integer
    Dim End_Flag As Boolean
    Dim intCounter As Integer
    Dim intRowCount As Integer
    Dim i As Integer

    ' Row and Column for the Export Data
    intRow = 3
    intCol = 219

    Sheets("DATA").Select
    Cells(intRow, intCol).Select

    lstExport.ColumnCount = 6

    End_Flag = False
    While (End_Flag = False)
    If ActiveCell.Value = True Then
    'intCounter = intCounter + 1
    ' Load it onto lstExport
    intRow = ActiveCell.Row
    Redim MyArray(intCounter,6)
    MyArray(intCounter, 0) = ActiveSheet.Range("HS" & intRow).Value
    MyArray(intCounter, 1) = ActiveSheet.Range("HT" & intRow).Value
    MyArray(intCounter, 2) = ActiveSheet.Range("HU" & intRow).Value
    MyArray(intCounter, 3) = ActiveSheet.Range("HV" & intRow).Value
    MyArray(intCounter, 4) = ActiveSheet.Range("HW" & intRow).Value
    MyArray(intCounter, 5) = ActiveSheet.Range("HX" & intRow).Value
    intCounter = intCounter + 1
    ' Move down 1 row
    ActiveCell.Offset(1, 0).Select
    Else
    ' We have reached the end of the file
    If Len(ActiveCell.Value) = 0 Then
    End_Flag = True
    Else
    ' Move down 1 row
    ActiveCell.Offset(1, 0).Select
    End If
    End If
    Wend

    ' Show the Listbox with the values
    lstExport.List = MyArray

    End Sub

    _________________
    Thanx.

    [ This Message was edited by: Robb on 2002-05-09 11:39 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Winnipeg, Manitoba, CANADA
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I found a way around my problem, see code below (if anyone else can do it better, please let me know, thanx).

    Option Explicit
    Dim MyArray(300, 6) As Variant 'Array containing column values for ListBox.

    Private Sub UserForm_Activate()
    ' Load all Styles that have their Export to Booking Report checked off
    ' Sort it by Style number in ascending order
    Dim intRow As Integer
    Dim intCol As Integer
    Dim End_Flag As Boolean
    Dim intCounter As Integer
    Dim intRowCount As Integer
    Dim i As Integer
    Dim UpHer As Integer
    Dim MySecArray As Variant

    ' Row and Column for the Export Data
    intRow = 3
    intCol = 219

    UnHide_WS "DATA"
    Application.ScreenUpdating = False
    Sheets("DATA").Select
    Cells(intRow, intCol).Select

    ' If the first one is blank then exit
    If Len(ActiveCell.Value) = 0 Then
    MsgBox "No Data Found."
    Hide_WS "DATA"
    Unload Me
    Exit Sub
    End If

    lstExport.ColumnCount = 6

    End_Flag = False
    While (End_Flag = False)
    If ActiveCell.Value = True Then
    ' Load it into an array
    intRow = ActiveCell.Row
    MyArray(intCounter, 0) = ActiveSheet.Range("HS" & intRow).Value
    MyArray(intCounter, 1) = ActiveSheet.Range("HT" & intRow).Value
    MyArray(intCounter, 2) = ActiveSheet.Range("HU" & intRow).Value
    MyArray(intCounter, 3) = ActiveSheet.Range("HV" & intRow).Value
    MyArray(intCounter, 4) = ActiveSheet.Range("HW" & intRow).Value
    MyArray(intCounter, 5) = ActiveSheet.Range("HX" & intRow).Value
    intCounter = intCounter + 1

    ' Move down 1 row
    ActiveCell.Offset(1, 0).Select
    Else
    ' We have reached the end of the file
    If Len(ActiveCell.Value) = 0 Then
    End_Flag = True
    Else
    ' Move down 1 row
    ActiveCell.Offset(1, 0).Select
    End If
    End If
    Wend

    intCounter = intCounter - 1

    ReDim MySecArray(intCounter, 6)

    For i = 0 To intCounter
    MySecArray(i, 0) = MyArray(i, 0)
    MySecArray(i, 1) = MyArray(i, 1)
    MySecArray(i, 2) = MyArray(i, 2)
    MySecArray(i, 3) = MyArray(i, 3)
    MySecArray(i, 4) = MyArray(i, 4)
    MySecArray(i, 5) = MyArray(i, 5)
    Next i

    'Populate the listbox with values
    lstExport.List = MySecArray

    End Sub
    Thanx.

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about

    Redim Preserve yourArray


    ?

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Winnipeg, Manitoba, CANADA
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mark,

    "Preserve" didn't work (I did try it prior to posting). Preserve does not allow change to the UBound, it only allows change to LBound. Kinda silly eh? Unless....I'm missing something.
    Thanx.

  5. #5
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yeah, you're missing something you initially declared your array like this:


    Dim MyArray(300, 6)


    This isn't a dynamic array.
    Try something like this:


    Dim MyArray()

    ReDim MyArray(300, 6)


    Then trying "Preserve" - ing later on.

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It looks like Robb wants to resize the first element in his array and Preserve will allow him to only resize the last element. Robb, could you switch things around. Probably be a lot of work...

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Winnipeg, Manitoba, CANADA
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried switching around (and it did work, again I did this before my first posting) but it is unmanageable and ugly. What was Bill thinking?

    Actually it wasn't that much work to switch it around.
    Thanx.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •