Using Sort within a loop

Dreamteam

Dreamteam

New Member
Joined
Feb 22, 2018
Messages
28
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

I have written some code that looks at a database and filters on a user number and then pastes the data onto worksheets (named as the user numbers) - all of this part is working fine - I have created a very simple array to store the user numbers. I have some additional formatting for centering the data etc.

Code: 
Option Explicit

Sub Macro1()


Dim wb As Workbook
Set wb = ThisWorkbook
Dim wsdata As Worksheet
Set wsdata = wb.Worksheets("data")
Dim rngdataforfilter As Range
Set rngdataforfilter = wsdata.Range("$A$1:$l$1")
Dim rngstartcell As Range
Set rngstartcell = wsdata.Range("a1")


Dim strdrivers(9) As String
Dim i As Integer


If wsdata.ProtectContents = True Then
  Exit Sub
End If




strdrivers(0) = "1"
strdrivers(1) = "3"
strdrivers(2) = "4"
strdrivers(3) = "5"
strdrivers(4) = "6"
strdrivers(5) = "7"
strdrivers(6) = "8"
strdrivers(7) = "14"
strdrivers(8) = "15"
strdrivers(9) = "17"


Application.ScreenUpdating = False


    For i = LBound(strdrivers) To UBound(strdrivers)
    
    rngdataforfilter.AutoFilter Field:=10, Criteria1:=strdrivers(i)
    rngstartcell.CurrentRegion.Copy
    
    Dim rngstartcelldriverws As Range
    Set rngstartcelldriverws = Worksheets(strdrivers(i)).Range("a1")
    
    With Worksheets(strdrivers(i))
       rngstartcelldriverws.PasteSpecial Paste:=xlPasteValues
      .Range("C:C").NumberFormat = "dd/mm/yyyy"
      .Columns.AutoFit
       rngstartcelldriverws.CurrentRegion.HorizontalAlignment = xlCenter
    End With
     
    Next i


Worksheets("data").ShowAllData
   
Application.ScreenUpdating = True
   
End Sub

However, the final piece to the puzzle is just to simply sort the data by date which is column C. I have tried many ways of doing this but none of them seem to work - which I am struggling to understand why not. I want to include this sort into my loop - where everything else is working fine. The annoying thing is that if I write the code procedurally - ie removing the loop and having the code many times larger than it is now - then it works?! I have not included any of the code that I have tried to use for the sorting.

I will try to include one of the worksheets that are created by the code so far.

Any help would be much appreciated.



