2D array from non contiguous columns

[nigelog]

In an effort to try to not use named ranges on helper worksheets I would like to see if I can write to a 2D array and use the data in that to populate userforms. I have little knowledge of arrays and might be going the wrong way about it. This code will load two columns but not 3 or 4. I would also like to refer the columns by number rather than letter. Any pointers appreciated.

Sub StoreRange()
    Dim MyArray() As String, asheet As Worksheet
    Dim iloop As Integer
    Dim x As Variant
    ReDim MyArray(0)
    Dim LastRow As Long
    Set asheet = ActiveSheet
    With asheet
     LastRow = .Range("a" & Rows.Count).End(xlUp).Row
    For Each x In .Range("a1:A" & LastRow, "e1:e" & LastRow).Cells

    'For Each x In .Range("a1:A" & LastRow, "e1:e" & LastRow, "j1:j" & LastRow, "n1:n" & LastRow).Cells

        MyArray(UBound(MyArray)) = x
        ReDim Preserve MyArray(UBound(MyArray) + 1)
    Next
    
    ReDim Preserve MyArray(UBound(MyArray))

    For iloop = LBound(MyArray) To UBound(MyArray)
        Debug.Print MyArray(iloop)
    Next
    End With
End Sub

sample attached

[Norie]

Nigel

Arrays and non-contiguous ranges don't really work well together.

For example, it's easy enough to populate an array with data from non-contiguous arrays, as you appear to be doing, but what happens when you want to write the data back? How do you determine which range/column the data is to go to?

What is it you are actually trying to do?

PS Are you thinking of having a 2-dimensional array where the first dimension is the value and the second the column?

[jindon]

See if this is how you wanted.

Sub StoreRange()
    Dim MyArray As Variant, asheet As Worksheet
    Dim LastRow As Long
    Set asheet = ActiveSheet
    With asheet.Cells(1).CurrentRegion.Resize(, 14)
        MyArray = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count & ")"), Array(1, 5, 10, 14))
    End With
End Sub

[nigelog]

@Don
in the sample userform the dynamic form gets its data from a named range tblEmployeeData3 from a helper worksheet "data", which in turn is loaded from the main data set. The form looks for dates in a heading column and loads the name and date if qualifies.

There are many forms in the workbook I use and each one needs a helper worksheet which has to be refreshed
I would like to load an array from each userform as activated and use the data from the array to load the form

@ jindon, Ill have a look

[jindon]

@ jindon, Ill have a look

What are you trying to do?

[nigelog]

@ jindon... see above post

[Norie]

Why are you dynamically creating controls?

Why not have something like this?

A combobox with the headings from Data sheet.

A 2 column listbox for Name and Date.

When a value is selected from the combobox the listbox is populated with the data from the associated column(s) on Data.

[nigelog]

@ Norie

these are alert sheets that are loaded when the workbook opens and have to resize, people here will not go looking for the information therefore the userform alerts them to imminent expiry dates for many different areas. To change the format now would create chaos. All I am trying to do is obviate the need for a helper sheet for each one as they are cluttering the workbook...

[nigelog]

@ jindon
your code loads an array but how can I view or use the data in the array

[jindon]

Output on in a range to see the elements.
e.g

[a1].resize(ubound(myarray,1),ubound(myarray,2))=myarray

Still no idea about what you are trying to do, so I'll leave this to someone else.

[nigelog]

thanks jindon, array format is how I imagined now want to see if I can use to load a form

I'm trying to use an array rather than a named range on a worksheet to use as the data for a userform

The array elements are in exactly the same order as the range that the userform code uses to populate itself and I am trying to use the array rather than a physical range on a worksheet


The original question has been answered so I will mark as solved

[nigelog]

I used the array output on a temporarily created named range that was used to populate the form. This way I have no helper sheets and can create the named range from the array required for a particular form, all read from the main data set. 10 less worksheets. Thanks for the assistance Norie and jindon

[jindon]

Glad if that helped and thanks for the rep.

[Greg M]

Hi Nigel,

Thanks for your message regarding the above, and sorry for the delay in replying - I haven't been online here recently.

See if the following code does what you need:

