+ Reply to Thread
Results 1 to 2 of 2

Problem re-assigning array after a sort

Hybrid View

welchs101 Problem re-assigning array... 04-21-2012, 07:24 AM
welchs101 Re: Problem re-assigning... 04-21-2012, 08:58 AM
  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Problem re-assigning array after a sort

    Hi,

    I have an array where i read in the file names from a directory.

    Note: When trying to run this macro create files with file names such as this
    AA1_AA1_AA1_DET_owner.xls
    AA2_AA2_AA2_DET_owner.xls
    etc


    The array used to store the file names has the format like this
    file_array(1,1)
    file_array(1,2)
    file_array(1,3)
    file_array(1,4)

    file_array(2,1)
    file_array(2,2)
    file_array(2,3)
    file_array(2,4)


    I dont know if you call this a multi-dimensional array......not sure.

    Anyway, i want to sort this array by the file_array(2, values.

    So what i do is transpose the array , then paste into excel sheet, sort and then re-sort again and try to put back into arrray.

    The re-transpose and trying to put back into the array is where i am having issues.

    Its not doing it correctly.

    Can someone help me? I have tried various things to no avail.
    Attached Files Attached Files
    Last edited by welchs101; 04-21-2012 at 09:00 AM.

  2. #2
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: Problem re-assigning array after a sort

    i figured it out......i changed the sort routine.....to this........



    '*****************************************************************
    '*****************************************************************
    Sub sort_file_array(fl_macro As String, file_array1 As Variant)
    
    Set ws_macro = Workbooks(fl_macro).Worksheets(misc_info_sht)
    
    num_entries = UBound(file_array1, 2)
    
    If UBound(file_array1, 2) > 0 Then
    
        'we will use col E and F on the Misc_info_sheet  of the macro to paste
        'contents of the array .......sort in alphabetical order then read it
        'back into the array
        
        'paste array into col E
        'ws_macro.Range("E1").Resize(UBound(file_array1, 2)).Value = WorksheetFunction.Transpose(file_array1)
        'ws_macro.Range("E1").Resize(UBound(file_array1, 2)).Value = file_array1
        'ws_macro.Range("E1").Resize(UBound(file_array1, 2)).Value = Application.Transpose(file_array1)
        ws_macro.Range("E1:F" & num_entries).Value2 = Application.Transpose(file_array1)
        
        
        'sort col k in alphabetical order......use col K from this sheet to help sort data
        ws_macro.Range("E1:F" & num_entries).Sort _
                    Key1:=ws_macro.Columns(6), Order1:=xlAscending, _
                    Key2:=ws_macro.Columns(5), Order1:=xlAscending, _
                    Header:=xlNo
        
        'store alphabetized list into array
        file_array1 = ws_macro.Range("E1:F" & num_entries).Value2
        file_array1 = Application.Transpose(file_array1)
        
        MsgBox ("stope")
        'clear contents only in col
        ws_macro.Range("E:F").ClearContents
                    
        'ws_macro.Range("m1").Resize(UBound(file_array1, 1), 1).Value = file_array1
    
    End If
    
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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