Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > BOOK: Excel VBA 24-Hour Trainer 2nd edition
|
BOOK: Excel VBA 24-Hour Trainer 2nd edition
This is the forum to discuss the Wrox book Excel VBA 24-Hour Trainer 2nd Edition by Tom Urtis; ISBN: 978-1-118-99137-4
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel VBA 24-Hour Trainer 2nd edition section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 7th, 2015, 05:54 AM
Registered User
 
Join Date: Jul 2015
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Question How does 'sort' work while recording a macro ?

Hi,

Am a first time user of VBA and am still at chapter 2 of the book learning to use the Macro Recorder. I have been trying to work out the example at pg.16 where the macro code requires sorting by way of Region , items and count. I followed all the steps laid out in instructions in this example to record the macro. The Macro does work except for the sort. i.e. The count col formatting works , the new col creation works, the old col deletion works and bold formatting works. But the macro does not sort. Is this a standard issue or am I missing something here ?
 
Old July 10th, 2015, 08:17 PM
Wrox Author
 
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
Default

Hi, thank you for buying my book.

The instructions on page 17 explain how to sort the 3 fields. You can change the sequence of the sorting columns, or only have one column be sorted.

If you're still having a problem, please post back.

Tom
 
Old January 1st, 2016, 12:11 PM
Registered User
 
Join Date: Jan 2016
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am having the EXACT same problem and have spent countless hours trying to figure it out with no satisfaction!

1) The only thing the Instructions on page 17 say is: "Select Range A1:C13 and sort in Ascending order by Region Item and Count"

2) I perform the following actions while the macro record is active:

a) use mouse to select cells A1:C13
b) Click Sort, Sort by Region, Add Level, Sort by Item, Add Level, Sort by Count, Largest to Smallest

3) When I perform these actions, the table is sorted as intended

4) However, when I run the macro on a test table, the formatting works perfectly, but the data is unsorted!

Is there some kind of trick with using the sort function while recording a macro?
 
Old January 1st, 2016, 12:21 PM
Registered User
 
Join Date: Jan 2016
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Also ... tried it with sorting on a single variable (Region) ... still didn't work
 
Old January 1st, 2016, 01:55 PM
Wrox Author
 
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
Default

Hi, and thanks for buying the book.

Something is affecting the results you are getting (or not getting) that, based on your description, I cannot replicate.

Just now, I created this example table in A1:C3, with A1:C1 being header cells:

State Items Count
Texas Socks 33
Calif Shirt 86
Idaho Shirt 37
Calif Pants 83
Idaho Shirt 93
Idaho Shirt 60
Calif Pants 31
Texas Shirt 95
Idaho Socks 66
Texas Pants 33
Calif Shirt 86
Calif Socks 11

I used the macro recorder and got this code:

Sub Macro1()
Range("A1:C13").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("A2:A13") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("B2:B13") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("C2:C13") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:C13")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Then, I clicked the Undo button to restore the original data. I ran the recorded macro and it produced this sorted table, as expected:

State Items Count
Calif Pants 83
Calif Pants 31
Calif Shirt 86
Calif Shirt 86
Calif Socks 11
Idaho Shirt 93
Idaho Shirt 60
Idaho Shirt 37
Idaho Socks 66
Texas Pants 33
Texas Shirt 95
Texas Socks 33

Finally, I had made a copy of the original data and pasted it onto the sorted data in the original range, and tested the recorded macro again. Same result, sorted as I recorded.

Maybe I am missing something about what is being seen by yourself and the first poster on this thread. Or, just thinking out loud, is the workbook saved as a .xlsm (for macros to be executed, as opposed to .xlsx for code-less workbooks), or are your macro's security settings allowing for your macro to run.

In any case, please post back with what I am missing. Or if you like, you can email me directly with your attached workbook (my email address is in the "About the Author" section near the front of the book), and I'll be happy to take a look at what you are seeing.
 
Old January 1st, 2016, 06:29 PM
Registered User
 
Join Date: Jan 2016
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Okay ... so I think I figured out what the problem is/was ...

I haven't reached the part of the text on reading/debugging the code yet, but here is what I figured out.

Within the same workbook, I had used one worksheet (Sheet 1)to develop (record) the macro and another worksheet (Sheet2) to test the macro on the original data. [So I wouldn't have to re-enter or re-paste the data every time I tested the macro]

Apparently inserting and deleting columns, cutting and pasting & formatting are "transportable" from sheet to sheet within the workbook. That is why these functions worked.

However, the sorting macro clearly requires the sheet to be sorted to be identified. That portion of the macro (and only that portion) failed to work when I ran the macro on Sheet 2.

I'm guessing at some point in the course I will learn how to adjust the code so that the sort feature is "transportable" from one Sheet to another within the same workbook. Not sure how to do this with "record macro" yet ... perhaps selecting the active sheet while recording the macro will do it.

Thanks for your help
 
Old January 1st, 2016, 07:56 PM
Wrox Author
 
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
Default

As you will see later in the book, where VBA is concerned, the parent object (in this case, the worksheet where the sort should take place) must be defined. VBA needs to know which worksheet you want to affect, in case the worksheet you are currently on (that is, the active sheet), is not the worksheet where the data needs to be sorted.

The code to take that worry away from you, where range A1:C13 is sorted regardless of the current worksheet, assuming your workbook with that range to be sorted is the active workbook (you'll see how to deal with multiple workbooks, too) can be like this:

Sub Macro2()
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("A2:A13") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("B2:B13") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("C2:C13") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:C13")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

And that macro can be further reduced to this. Notice the period (or dot) characters in front of ranges, inside what are called With structures, as you will see in the book how to construct. Here's an advance look that tested fine for me:

Sub Macro3()
With Worksheets("Sheet1")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("A2:A13"), SortOn:=xlSortOnValues, Order:=xlAscending
.Sort.SortFields.Add Key:=.Range("B2:B13"), SortOn:=xlSortOnValues, Order:=xlAscending
.Sort.SortFields.Add Key:=.Range("C2:C13"), SortOn:=xlSortOnValues, Order:=xlDescending

With .Sort
.SetRange Range("A1:C13")
.Header = xlYes
.Apply
End With

End With
End Sub





Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic sort key components on xsl:perform-sort apollo13 XSLT 5 August 10th, 2012 03:26 PM
Use a macro to sort a user selected range rmccafferty Excel VBA 3 April 26th, 2011 04:32 PM
how to sort cross tab.sort based on row total joxa83 Crystal Reports 7 March 2nd, 2006 09:12 AM
Unable to sort using xsl sort command sly_jimmy_boy XSLT 3 June 17th, 2005 05:15 AM
sort by last post time doesn't work? amarelo Forum and Wrox.com Feedback 1 June 6th, 2004 09:21 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.