VBA error 1004

[levy77]

I'm having a 1004 runtime error which I have been trying to workout for the past hour. Does anyone have any ideas? It shows the error on the ".Apply" line.

Workbooks.Open (FileName)
        Set aWB = ActiveWorkbook
        aWB.Worksheets(1).Activate
        erow = aWB.Worksheets(1).Range("A100000").End(xlUp).Row
        
        SelA = aWB.Worksheets(1).Range("A1").CurrentRegion.Address
        SelB = "$A$2:" & Right(SelA, 4)
        SelC = "$B$2:" & Right(SelA, 4)
        
        aWB.Worksheets(1).Range(SelA).Select
        aWB.Worksheets(1).Sort.SortFields.Clear
        aWB.Worksheets(1).Sort.SortFields.Add2 Key:=Range(SelB) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortTextAsNumbers
        aWB.Worksheets(1).Sort.SortFields.Add2 Key:=Range(SelC), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With aWB.Worksheets(1).Sort
            .SetRange Range(SelA)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

 

[Fluff]

What are you trying to do?

 

[levy77]

This is part of a sub where. The idea of the sub it to open another workbook (FileName) sort the data in there by Date (Column A) then by Buy/Sell (Column B) then it will grab the sorted data and put it into a variant array where the rest of the sub will decided what data to and not to put into the original workbook. All that is show here is the opening of the second workbook and attempting to sort the data in there.

 

[Fluff]

Ok, how about

   Set aWB = Workbooks.Open(Filename)
   aWB.Worksheets(1).Activate
   With aWB.Worksheets(1).Sort
      .SortFields.Clear
      .SortFields.Add2 Key:=Range("A1") _
         , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortTextAsNumbers
      .SortFields.Add2 Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal
      .SetRange Range("A1").CurrentRegion
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With

 

[levy77]

I like how it is much more to the point but it isn't actually sorting the data for some reason, If it helps I've attached a bit more code.

Dim FileName As Variant
    Dim tWB As Workbook
    Dim aWB As Workbook
    Dim A() As Variant
    Dim B() As Variant
    Dim erow As Integer
    Dim i As Integer
    
    Application.ScreenUpdating = False
    
    Set tWB = ThisWorkbook
    FileName = Application.GetOpenFilename("CSV File (*.csv),*.csv, Excel File (*.xlsx),*xlsx", , , , False)
    If FileName <> False Then
        tWB.Worksheets("All Trades").Activate
        erow = Range("A100000").End(xlUp).Row
        
        If erow <> 1 Then
            ReDim A(erow - 1, 5)
            For i = 2 To erow
                A(i - 1, 1) = Range("A" & i)
                A(i - 1, 2) = Range("B" & i)
                A(i - 1, 3) = Range("C" & i)
                A(i - 1, 4) = Range("D" & i)
                A(i - 1, 5) = Range("E" & i)
            Next i
        End If
            
        Set aWB = Workbooks.Open(FileName)
        aWB.Worksheets(1).Activate
        With aWB.Worksheets(1).Sort
            .SortFields.Clear
            .SortFields.Add2 Key:=Range("A1") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortTextAsNumbers
            .SortFields.Add2 Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
            .SetRange Range("A1").CurrentRegion
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        erow = aWB.Worksheets(1).Range("A100000").End(xlUp).Row
        
        ReDim B(erow - 1, 7)
        For i = 2 To erow
            B(i - 1, 1) = Range("A" & i)
            B(i - 1, 2) = Range("B" & i)
            B(i - 1, 3) = Range("C" & i)
            B(i - 1, 4) = Range("D" & i)
            B(i - 1, 5) = Range("E" & i)
            B(i - 1, 6) = Range("F" & i)
            B(i - 1, 7) = Range("G" & i)
        Next i
    Else
    
    End If

 

[Fluff]

Do you have any blank rows or columns in your data?

 

[levy77]

I thought it could have been Range("A1:A58") rather than Range("A1") but that still didn't work either, the data looks as below, there are no spaces in the data.

Trade Date	Buy/ Sell	Security	Units	Average Price ($)	Brokerage (inc GST.)	Net Proceeds ($)
4/06/2020​	S	IFN	2000​	0.813​	19.95​	1605.05​
4/06/2020​	S	GGUS	100​	17.05​	19.95​	1685.05​
4/06/2020​	S	GEAR	131​	18​	19.95​	2338.05​
4/06/2020​	S	VMIN	50​	54.12​	19.95​	2686.05​
4/06/2020​	S	IVV	3​	452.9​	19.95​	1338.75​
4/06/2020​	S	ADH	800​	1.921​	19.95​	1516.85​
21/04/2020​	B	ADH	800​	1.24​	10​	1002​
17/04/2020​	B	GGUS	100​	14.65​	19.95​	1484.95​
17/04/2020​	B	GEAR	100​	15.02​	19.95​	1521.95​

 

[Fluff]

Are you dates real dates, or text?

 

[levy77]

Dates are formatted as dates and not text.

 

[Fluff]

If you change the format to general what do you see?