+ Reply to Thread
Results 1 to 8 of 8

New to arrays-how do I build new array while filtering first array?

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Question New to arrays-how do I build new array while filtering first array?

    Please see code below. I am struggling with building the new array for the listbox on the form.

    (Unless it would be faster/more efficient just to filter & delete values in the original array?? But I don't know how/if this is even possible)

    Private Sub EmployeeList_Populate()
        sWbSheet = "employee10"
    
        Dim intMaxRow As Integer
        Dim intMaxCol As Integer
        Dim varMainArray As Variant
    
        ' turn screen updating off (prevent the user from seeing the source workbook being opened)
        ' open the source workbook as ReadOnly
        Application.ScreenUpdating = False
        Set wbSource = Workbooks.Open(sServerFolderProgram & sServerFileDatabaseExcel, False, True)
    
        ' create array from worksheet range
        With wbSource
            With .Worksheets(sWbSheet)
                .Activate
                intMaxRow = .Range("A1").End(xlDown).Row 'there are no blanks in row 1 data
                intMaxCol = .Range("A1").End(xlToRight).Column 'there are no blanks in column A data
                varMainArray = .Range(Worksheets(sWbSheet).Cells(1, 1), Worksheets(sWbSheet).Cells(intMaxRow, intMaxCol)) 'used range starting from A1
            End With
            .Close False ' close the source workbook without saving changes
            Set wbSource = Nothing
        End With
    
    
    '   build new array
        Dim varNewArray As Variant
        Dim i As Integer
        For i = LBound(varMainArray) To UBound(varMainArray)
           If varMainArray(i, 2) = int_employer_id Then
    
        Help NEEDED!
        array is expected to be 5 columns wide by variable rows deep
        I need to check if column 2 (B) value is = int_employer id.
        If yes then add values from column 1 (A), column 3 & 4 (C & D) to a new array (3 columns by X rows deep)
    
    
              'varNewArray = varMainArray(i, 1)
           End If
        Next i
    
    
    '   populate listbox
        Me.ListBox1.List = varNewArray
        Application.ScreenUpdating = True
    End Sub
    Last edited by mc84excel; 05-02-2013 at 06:10 PM. Reason: Correct code xlRight to xlToRight
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: New to arrays-how do I build new array while filtering first array?

    I think this is closer but I am still missing something.

    (Extract only this time)
    '   build new array
        Dim varNewArray() As Variant
        ReDim varNewArray(1)
        varNewArray(1) = ""
        Dim i As Integer
            For i = LBound(varMainArray) To UBound(varMainArray)
           If varMainArray(i, 2) = int_employer_id Then
    
        Help NEEDED!
        array is expected to be 5 columns wide by variable rows deep
        I need to check if column 2 (B) value is = int_employer id.
        If yes then add values from column 1 (A), column 3 & 4 (C & D) to new array
    
                ReDim Preserve varNewArray(UBound(varNewArray) + 1)
                varNewArray(UBound(varNewArray)) = varMainArray(i, 1), varMainArray(i, 3), varMainArray(i, 4)
           End If
        Next i

  3. #3
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Unhappy Re: New to arrays-how do I build new array while filtering first array?

    stumped. Runtime error 9. Below is the latest version... (If you add a exit sub line just before the code building the new array, the error message doesn't appear. So it's definitely the sub array that's causing the issue)

    Private Sub EmployeeList_Populate()
        int_employer_id = 22 'for testing
        sWbSheet = "employee10"
    
        ' turn screen updating off (prevent the user from seeing the source workbook being opened)
        ' open the source workbook as ReadOnly
        Application.ScreenUpdating = False
        Set wbSource = Workbooks.Open(sServerFolderProgram & sServerFileDatabaseExcel, False, True)
    
        ' create array from worksheet range
        Dim intMaxRow As Integer
        Dim intMaxCol As Integer
        With wbSource
            With .Worksheets(sWbSheet)
                .Activate
                intMaxRow = .Range("A1").End(xlDown).Row 'there are no blanks in row 1 data
                intMaxCol = .Range("A1").End(xlToRight).Column 'there are no blanks in column A data
                varMainArray = .Range(Worksheets(sWbSheet).Cells(2, 1), Worksheets(sWbSheet).Cells(intMaxRow, intMaxCol)) 'used range starting from A2
            End With
            .Close ' close the source workbook without saving changes
            Set wbSource = Nothing
        End With
    
    '   build new array
        Dim varNewArray() As Variant
        ReDim varNewArray(1, 3)
        varNewArray(1, 3) = ""
        Dim i As Integer
        For i = LBound(varMainArray) To UBound(varMainArray)
            If varMainArray(i, 2) = int_employer_id Then
                'Help NEEDED!
                'array is expected to be 5 columns wide by variable rows deep
                'I need to check if column 2 (B) value is = int_employer id.
                'If yes then add values from column 1 (A), column 3 & 4 (C & D) to new array
                ReDim Preserve varNewArray(UBound(varNewArray) + 1)
                varNewArray(UBound(varNewArray), 1) = varMainArray(i, 1).Value
                varNewArray(UBound(varNewArray), 2) = varMainArray(i, 3).Value
                varNewArray(UBound(varNewArray), 3) = varMainArray(i, 4).Value
            End If
        Next i
    
    '   populate listbox
        Me.ListBox1.List = varNewArray
    
        Application.ScreenUpdating = True
    End Sub

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: New to arrays-how do I build new array while filtering first array?

    if you have a 2D array you can only resize the last dimension if you use redim preserve
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  5. #5
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: New to arrays-how do I build new array while filtering first array?

    Quote Originally Posted by JosephP View Post
    if you have a 2D array you can only resize the last dimension if you use redim preserve
    I am new to arrays. However I am already using redim preserve in the code in post #3.

    So where am I going wrong?

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: New to arrays-how do I build new array while filtering first array?

    I had a friend have a go at the code and here is his version. However I still get a runtime error 9 subscript out of range message.

    I have stepped through the code and it all runs smoothly, checking through the main array etc. UNTIL it reaches a match. As soon as VBA goes from this line:
    ReDim Preserve varNewArray(2, UBound(varNewArray, 2) + 1)
    to this line
    varNewArray(0, UBound(varNewArray)) = varMainArray(i, 1)
    it gets the error message. Why?

    Option Explicit
    
    Private wbSource As Workbook
    Private sWbSheet As String
    Private varMainArray As Variant
    
    Private Sub UserForm_Initialize()
        Call EmployeeList_Populate
    End Sub
    
    Private Sub CancelButton_Click()
        Unload Me
    End Sub
    
    Private Sub OKButton_Click()
        If ListBox1.ListIndex = -1 Then
            MsgBox "No employee selected!"
            Unload Me
            Exit Sub
        End If
    
        MsgBox ListBox1.Value 'for test purposes
    
        Unload Me
    End Sub
    '
    '-----form macros above, private macros below--------------------------------
    '
    Private Sub testme()
        Me.Show
    End Sub
    
    Private Sub EmployeeList_Populate()
        int_employer_id = 3 'for testing
        sWbSheet = "employee10"
    
        ' turn screen updating off (prevent the user from seeing the source workbook being opened)
        ' open the source workbook as ReadOnly
        Application.ScreenUpdating = False
        Set wbSource = Workbooks.Open(sServerFolderProgram & sServerFileDatabaseExcel, False, True)
    
        ' create array from worksheet range
        Dim intMaxRow As Integer
        Dim intMaxCol As Integer
        With wbSource
            With .Worksheets(sWbSheet)
                .Activate
                intMaxRow = .Range("A1").End(xlDown).Row 'there are no blanks in row 1 data
                intMaxCol = .Range("A1").End(xlToRight).Column 'there are no blanks in column A data
                varMainArray = .Range(Worksheets(sWbSheet).Cells(2, 1), Worksheets(sWbSheet).Cells(intMaxRow, intMaxCol)) 'used range starting from A2 (to avoid header)
            End With
            .Close ' close the source workbook without saving changes
            Set wbSource = Nothing
        End With
    
    '   build new array
        Dim varNewArray() As Variant
        ReDim varNewArray(2, 0)
    
        Dim i As Integer
        For i = LBound(varMainArray) To UBound(varMainArray)
            If varMainArray(i, 2) = int_employer_id Then
                'Help NEEDED!
                'array is expected to be 5 columns wide by variable rows deep
                'I need to check if column 2 (B) value is = int_employer id.
                'If yes then add values from column 1 (A), column 3 & 4 (C & D) to new array
                ReDim Preserve varNewArray(2, UBound(varNewArray, 2) + 1)
                varNewArray(0, UBound(varNewArray)) = varMainArray(i, 1)
                varNewArray(1, UBound(varNewArray)) = varMainArray(i, 3)
                varNewArray(2, UBound(varNewArray)) = varMainArray(i, 4)
            End If
        Next i
    
    '   populate listbox
        Me.ListBox1.List = varNewArray
    
        Application.ScreenUpdating = True
    End Sub

  7. #7
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: New to arrays-how do I build new array while filtering first array?

    Solved.

    Thank you all for the help
    Last edited by mc84excel; 05-02-2013 at 07:31 PM.

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: New to arrays-how do I build new array while filtering first array?

    for others' benefit it would be good if you posted the answer :-)

    I didn't review the code but suspect you needed varNewArray(0, UBound(varNewArray, 2)) = varMainArray(i, 1)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1