Assigning data to an array

[mchac]

If I can do this:

Sub tester()
    Dim LATEST_SortSheet As String
    Dim LAST_COLUMN_NUMBER As Long
    Dim LAST_COLUMN_LETTER As String
    Dim LAST_ROW_NUMBER As Long
    Dim DATA_RANGE As Range
    Dim DATA_ARRAY()
    
    LATEST_SortSheet = Sheets("Master Input Source Run").Range("D" & Sheets("Master Input Source Run").Range("D" & Rows.Count).End(xlUp).Row).Value
    LAST_COLUMN_NUMBER = Sheets(LATEST_SortSheet).Cells.Find("*", Sheets(LATEST_SortSheet).Range("A1"), xlFormulas, xlPart, xlByColumns, xlPrevious).Column
    LAST_COLUMN_LETTER = Split(Evaluate("address(1," & LAST_COLUMN_NUMBER & ")"), "$")(1)
    LAST_ROW_NUMBER = Sheets(LATEST_SortSheet).Cells.Find("*", Sheets(LATEST_SortSheet).Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious).Row
            
    Set DATA_RANGE = Sheets(LATEST_SortSheet).Range("A2:" & LAST_COLUMN_LETTER & LAST_ROW_NUMBER)
    
    ReDim DATA_ARRAY(1 To LAST_ROW_NUMBER - 1, 1 To LAST_COLUMN_NUMBER)
    
    DATA_ARRAY = DATA_RANGE   

End Sub

Why can't I do it directly like this?
DATA_ARRAY = Sheets(LATEST_SortSheet).Range("A2:" & LAST_COLUMN_LETTER & LAST_ROW_NUMBER)

I get a type mismatch error.


Thanks in advance.

 

[VBA Geek]

Try without Redimming the Array

 

[mchac]

Thanks but I get the same error. Here's the amended code.

Sub tester()
    Dim LATEST_SortSheet As String
    Dim LAST_COLUMN_NUMBER As Long
    Dim LAST_COLUMN_LETTER As String
    Dim LAST_ROW_NUMBER As Long
    Dim DATA_RANGE As Range
    Dim DATA_ARRAY()
    
    'Create Array from Data in the LatestSortSheet
    LATEST_SortSheet = Sheets("Master Input Source Run").Range("D" & Sheets("Master Input Source Run").Range("D" & Rows.Count).End(xlUp).Row).Value
    LAST_COLUMN_NUMBER = Sheets(LATEST_SortSheet).Cells.Find("*", Sheets(LATEST_SortSheet).Range("A1"), xlFormulas, xlPart, xlByColumns, xlPrevious).Column
    LAST_COLUMN_LETTER = Split(Evaluate("address(1," & LAST_COLUMN_NUMBER & ")"), "$")(1)
    LAST_ROW_NUMBER = Sheets(LATEST_SortSheet).Cells.Find("*", Sheets(LATEST_SortSheet).Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious).Row
            
    'Set DATA_RANGE = Sheets(LATEST_SortSheet).Range("A2:" & LAST_COLUMN_LETTER & LAST_ROW_NUMBER)
    
    'ReDim DATA_ARRAY(1 To LAST_ROW_NUMBER - 1, 1 To LAST_COLUMN_NUMBER)
    
    DATA_ARRAY = Sheets(LATEST_SortSheet).Range("A2:" & LAST_COLUMN_LETTER & LAST_ROW_NUMBER)  
End Sub