copy column data from one array to another

[LlebKcir]

2 workbooks, Master & Comm. Both have tables. The master holds more columns than the table in the Comm workbook.

I have been able to successfully identify the different order and correct columns between the two.
ex: Master table(s) have the order Column {1, 2, 3, ...} while the Comm table might have Column {15, 2, 41, ...}

So the first part of the loop identifies the column from Master table(s) [hard coded to one table for now] in the order of the table on Comm workbook.

Private Function ColNumToLet(ByVal dividend As Long)
    Dim columnName As String
    Dim modulo As Integer
    Dim tmp As Integer
    Dim char As String
    
    Do While dividend > 0
        modulo = (dividend - 1) Mod 26
        tmp = 65 + modulo
        char = Chr(tmp)
        columnName = char & columnName
        dividend = CInt((dividend - modulo) / 26)
    Loop
    ColNumToLet = columnName
End Function

The above private function converts the column # into the alphabetic value, so column 27 = AA and that works great. See images for the error and the step-through to find the location of the error.

The error is when I am attempting to populate the colSortedArr with data from masterSheetArr

Asking for help with that section.

Sub TEST()
    Dim arrm As Variant
    Dim arrc As Variant
    Dim masterSheetArr As Variant
    Dim colSortedArr As Variant
    Dim commSheetArr As Variant
    Dim wbm As Workbook
    Dim wsm As Worksheet
    Dim wbc As Workbook
    Dim wsc As Worksheet
    Dim lColm As String
    Dim lColc As String
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim lColmNum As Long
    Dim lColcNum As Long
    Dim LastRow As Integer
    
    Dim search As String
    
    Set wbm = ThisWorkbook
    Set wsm = wbm.Sheets("City")
    Set wbc = Workbooks("Comm.xlsm")
    Set wsc = wbc.Sheets("Overview2")
    LastRow = wsm.Cells(wsm.Rows.Count, "A").End(xlUp).Row
        
    lColmNum = wsm.Cells(1, Columns.Count).End(xlToLeft).Column
    lColm = ColNumToLet(lColmNum)
    lColcNum = wsc.Cells(1, Columns.Count).End(xlToLeft).Column
    lColc = ColNumToLet(lColcNum)
    masterSheetArr = wbm.Sheets("City").Range("A2:" & lColm & "1000")
    
    arrm = wsm.Range("A1:" & lColm & "1").Value2
    arrc = wsc.Range("A1:" & lColc & "1").Value2

    For i = 1 To lColcNum
        search = CStr(arrc(1, i))
        If search = "" Then GoTo done
        For j = 1 To lColmNum
            If CStr(arrm(1, j)) = search Then
                MsgBox ("Found " & search & " at " & j)
                'TODO: Copy found column data in master array (j) to sorted array (i)
                colSortedArr(i) = masterSheetArr(j)
                GoTo found
            ElseIf j = lColmNum Then
                MsgBox ("Did not find " & search)
                'TODO: Skip this column and look for next to map
            End If
        Next j
found:
    Next i
    'TODO: Search the city/network column to only copy the needed rows from sorted array to commissioning sheet array
    'TODO: Paste final array into comm sheet
done:
        
End Sub

Thank you in advance. Once I get this sorted it will be time to print the data from the colSortedArr into commSheetArr to be further manipulated by other code.

 

[LlebKcir]

Many thanks as always Fluff, help me follow the code please. I take it the way you used UBound that is a built in function for the upper bound range of the array. Will UBound(masterSheetArr) be the same value as LastRow I have in the code?

 

[Fluff]

Will UBound(masterSheetArr) be the same value as LastRow I have in the code?

unlikely, in this case the Ubound will always be 999 because of the way you have loaded the data into the array

 

[LlebKcir]

ahh ok, so because i set masterSheetArr at 1 to 1000, well A2:A1000, UBound will see 999, got ya.

Now that I have the colSortedArr, that should contain the data only from the 41 column headers listed in the Comm workbook, if I am following this code correctly.

Next step is to filter by City/Network, should be column I in the new array and delete all rows not matching the filter. Time to do more reading.

 

[Fluff]

ahh ok, so because i set masterSheetArr at 1 to 1000, well A2:A1000, UBound will see 999, got ya.

That's right

 

[LlebKcir]

is that also why I was receiving the error message? I was trying to pass a larger row count into the colSortedArr than masterSheetArr was providing?

 

[Fluff]

No you originally had the error because the colSortedArr had no "size", you had declared it & noting more.
Also you where referencing the MasterArr as if it was a 1D array, rather than a 2D array.

 

[LlebKcir]

ok, i follow that.

So many thanks Fluff. Tomorrow Ill focus on the 2 trim functions I need to build around the colSortedArr. I am sure I will be back with another thread. :D

 

[Fluff]

You're welcome & thanks for the feedback