Populate Listbox with Two Columns from Two Different Arrays

[Dsewardj]

Hi,

I believe this is a relatively simple task yet it is evading me. I am attempting to populate two columns within a listbox from two different arrays.

Below is my code:

'Capture named ranges into arrays.
arrHeaderNew = Names("headerNew").RefersToRange.Value
arrHeaderOld = Names("headerOld").RefersToRange.Value
'Check arrays against each other and capture any errors.
Dim i As Integer
For i = 1 To headerNewCount
    If LCase(arrHeaderNew(1, i)) <> LCase(arrHeaderOld(1, i)) Then
        ReDim Preserve arrHeaderNewError(i)
        ReDim Preserve arrHeaderOldError(i)
            arrHeaderNewError(i) = arrHeaderNew(1, i)
            arrHeaderOldError(i) = arrHeaderOld(1, i)
    End If
Next i
'Populate listbox in frmNonMatchingHeaders with fields not matching.
With frmNonMatchingHeaders
    .lboxNonMatchingHeaders.List = arrHeaderNewError
    '.lboxNonMatchingHeaders.List = arrHeaderOldError
    .Show
 
End With
End Sub

I am able to populate the first column, although the first row is blank for some reason, and I do not believe the issue is with my array. I am just not certain how I can populate the second column?

Any suggestions would be greatly appreciated.

 

[Andrew Poulsom]

Here's an example:

Private Sub UserForm_Initialize()
    Dim Arr1, Arr2
    Dim i As Long
    Arr1 = Array("a", "b", "c")
    Arr2 = Array(1, 2, 3)
    With ListBox1
        For i = LBound(Arr1) To UBound(Arr1)
            .AddItem Arr1(i)
            .List(i, 1) = Arr2(i)
        Next i
    End With
End Sub

Listbox rows and columns are zero based, as are variant arrays.

 

[MickG]

This is based on your 2 named ranges, in my case "A1:A12" & "C1 :C12".
As you will see I just filled a "2D" array with both of the different values in from named ranges, in Column ! and 2 of the Array "Ray".

[COLOR="Navy"]Sub[/COLOR] MG03Mar13
[COLOR="Navy"]Dim[/COLOR] arrHeaderNew
[COLOR="Navy"]Dim[/COLOR] arrHeaderOld
[COLOR="Navy"]Dim[/COLOR] headerNewcount [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
arrHeaderNew = ActiveWorkbook.Names("headernew").RefersToRange.value
arrHeaderOld = ActiveWorkbook.Names("headerOld").RefersToRange.value
headerNewcount = UBound(arrHeaderNew)
ReDim ray(1 To UBound(arrHeaderNew), 1 To 2)
'[COLOR="Green"][B]Check arrays against each other and capture any errors.[/B][/COLOR]
[COLOR="Navy"]For[/COLOR] i = 1 To headerNewcount
    [COLOR="Navy"]If[/COLOR] LCase(arrHeaderNew(i, 1)) <> LCase(arrHeaderOld(i, 1)) [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            ray(c, 1) = arrHeaderNew(i, 1)
            ray(c, 2) = arrHeaderOld(i, 1)
'[COLOR="Green"][B]        ReDim Preserve arrHeaderNewError(i)[/B][/COLOR]
'[COLOR="Green"][B]        ReDim Preserve arrHeaderOldError(i)[/B][/COLOR]
'[COLOR="Green"][B]            arrHeaderNewError(i) = arrHeaderNew(1, i)[/B][/COLOR]
'[COLOR="Green"][B]            arrHeaderOldError(i) = arrHeaderOld(1, i)[/B][/COLOR]
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] i
'[COLOR="Green"][B]Populate listbox in frmNonMatchingHeaders with fields not matching.[/B][/COLOR]
[COLOR="Navy"]With[/COLOR] ListBox1
    .ColumnCount = 2
    .ColumnWidths = "50,50"
    .List = ray
[COLOR="Navy"]End[/COLOR] With
'[COLOR="Green"][B]With frmNonMatchingHeaders[/B][/COLOR]
'[COLOR="Green"][B]    .lboxNonMatchingHeaders.List = arrHeaderNewError[/B][/COLOR]
'[COLOR="Green"][B]    '.lboxNonMatchingHeaders.List = arrHeaderOldError[/B][/COLOR]
'[COLOR="Green"][B]    .Show[/B][/COLOR]
'[COLOR="Green"][B][/B][/COLOR]
'[COLOR="Green"][B]End With[/B][/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Dsewardj]

Hi MickG,

I have used your code but when the userform opens it shows no values in either columns although values should be populating, however no errors are occurring.

Andrew,

I was trying to avoid looping but I will use your example and build off of that as well.

Thanks for the replies.

 

[MickG]

The 2 columns in my arrays wher column "A" & "C".
You will need to amend the code (also column/row references) To take account of the Actual ranges you are using.
Mick