1

Ok, So i'm trying to do a custom sort on my Classified Table via a Reference Table, here is a small portion of the reference table

Reference Table

Now, whenever I sort my Classified Table, it becomes this

Classified Table

as you can see, It only sorts up to a certain number of rows (C&J Global Enterprises), then the rest is sorted alphabetically (Which is not what I want)

Here is my VBA Code

Private Sub WorkSheet_Activate()

Dim MainSheet As Worksheet, RefSheet As Worksheet
Set MainSheet = Worksheets("Classifieds Client Table")
Set RefSheet = Worksheets("RefTable")

Dim RangeOne As Range, RangeTwo As Range
Set RangeOne = MainSheet.Range("A2:A150")
Set RangeTwo = RefSheet.Range("A1:A137")

Dim MainRange As Range
Set MainRange = MainSheet.Range("A2:H150")

Application.AddCustomList ListArray:=RangeTwo
MainSheet.Sort.SortFields.Clear

MainRange.Sort Key1:=MainRange, Header:=xlNo, _
OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Application.DeleteCustomList Application.CustomListCount

Thanks in Advance!

El Padrino Lucio Pilar
  • 15
  • 5

1 Answers1

0

SortOn is not a valid parameter for Range.Sort.

I suspect you meant to use SortMethod.

Fix that (and also remove any extraneous code that's unrelated to the answer) and if it's still not sorting by your custom list I will try to edit my answer and figure it out.

More Information:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • Sir, I have edited my code but it still doesn't sort properly. I have removed all special characters, replaced numbers with words but it still doesn't sort correctly. It only sorts correctly up to a certain row and then the rest is sorted alphabetically – El Padrino Lucio Pilar Feb 20 '18 at 08:28
  • okay, I can take a look... but can you replace the 1st screenshot with the same data but pasted in text (as a code block) so I can copy & paste it? – ashleedawg Feb 20 '18 at 08:45
  • Why do you use a `+1` when referring to the list? – ashleedawg Feb 20 '18 at 09:24
  • Actually, I used the answer in the link provided by Tim Williams, you can take a look at the file I use https://drive.google.com/open?id=1_O4RToVeFjmELIkNGX17ud9HL3al1FhE – El Padrino Lucio Pilar Feb 20 '18 at 09:31
  • yeah I'm looking – ashleedawg Feb 20 '18 at 10:25
  • Dude. The [updated] code from your question, run against the data from your link, works perfectly. Did you try running it since you shortened the code? – ashleedawg Feb 20 '18 at 10:28
  • the copy on your OneDrive didn't have VBA so I pasted in your code from the question, and it sorts the entire list exactly list it's supposed to. Here is the file back: http://jmp.sh/PU0Sz5s – ashleedawg Feb 20 '18 at 10:31
  • What the. When I try my code, I get Method 'AddCustomList' of object '_Application' failed and when I debug it, it points to this line Application.AddCustomList ListArray:=RangeTwo – El Padrino Lucio Pilar Feb 20 '18 at 10:50
  • Sir, can you try this complete code of mine? https://drive.google.com/open?id=1aHE5laLOdfKESDcQ7TGX1HBSirOG0HBW I think there is something wrong with it – El Padrino Lucio Pilar Feb 20 '18 at 11:03
  • Did you try the one I sent back? I don't have time to do it again, sorry. Also add `Option Explicit` at the top of your modules. – ashleedawg Feb 20 '18 at 11:32
  • Well, unfortunately, the file you provided doesn't have any VBA Code – El Padrino Lucio Pilar Feb 20 '18 at 12:00
  • don't use the online view; download the file. or, just copy the code from your verson in to the blank module and run it – ashleedawg Feb 20 '18 at 12:01
  • Sir, The code that I posted is still not working. I already tried different ways, but it still only sorts up to a certain row, then the rest (rows below) sorts alphabetically and does not follow my reference table – El Padrino Lucio Pilar Feb 26 '18 at 11:28