Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-07-2018, 06:25 AM
Phil H Phil H is offline Modify Macro to Sort Last Four Characters Windows XP Modify Macro to Sort Last Four Characters Office 2010 64bit
Advanced Beginner
Modify Macro to Sort Last Four Characters
 
Join Date: Jun 2010
Posts: 81
Phil H is on a distinguished road
Default Modify Macro to Sort Last Four Characters

It would be much appreciated if someone could help modify this code to sort by the last four characters. Cells in the range contain entries of this format: N12345-12-A-1234. I need the code to sort by the last four numbers.



Again - appreciate any help.


Code:
Private Sub CommandButton23_Click()
    Range("B3:AA39").Select
    ActiveWorkbook.Worksheets("Current Status of CORs").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Current Status of CORs").Sort.SortFields.Add Key:= _
        Range("G4:G39"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Current Status of CORs").Sort
        .SetRange Range("B3:AA39")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub
Reply With Quote
  #2  
Old 06-07-2018, 06:50 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Modify Macro to Sort Last Four Characters Windows 7 64bit Modify Macro to Sort Last Four Characters Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Could you eventually add a "helper column" with =RIGHT(A1,4) and use that new column to sort ?
(I'm no VBA man myself)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 06-07-2018, 07:54 AM
Phil H Phil H is offline Modify Macro to Sort Last Four Characters Windows XP Modify Macro to Sort Last Four Characters Office 2010 64bit
Advanced Beginner
Modify Macro to Sort Last Four Characters
 
Join Date: Jun 2010
Posts: 81
Phil H is on a distinguished road
Default

Can't do that - the data column is part of fixed form
Reply With Quote
  #4  
Old 06-07-2018, 09:54 AM
NoSparks NoSparks is offline Modify Macro to Sort Last Four Characters Windows 7 64bit Modify Macro to Sort Last Four Characters Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Attach a sample workbook so we know what you're dealing with and can test our ideas before posting them.
Reply With Quote
  #5  
Old 06-07-2018, 11:13 AM
Phil H Phil H is offline Modify Macro to Sort Last Four Characters Windows XP Modify Macro to Sort Last Four Characters Office 2010 64bit
Advanced Beginner
Modify Macro to Sort Last Four Characters
 
Join Date: Jun 2010
Posts: 81
Phil H is on a distinguished road
Default

Attached data format



Contract Sort.xlsm
Reply With Quote
  #6  
Old 06-07-2018, 11:46 PM
Shashi Kant Shashi Kant is offline Modify Macro to Sort Last Four Characters Windows 7 32bit Modify Macro to Sort Last Four Characters Office 2016
Novice
 
Join Date: May 2018
Posts: 16
Shashi Kant is on a distinguished road
Default

Dear Friend,


Kindly find attachment, it might be useful for you.
Attached Files
File Type: xlsm Contract Sort.xlsm (24.8 KB, 9 views)
Reply With Quote
  #7  
Old 06-08-2018, 07:18 AM
Phil H Phil H is offline Modify Macro to Sort Last Four Characters Windows XP Modify Macro to Sort Last Four Characters Office 2010 64bit
Advanced Beginner
Modify Macro to Sort Last Four Characters
 
Join Date: Jun 2010
Posts: 81
Phil H is on a distinguished road
Default

Shashi, Thanks for taking the time to address my problem, much appreciated. Your solution for CommandButton1 works. Now I have to integrate it into my worksheet/format. Originally, I used the macro recorder to create the code, which does a typical ascending sort. In my post, the range of all columns and rows sorted is B3:AA39. The sort column range is G4:G39 (where the contract numbers are held).

Running the code, I get a Run-time error '1004': This formula is missing a range reference of a defined name. Clicking Debug, the "sort now" line is highlighted: Range("A4").Sort....

Column A is not used for data entry.
How to modify the code?
Reply With Quote
  #8  
Old 06-11-2018, 12:33 AM
Shashi Kant Shashi Kant is offline Modify Macro to Sort Last Four Characters Windows 7 32bit Modify Macro to Sort Last Four Characters Office 2016
Novice
 
Join Date: May 2018
Posts: 16
Shashi Kant is on a distinguished road
Default

Dear Friend,

Kindly follow attachment...


it will work according to your range, if you increase your range still it will work or if you decrease your range still it will work.

if not working kindly send your file and your required output on other sheet for my better understanding.
Attached Files
File Type: xlsm Contract Sort.xlsm (38.4 KB, 8 views)
Reply With Quote
  #9  
Old 06-11-2018, 06:00 AM
Phil H Phil H is offline Modify Macro to Sort Last Four Characters Windows XP Modify Macro to Sort Last Four Characters Office 2010 64bit
Advanced Beginner
Modify Macro to Sort Last Four Characters
 
Join Date: Jun 2010
Posts: 81
Phil H is on a distinguished road
Default

All I need is to sort the column by the last four numbers.
Attached Files
File Type: xlsm Sort Last Four Numbers Spreadsheet.xlsm (43.5 KB, 8 views)

Last edited by Phil H; 06-11-2018 at 12:16 PM.
Reply With Quote
  #10  
Old 06-11-2018, 12:21 PM
Phil H Phil H is offline Modify Macro to Sort Last Four Characters Windows XP Modify Macro to Sort Last Four Characters Office 2010 64bit
Advanced Beginner
Modify Macro to Sort Last Four Characters
 
Join Date: Jun 2010
Posts: 81
Phil H is on a distinguished road
Default Sorting Last Four Numbers

Resubmitting uploaded file showing the original worksheet
Attached Files
File Type: xlsm Sort Last Four Numbers Spreadsheet.xlsm (41.2 KB, 12 views)
Reply With Quote
  #11  
Old 06-11-2018, 10:22 PM
Shashi Kant Shashi Kant is offline Modify Macro to Sort Last Four Characters Windows 7 32bit Modify Macro to Sort Last Four Characters Office 2016
Novice
 
Join Date: May 2018
Posts: 16
Shashi Kant is on a distinguished road
Default

I hope it would be worthy now
Attached Files
File Type: xlsm Sort Last Four Numbers Spreadsheet.xlsm (41.2 KB, 11 views)
Reply With Quote
  #12  
Old 06-12-2018, 04:05 AM
Phil H Phil H is offline Modify Macro to Sort Last Four Characters Windows XP Modify Macro to Sort Last Four Characters Office 2010 64bit
Advanced Beginner
Modify Macro to Sort Last Four Characters
 
Join Date: Jun 2010
Posts: 81
Phil H is on a distinguished road
Default

Shashi, This works as needed. Thank you for all your time writing this macro - it will improve process efficiency when scanning a list by eye trying to locate a particular number. Again thank you. R/Phil
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
custom sort macro ewso Excel Programming 7 10-10-2017 12:08 PM
Modify recorded macro to run until end of document peter961 Word VBA 1 09-04-2017 02:50 PM
Modify Macro to Sort Last Four Characters how modify a macro code tarikov2006 Excel Programming 1 11-18-2016 04:10 AM
Modify Macro to Sort Last Four Characters Macro to sort columns SerenityNetworks Excel Programming 4 09-02-2016 06:20 AM
Modify Macro to Sort Last Four Characters Sort strings by number of characters reneforster Word VBA 1 12-04-2014 07:25 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:53 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft