Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-21-2012, 08:26 AM
gbaker gbaker is offline Sorting Challenge Windows 7 32bit Sorting Challenge Office 2010 32bit
Competent Performer
Sorting Challenge
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Sorting Challenge

I have a challenge Sorting a list. Instead of the information moving to the top it falls to the bottom. All the blanks are on the top.
I have attached a worksheet showing the issue I am facing.
Can anyone help!!!
Thanks in advance.







Code:
Sub testforforum()
'
' testforforum Macro
'
'
    ActiveWindow.SmallScroll Down:=102
    Range("A1:L").Select
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range( _
        "A1:A15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range( _
        "B1:B15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("DATA").Sort
        .SetRange Range("A1:A15")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Attached Files
File Type: xlsx test for forum.xlsx (9.8 KB, 14 views)
Reply With Quote
  #2  
Old 06-21-2012, 08:51 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Sorting Challenge Windows 7 32bit Sorting Challenge Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

It's because those "empty" cells in column B aren't actually empty.

Using your attached workbook and code as an example, you can force the empty strings to be cleared from the cells like this:
Code:
Sub Example()
 
    With Sheet1
        'get rid of any null strings stored in the cells
        With .Range("A1:L15")
            .Value = .Value
        End With
 
 
        With .Sort
 
            'set up the sort fields
            With .SortFields
                .Clear
                .Add Key:=Sheet1.Range("A1:A15"), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
 
                .Add Key:=Sheet1.Range("B1:B15"), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
            End With
 
            'set up the rest of the sort and apply
            .SetRange Sheet1.Range("A1:L15")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 
End Sub
Reply With Quote
  #3  
Old 06-21-2012, 09:05 AM
gbaker gbaker is offline Sorting Challenge Windows 7 32bit Sorting Challenge Office 2010 32bit
Competent Performer
Sorting Challenge
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default

Thanks Colin,
I'll try it. and let you know how it works. New at this!!!!
Reply With Quote
  #4  
Old 06-21-2012, 11:35 AM
gbaker gbaker is offline Sorting Challenge Windows 7 32bit Sorting Challenge Office 2010 32bit
Competent Performer
Sorting Challenge
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Sort Challenge

Hi Colin,
Like I said I'm new at this. Can't make it work. Code I used based on the information you gave me but I must be doing something wrong because it come up with a runtime error 424 on this line:

With .Range("Cr2:Cr3980")

Here is the code I'm using for my sheet which I will also attach.

Code:
Sub sortnew()
'
' sortnew  Macro
'
'
    With Data
'get rid of any null strings stored in the cells
     With .Range("Cr2:Cr3980")
            .Value = .Value
    End With
    With .Sort
'set up the sort fields
    With .Sort.SortFields
   .Clear
   .Add Key:=Data.Range("Cr2:Cr3980"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   .Add Key:=Data.Range("Cs2:Cs3980"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   .Add Key:=Data.Range("Ct2:Ct3980"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   .Add Key:=Data.Range("Cu2:Cu3980"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
     End With
        .SetRange Data.Range("Cs2:DE3980")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlSortColumns
        .SortMethod = xlPinYin
        .Apply
      End With
    End With
 
End Sub
Attached Files
File Type: xlsm test sheet for Colin.xlsm (225.9 KB, 9 views)
Reply With Quote
  #5  
Old 06-21-2012, 11:52 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Sorting Challenge Windows 7 32bit Sorting Challenge Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Hi,

It's no problem that you're new to this - we're here to help. Rather than give you the answer, I'll ask you a question because I think it'll help you understand what the problem is: in your code, what is Data?
Reply With Quote
  #6  
Old 06-21-2012, 12:44 PM
gbaker gbaker is offline Sorting Challenge Windows 7 32bit Sorting Challenge Office 2010 32bit
Competent Performer
Sorting Challenge
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Sorting Challenge

Hi Colin,
The Data comes from a calendar. It is a list of mailing that we do. I designed a input screen that mailings can be put into and then each day is linked to a calendar to be reviewed by a few vendors and staff. The information I gave you is just one vendor. I need to pastevalue to the sheet I showed you and then sort it so it will appear on the calendar in order.
I have attached an example of the end result I'm looking for.
I want to get rid of the spaces and have everything go to the top.
Hope this explains it. The origional file is over 11 MB so I can't send you the whole workbook.
Thanks
Reply With Quote
  #7  
Old 06-21-2012, 01:54 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Sorting Challenge Windows 7 32bit Sorting Challenge Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Okay, you've slightly misunderstood my quesiton. Let me explain what I was driving at....

In my example I used
Code:
With Sheet1
Sheet1 is the codename of the worksheet in the attachment you gave in post #1. So my code is doing stuff with Sheet1. It understands what Sheet1 is so the code runs fine.

In your code you used this instead:
Code:
   With Data
Data isn't anything - it's just a word (a variable) which you've put in your code. You're getting an error message because it doesn't know what Data is. Your code needs to explain which worksheet the sort should be happening on. To put it slightly more technically: you don't have variable declaration enforced (with an Option Explicit statement) so when the compiler scans your code it creates a Variant variable called Data with holds a value of Empty.


So you need to either use the worksheet's codename like I did (you can see the codename in the VBA IDE in the project explorer window - see my attachment) or you need to get a reference to the worksheet via the Worksheets collection using either the worksheet's name or it's index (eg With Worksheets("Data"). Does that make sense?
Attached Images
File Type: jpg Codename vs Name.jpg (84.3 KB, 12 views)
Reply With Quote
  #8  
Old 06-22-2012, 05:58 AM
gbaker gbaker is offline Sorting Challenge Windows 7 32bit Sorting Challenge Office 2010 32bit
Competent Performer
Sorting Challenge
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Sort Challenge

Hi Colin,
I understand now. Even though the Tab is Data in the Worksheet, the actual name of the sheet is Sheet6. It Works. Thanks for your help making me understand. I can go forward now with the project.
I attached the worksheet for you to review.
Thanks again.
Gary
Attached Files
File Type: xlsm test sheet for Colin.xlsm (206.2 KB, 9 views)
Reply With Quote
  #9  
Old 06-22-2012, 07:58 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Sorting Challenge Windows 7 32bit Sorting Challenge Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Hi Gary,

Well done for getting it to work!

Right, so the name of the worksheet is Data, the codename of the worksheet is Sheet6 and the position of the worksheet in the workbook is 1 (ie. it is the first worksheet).

In your VBA code, there are 3 ways you can get a reference to a worksheet:
1. Using its codename
2. Using Worksheets(WorksheetName)
3. Using Worksheets(WorksheetIndex)

So, in your code you can get a reference to the sheet by using
Code:
With Sheet6           'Using its codename
or
Code:
With Worksheets("Data")        'Using Worksheets(WorksheetName)
or
Code:
With Worksheets(1)          'Using Worksheets(WorksheetIndex)


You've used Option (1) which is great.
Reply With Quote
  #10  
Old 06-22-2012, 08:09 AM
gbaker gbaker is offline Sorting Challenge Windows 7 32bit Sorting Challenge Office 2010 32bit
Competent Performer
Sorting Challenge
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default

Thanks for all your help!!! Have a great weekend.
Gary
Reply With Quote
  #11  
Old 06-22-2012, 08:44 AM
gbaker gbaker is offline Sorting Challenge Windows 7 32bit Sorting Challenge Office 2010 32bit
Competent Performer
Sorting Challenge
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Sorting Challenge

Hi Colin,
I finalized the code to work on 4 vendors. I am getting an error at the end.
Compile Error:
Expected End With
Don't understand why I'm getting this error. Can you clarify. See code below.
Code:
Sub SortdataOnly()
With Sheet2
              'get rid of any null strings stored in the cells for Print
            With .Range("Cr2:DE3980")
                         .Value = .Value
             End With
            With .Sort
                     'set up the sort fields for Print on (data tab)
            With .SortFields
                      .Clear
            .Add Key:=Sheet2.Range("Cr2:Cr3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
           .Add Key:=Sheet2.Range("Cs2:Cs3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
                       'set up the rest of the sort and apply for Print
               .SetRange Sheet2.Range("Cr2:De3980")
              .Header = xlNo
              .MatchCase = False
              .Orientation = xlSortColumns
              .SortMethod = xlPinYin
              .Apply
         End With
With Sheet2
               'get rid of any null strings stored in the cells for BNY
         With .Range("Dq2:Eb3980")
                         .Value = .Value
             End With
            With .Sort
                     'set up the sort fields on BNY on (data tab)
            With .SortFields
                      .Clear
            .Add Key:=Sheet2.Range("Dq2:Dq3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
           .Add Key:=Sheet2.Range("Dr2:Dr3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
                       'set up the rest of the sort and apply for BNY
               .SetRange Sheet2.Range("Dq2:Eb3980")
              .Header = xlNo
              .MatchCase = False
              .Orientation = xlSortColumns
              .SortMethod = xlPinYin
              .Apply
         End With
With Sheet2
         'get rid of any null strings stored in the cells for Wilde
       With .Range("En2:Ey3980")
                         .Value = .Value
             End With
            With .Sort
                     'set up the sort fields on Wilde on (data tab)
            With .SortFields
                      .Clear
            .Add Key:=Sheet2.Range("En2:En3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
           .Add Key:=Sheet2.Range("Eo2:Eo3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
                       'set up the rest of the sort and apply for Wilde
               .SetRange Sheet2.Range("En2:Ey3980")
              .Header = xlNo
              .MatchCase = False
              .Orientation = xlSortColumns
              .SortMethod = xlPinYin
              .Apply
         End With
With Sheet2
         'get rid of any null strings stored in the cells for DS Graphics
         With .Range("Fm2:Fx3980")
                         .Value = .Value
             End With
            With .Sort
                     'set up the sort fields on DS Graphics on (data tab)
            With .SortFields
                      .Clear
            .Add Key:=Sheet2.Range("Fm2:Fm3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
           .Add Key:=Sheet2.Range("Fn2:Fn3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
                       'set up the rest of the sort and apply for DS Graphics
               .SetRange Sheet2.Range("Fm2:Fx3980")
              .Header = xlNo
              .MatchCase = False
              .Orientation = xlSortColumns
              .SortMethod = xlPinYin
              .Apply
             End With
    End With
 
End Sub
Reply With Quote
  #12  
Old 06-22-2012, 09:39 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Sorting Challenge Windows 7 32bit Sorting Challenge Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

When you use a With... End With block, you have to make sure you close out the block using an End With statement. Your code is missing a few of these End With statements. The easiest way to spot it is to indent your code so that the "With" and the "End With" line up.

Code:
Sub SortdataOnly()
 
    With Sheet2
 
        'get rid of any null strings stored in the cells for Print
        With .Range("Cr2:DE3980")
            .Value = .Value
        End With
 
        With .Sort
 
           'set up the sort fields for Print on (data tab)
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("Cr2:Cr3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Cs2:Cs3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
 
            'set up the rest of the sort and apply for Print
            .SetRange Sheet2.Range("Cr2:De3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 
    With Sheet2
 
        'get rid of any null strings stored in the cells for BNY
        With .Range("Dq2:Eb3980")
            .Value = .Value
        End With
 
        With .Sort
            'set up the sort fields on BNY on (data tab)
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("Dq2:Dq3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Dr2:Dr3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
             'set up the rest of the sort and apply for BNY
            .SetRange Sheet2.Range("Dq2:Eb3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 
    With Sheet2
 
        'get rid of any null strings stored in the cells for Wilde
        With .Range("En2:Ey3980")
            .Value = .Value
        End With
 
        With .Sort
            'set up the sort fields on Wilde on (data tab)
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("En2:En3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Eo2:Eo3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
           End With
 
            'set up the rest of the sort and apply for Wilde
            .SetRange Sheet2.Range("En2:Ey3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 
    With Sheet2
 
        'get rid of any null strings stored in the cells for DS Graphics
        With .Range("Fm2:Fx3980")
            .Value = .Value
        End With
 
        With .Sort
            'set up the sort fields on DS Graphics on (data tab)
 
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("Fm2:Fm3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Fn2:Fn3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
 
            'set up the rest of the sort and apply for DS Graphics
            .SetRange Sheet2.Range("Fm2:Fx3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 
End Sub
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting by certain criteria randenius Excel 2 06-11-2012 02:18 AM
Sorting Challenge Word Challenge jpotter2 Word 3 03-22-2011 02:07 PM
Challenge!! Need help though. (Conditional formatting) knuckles70 Excel 2 02-05-2010 12:24 PM
Sorting Challenge CHALLENGE! Issue sorting data containing relative references on a separate tab lax828 Excel 3 01-07-2010 11:28 AM
Challenge: Get Custom Form to Show up in Reader Pane in 2007 JohnGG Outlook 0 08-21-2009 05:44 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:00 AM.


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