[TABLE="width: 781"]
<tbody>[TR]
[TD]MONTH[/TD]
[TD]DAY[/TD]
[TD]DATE[/TD]
[TD]CONTRACT[/TD]
[TD]PICK UP[/TD]
[TD]DROP OFF[/TD]
[TD]FARE (£)[/TD]
[TD]DRIVER REF[/TD]
[TD]PICK UP/RETURN[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]TUE[/TD]
[TD]01/05/2018[/TD]
[TD]COSAWES[/TD]
[TD]COSAWES[/TD]
[TD]PEN COLL[/TD]
[TD]20[/TD]
[TD]8[/TD]
[TD]PICK UP[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]TUE[/TD]
[TD]01/05/2018[/TD]
[TD]COSAWES[/TD]
[TD]PEN COLL[/TD]
[TD]COSAWES[/TD]
[TD]20[/TD]
[TD]8[/TD]
[TD]RETURN[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]WED[/TD]
[TD]02/05/2018[/TD]
[TD]B MEWS[/TD]
[TD]MABE PRIM[/TD]
[TD]B MEWS[/TD]
[TD]15[/TD]
[TD]8[/TD]
[TD]RETURN[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]FRI[/TD]
[TD]04/05/2018[/TD]
[TD]BASS ACCACIA[/TD]
[TD]BASS ACACIA[/TD]
[TD]PEN COLL[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]PICK UP[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]FRI[/TD]
[TD]04/05/2018[/TD]
[TD]BASS ACCACIA[/TD]
[TD]PEN COLL[/TD]
[TD]BASS ACACIA[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]RETURN[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]WED[/TD]
[TD]09/05/2018[/TD]
[TD]SWAN MONGL[/TD]
[TD]SWAN MONGL[/TD]
[TD]FAL SCH[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]PICK UP[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]THU[/TD]
[TD]10/05/2018[/TD]
[TD]BASS ACCACIA[/TD]
[TD]BASS ACACIA[/TD]
[TD]PEN COLL[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]PICK UP[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]THU[/TD]
[TD]10/05/2018[/TD]
[TD]BASS ACCACIA[/TD]
[TD]PEN COLL[/TD]
[TD]BASS ACACIA[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]RETURN[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]MON[/TD]
[TD]14/05/2018[/TD]
[TD]TAP[/TD]
[TD]PONSNOOTH[/TD]
[TD]NEWQUAY[/TD]
[TD]60[/TD]
[TD]8[/TD]
[TD]PICK UP[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]MON[/TD]
[TD]14/05/2018[/TD]
[TD]TAP[/TD]
[TD]NEWQUAY[/TD]
[TD]PONSANOOTH[/TD]
[TD]60[/TD]
[TD]8[/TD]
[TD]RETURN[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]MON[/TD]
[TD]14/05/2018[/TD]
[TD]COSAWES[/TD]
[TD]PEN COLL[/TD]
[TD]COSAWES[/TD]
[TD]20[/TD]
[TD]8[/TD]
[TD]RETURN[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]TUE[/TD]
[TD]15/05/2018[/TD]
[TD]BASS ACCACIA[/TD]
[TD]BASS ACACIA[/TD]
[TD]PEN COLL[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]PICK UP[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]TUE[/TD]
[TD]15/05/2018[/TD]
[TD]BASS ACCACIA[/TD]
[TD]PEN COLL[/TD]
[TD]BASS ACACIA[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]RETURN[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]TUE[/TD]
[TD]15/05/2018[/TD]
[TD]PENRYN COLLEGE/KERG[/TD]
[TD]KERGILLIACK[/TD]
[TD]PEN COLL[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]PICK UP[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]WED[/TD]
[TD]16/05/2018[/TD]
[TD]B MEWS[/TD]
[TD]B MEWS[/TD]
[TD]MABE PRIM[/TD]
[TD]15[/TD]
[TD]8[/TD]
[TD]PICK UP[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]WED[/TD]
[TD]16/05/2018[/TD]
[TD]TREGEW[/TD]
[TD]TREGEW[/TD]
[TD]DRAC CNTRE[/TD]
[TD]15[/TD]
[TD]8[/TD]
[TD]PICK UP[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]WED[/TD]
[TD]16/05/2018[/TD]
[TD]B MEWS[/TD]
[TD]MABE PRIM[/TD]
[TD]B MEWS[/TD]
[TD]15[/TD]
[TD]8[/TD]
[TD]RETURN[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]THU[/TD]
[TD]17/05/2018[/TD]
[TD]COSAWES[/TD]
[TD]COSAWES[/TD]
[TD]PEN COLL[/TD]
[TD]20[/TD]
[TD]8[/TD]
[TD]PICK UP[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]THU[/TD]
[TD]17/05/2018[/TD]
[TD]COSAWES[/TD]
[TD]PEN COLL[/TD]
[TD]COSAWES[/TD]
[TD]20[/TD]
[TD]8[/TD]
[TD]RETURN[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]THU[/TD]
[TD]03/05/2018[/TD]
[TD]BAPTIST CHURCH[/TD]
[TD]MEADOW,THEY[/TD]
[TD]BAP CHURCH[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]PICK UP[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]THU[/TD]
[TD]10/05/2018[/TD]
[TD]BAPTIST CHURCH[/TD]
[TD]MEADOW,THEY[/TD]
[TD]BAP CHURCH[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]PICK UP[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]MON[/TD]
[TD]21/05/2018[/TD]
[TD]SWAN MONGL[/TD]
[TD]SWAN MONGL[/TD]
[TD]FAL SCH[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]PICK UP[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]MON[/TD]
[TD]21/05/2018[/TD]
[TD]SWAN MONGL[/TD]
[TD]FAL SCH[/TD]
[TD]SWANP MONGL[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]RETURN[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]TUE[/TD]
[TD]22/05/2018[/TD]
[TD]BERKLEY COTTAGES[/TD]
[TD]LOWENA[/TD]
[TD]FALCARE[/TD]
[TD]25[/TD]
[TD]8[/TD]
[TD]RETURN[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited by a moderator:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
How about
Code: 
   With Worksheets(strdrivers(i))
      rngstartcelldriverws.PasteSpecial Paste:=xlPasteValues
      .Range("C:C").NumberFormat = "dd/mm/yyyy"
      .Columns.AutoFit
      rngstartcelldriverws.CurrentRegion.HorizontalAlignment = xlCenter
      .Sort.SortFields.Clear
      .Sort.SortFields.Add key:=Range("C2"), SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
      With .Sort
         .SetRange Range("A1").CurrentRegion
         .Header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .Apply
      End With
   End With
 
Upvote 0
Fluff - many, many thanks - this works perfectly. I will now go away and work out why my previous attempts failed as they were so close.

Once again - thank you :~)
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
Fluff said:
Glad to help & thanks for the feedback
Click to expand...

Hi Fluff

Can I ask a question please about the solution that you gave me...

Are you actually using Sort as an object in its own right; it does look like it. I was under the impression that Sort was a method of the Range object and did not know that it could be used like this.

Many thanks

Dt
 
Upvote 0
It's a method of the range object in older versions of Excel, and it still works.

Code: 
range("A1"),currentregion.sort key1:=range("C2"), Header:=xlyes
 
Upvote 0
shg said:
It's a method of the range object in older versions of Excel, and it still works.

Code: 
range("A1"),currentregion.sort key1:=range("C2"), Header:=xlyes
Click to expand...

Hi shg

Thanks for this.

Can I dig a bit deeper here if you don’t mind - fairly new to vba but trying very hard to write the correct way ie trying to use references to ranges etc and NEVER using .Select ;~)

So, whilst it can be used as a method of Range object - it can also be used as a discrete object having its own methods and properties?

Many thanks
 
Upvote 0
See Help for Sort Object. Fluff's code shows examples of its properties and methods.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
548
Eric W
Eric W
C
  • Question Question
How To Use VLOOKUP Using Date, To Find Corresponding Holiday With Additional Criteria
Replies
1
Views
1K
ExceLoki
ExceLoki
A
  • Solved
HLOOKUP returns #N/A, but the value is there
2
Replies
10
Views
2K
Anthony47
A
S
  • Solved
using advanced filter with 2 criteria one having a list & the other having text or no text
Replies
7
Views
832
RoryA
RoryA
R
  • Question Question
If an Input range contains a specific text, then look up that value in another range and return a value from a different column
Replies
0
Views
610
RaufR
R

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top