excel vba sort runtime error 1004 Dexter City Ohio

Address 501 Washington St, Marietta, OH 45750
Phone (740) 568-9440
Website Link http://www.bestonecomputers.com

excel vba sort runtime error 1004 Dexter City, Ohio

I looked it up, it should have .sortfields.add (range) in it, such as: 'Sort the active rows With ActiveWorkbook.Worksheets("Product Backlog").Sort .SetRange Range("A4:F51") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom Style Default Style Contact Us Help Home Top RSS Terms and Rules Copyright © TechGuy, Inc. It doesn't seems to be to do with the parameters. "Does it stop at the same value each time?" No, there are different values. However, it is now working as expected and that is what is important.

The fact that the all the rows are selected forses the error. Using either form of Evaluate will mostly produce correct results but because of the limitations of Evaluate it is unwise (and inefficient) to use it unless it is required. Share it with others Twitter Linked In Google Reddit StumbleUpon Posting Permissions You may not post new threads You may not post replies You may not post attachments You may not All times are GMT -5.

Meaning of S. This site is completely free -- paid for by advertisers and donations. I have created a form that creates the spreadsheet. Meaning of S.

Hope this provides a full explanation.Regards, OssieMac Saturday, January 08, 2011 9:54 PM Reply | Quote 0 Sign in to vote [] is a shortcut for Application.Evaluate - this is a Thanks. Attached Files: maria33-Test.xlsm File size: 17.5 KB Views: 47 Mobility.xlsx File size: 15.1 KB Views: 44 Keebellah, Jun 2, 2014 #7 mariaa33 Thread Starter Joined: Aug 7, 2008 Messages: 164 Hans, Therefore correct syntax using the variables is: ws.Sort.SetRange [rs] 'In VBA the square brackets not essential.

That is Cool! This will make if much easier to test. rs.columns(1) is the first col of the usedrange, that must NOT be the entire column. If you assigned the ws.Sort.SetRange without using any variables for the worksheet and range it would be like the following: ThisWorkbook.Worksheets(1) _ .Sort.SetRange ThisWorkbook.Worksheets(1).Range("A1:F27") To include Range in the syntax again

The VBA code is located in the module section. Actually in your example Range("a1",rs) will only work correctly if your used range (the range you want to sort) already starts in A1. I have tried two different sort syntaxes with the same result: 'xlApp.Selection.Sort Key1:=Range("H7"), _ ' Order1:=xlDescending, _ ' Key2:=Range("A7"), _ ' Order2:=xlAscending, _ ' Header:=xlNo, _ ' OrderCustom:=1, _ ' MatchCase:=False, Set ws = ThisWorkbook.Worksheets(1) Set rs = ws.Range("A1:F27") Now rs can be used in lieu of all that is to the right of the = sign in the 2nd line of

I tend to when using the Cells property as arguments for the Range property. Note that it includes the keyword Range. Reply With Quote Jan 12th, 2008,02:21 PM #11 anhn View Profile View Forum Posts Head Hunted Join Date Aug 2007 Location Australia Posts 3,669 Re: Excel VBA Sort Help Error 1004 Environment: WIN XP Pro 2002 Excel 2007 12.0.6425.1000 SP 3 VB 6.5 Version 1053 VBS retail 6.5.1053 Regards chris Edited by Chris227 Friday, January 14, 2011 12:47 AM for correctness its

Friday, January 07, 2011 10:32 PM Reply | Quote 0 Sign in to vote Using columns select the enire column while the [rs] only selected the number of rows the you Good Term For "Mild" Error (Software) Why is the spacesuit design so strange in Sunshine? If you limitthe code when sorting to $A1:$AM200 the code runs without a hitch I just tested it. How would you help a snapping turtle cross the road?

more hot questions question feed lang-vb about us tour help blog chat data legal privacy policy work here advertising info mobile contact us feedback Technology Life / Arts Culture / Recreation need book id, written before 1996, it's about a teleport company that sends students learning to become colonists to another world How is the Heartbleed exploit even possible? more hot questions question feed lang-vb about us tour help blog chat data legal privacy policy work here advertising info mobile contact us feedback Technology Life / Arts Culture / Recreation sorry The error is not is Westconn's code.

To start viewing messages, select the forum that you want to visit from the selection below. Share it with others Like this thread? Can anyone suggest a reason why or a solution to this problem? Athttp://msdn.microsoft.com/en-us/library/aa139976(v=office.10).aspxone finds: "You can also refer to a range by enclosing anA1style range reference or a name in square brackets, which is a shortcut form of theEvaluatemethod of theApplicationobject.

Last edited by gnesterenko; Jul 6th, 2009 at 09:09 AM. Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Dec 19th, 2003,11:36 AM #4 alcorjr Board Regular Join Date Dec 2002 Location Costa Rica Posts 416 Re: Sorting Sub Subbing() Dim LastCell as String Range("A2").End(xlDown).Select LastCell = Selection.Offset(0, 13).Address Worksheets("Sheet1").Activate With ActiveWorkbook.Worksheets("Sheet1").Sort .SortFields.Clear .SetRange Range("A2", LastCell) .SortFields.Add Key:=ActiveWorkbook.Worksheets("Sheet1").Range("R2"), _ SortOn:=xlSortOnValues, Order:=xlAscending .SortFields.Add Key:=ActiveWorkbook.Worksheets("Sheet1").Range("S2"), _ SortOn:=xlSortOnValues, Order:=xlAscending .SortFields.Add Key:=ActiveWorkbook.Worksheets("Sheet1").Range("D2"), _ Excel - Tips and Solutions for Excel Privacy Statement Terms of Service Top All times are GMT -4.

Is there a buffer inside of excel, which I've to clear from time to time? I have another macro that uses .Apply and doesn't error. Ozgrid Retains the Rights to ALL Posts and Threads Register Help Remember Me? If I start the loop at this point 30 again it works fine.

Range(rs, rs) will also work, therefore you do not need a literal to make a Union work. What kind of bicycle clamps are these? Powered by vBulletin Version 4.2.3 Copyright © 2016 vBulletin Solutions, Inc. I tried this ws.Sort.SetRange rs and it worked correctly in Excel 2007Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/ Saturday, January 08, 2011 3:34 PM Reply | Quote 0 Sign in

I am trying to use the VBA code below to sort a worksheet of variable size by three different levels. In the United States is racial, ethnic, or national preference an acceptable hiring practice for departments or companies in some situations? If you don't like Google AdSense in the posts, register or log in above. Share it with others Like this thread?

I think it fails because an object is expected and rs is just a reference to an object. the error is somewhere else. Sunday, January 09, 2011 4:03 PM Reply | Quote 0 Sign in to vote Charles I tried to find out why [rs] will produce incorrect results under the To start viewing messages, select the forum that you want to visit from the selection below.