Sub CreateArrayForUserForm()

    Dim vColumnNo_Worksheet As Variant
    Dim iColumnNo_Worksheet As Integer
    Dim iColumnNo_Array     As Integer
    Dim vaUserFormData      As Variant
    Dim iLastRowNo          As Integer
    Dim iRowNo              As Integer
    Dim wks                 As Worksheet

    iColumnNo_Array = 0

    Set wks = ActiveSheet

    With wks

        iLastRowNo = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'       Specify the column numbers from which data should be copied
        For Each vColumnNo_Worksheet In Array(1, 5, 10, 14)

            iColumnNo_Worksheet = CInt(vColumnNo_Worksheet)

            iColumnNo_Array = iColumnNo_Array + 1

            If iColumnNo_Array = 1 Then
                  ReDim vaUserFormData(1 To iLastRowNo, 1 To iColumnNo_Array)
            Else: ReDim Preserve vaUserFormData(1 To iLastRowNo, 1 To iColumnNo_Array)
            End If

            For iRowNo = 1 To iLastRowNo
                vaUserFormData(iRowNo, iColumnNo_Array) = .Cells(iRowNo, iColumnNo_Worksheet).Value
            Next iRowNo

        Next vColumnNo_Worksheet

'       Just to test the result, copy the array to the source worksheet - omit this from the final version
        With .Range("T1")
            Range(.Cells(1, 1), .Cells(iLastRowNo, iColumnNo_Array)).Value = vaUserFormData
        End With

    End With

End Sub

The highlighted code can be altered to suit your own requirements.


Hope this helps - as always, please let me know how you get on.

Regards,

Greg M

[nigelog]

@ Greg

hi Greg, thanks for the input
that code does indeed load the array required, what has me stuck is the function mvaEmployee() that previously used the named range, I am trying to get it to read the created array so I have no requirement for helper sheets

Private Function mvaEmployeeData() As Variant

    'Const sEMPLOYEE_DATA    As String = "tblEmployeeData3"
    'Const sEMPLOYEE_DATA    As String = "MyArray"
    Const sSHEET_NAME       As String = "Data"

    Dim vaEmployeeData      As Variant
    Dim rEmployeeData       As Range
    Dim wksData             As Worksheet

    Set wksData = ThisWorkbook.Worksheets(sSHEET_NAME)

    Set rEmployeeData = Array(vaEmployeeData)
    Set rEmployeeData = vaEmployeeData

    vaEmployeeData = rEmployeeData.Value

    mvaEmployeeData = vaEmployeeData

End Function

Ive been messing with it but keeps telling the array vaEmployeeData data is empty although it is created(I changed the name due to the references in the userform code)

[Greg M]

Hi again Nigel,

Try substituting the following code for the existing mvaEmployeeData function, and see if it does what you need:

Private Function mvaEmployeeData() As Variant

    Dim vColumnNo_Worksheet As Variant
    Dim iColumnNo_Worksheet As Integer
    Dim iColumnNo_Array     As Integer
    Dim vaEmployeeData      As Variant
    Dim iLastRowNo          As Integer
    Dim iRowNo              As Integer
    Dim wks                 As Worksheet

    iColumnNo_Array = 0

    Set wks = ActiveSheet

    With wks

        iLastRowNo = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'       Specify the column numbers from which data should be copied
        For Each vColumnNo_Worksheet In Array(1, 5, 10, 14)

            iColumnNo_Worksheet = CInt(vColumnNo_Worksheet)

            iColumnNo_Array = iColumnNo_Array + 1

            If iColumnNo_Array = 1 Then
                  ReDim vaEmployeeData(1 To iLastRowNo, 1 To iColumnNo_Array)
            Else: ReDim Preserve vaEmployeeData(1 To iLastRowNo, 1 To iColumnNo_Array)
            End If

            For iRowNo = 1 To iLastRowNo
                vaEmployeeData(iRowNo, iColumnNo_Array) = .Cells(iRowNo, iColumnNo_Worksheet).Value
            Next iRowNo

        Next vColumnNo_Worksheet

    End With

    mvaEmployeeData = vaEmployeeData

End Function

The highlighted values may be altered to suit your own requirements.


Hope this helps - please keep me posted.

Best regards,

Greg M

[nigelog]

Perfect Greg, thanks for the solution.

[Greg M]

Hi Nigel,

Many thanks for the feedback and also for the Reputation increase - much appreciated!

You're very welcome - delighted to have been able to help.

Best regards,

Greg M

[antonsachs]

Looping takes forever.