Excel Vba Sort Range Error 1004
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about runtime error 1004 Stack Overflow the company Business Learn more about hiring developers or posting ads with
Excel Vba Error 1004 Sort Reference Not Valid
us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is excel vba error 1004 select method of range class failed a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up excel vba sort error 1004 up vote 0 down vote favorite i'm using this excel vba error 1004 method range of object _global failed code for sorting (checked for Excel 2010/2013): Worksheets("Tabelle4(1)").Activate ActiveSheet.Sort.SortFields.Add Key:=Range( _ "W2:W51"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal I loop over a sheet 70 times with different values and sort and export them as a pdf. Everything works fine, but after approximately 30 times I get an error 1004. If I start the loop at this point 30 again it works fine. The problem doesn't seem to do with the values.
Excel Vba Error 1004 Autofilter Method Of Range Class Failed
Is there a buffer inside of excel, which I've to clear from time to time? excel vba sorting buffer share|improve this question asked Apr 27 '15 at 13:14 user2405095 115 How do you pass the 70 values to this line? I'm not aware of any such buffer. Looks more like you are trying to sort with invalid parameters. Can you reproduce it? Does it stop at the same value each time? –nhee Apr 27 '15 at 13:19 I get the values from an SAP Add-In. 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. But it fails always between 30 and 35 rounds, at this point of coding. –user2405095 Apr 27 '15 at 13:27 I have to start excel new again, otherwise excel would stop with the same error again, even there are other values. –user2405095 Apr 27 '15 at 13:32 If I start excel again from the error point, I've no trouble. –user2405095 Apr 27 '15 at 13:34 How about this: is it possible you have more than one worksheet and keep jumping back and forth while the macro is running? Your macro
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site excel vba error 1004 delete method of range class failed About Us Learn more about Stack Overflow the company Business Learn more about excel vba error 1004 pastespecial method of range class failed hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join
Excel Vba Runtime Error 1004 Method Range Of Object _worksheet Failed
the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Excel VBA Run-Time Error http://stackoverflow.com/questions/29896890/excel-vba-sort-error-1004 1004 up vote 6 down vote favorite 1 I am using VBA for Excel 2010 and randomly receiving the following error: Run-time error '1004': "The sort reference is not valid. Make sure it's within the data you want to sort, and the first Sort By box isn't the same or blank." This is the code 'Sort the active rows With ActiveWorkbook.Worksheets("Product Backlog").Sort .SetRange Range("A4:F51") .Header http://stackoverflow.com/questions/8681990/excel-vba-run-time-error-1004 = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With excel-vba share|improve this question edited Jun 16 '13 at 12:15 Shadow Wizard 49.1k1385130 asked Dec 30 '11 at 17:07 Aaron 183127 1 The most likely cause is you are not within the required worksheet. Don't use ActiveWorkbook, use Sheets("xxxxx"). –Tony Dallimore Dec 30 '11 at 17:48 add a comment| 1 Answer 1 active oldest votes up vote 4 down vote accepted The sort by box is blank, that is your problem. I never have used a Sort object like your doing, but I can see that you have not defined a key, or a range to sort by, just the range to be sorted. A key should be defined such as Range("A4") or something. 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 .SortMethod = xlPinYin .SortFields.Add Key:=Range("A4:F51").Columns(1), SortOn:=xlSortOnValues, _ Order:=xlDescending, DataOption:=xlSortNormal .Apply End With I use the Sort function as follows: ActiveWorkbook.Worksheets("Product Backlog").Range("A4:F51").Sort _ Key1:= ActiveWorkbook.Worksheets("Product Backlog").Range("A4:F51").Columns(1), _ Header:= xlYes, _ Orientation:=xlSortColumns, _ MatchCase:=False, _ SortM
Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages Excel Web Pages | Produce Clean Efficient VBA Code http://www.ozgrid.com/forum/showthread.php?t=59162 Every Time | Build Automated Trading Models In Excel | Excel Web Pages | Excel Video Training Forum New Posts FAQ Calendar Forum Actions Mark Forums Read Quick Links Today's Posts What's New? Advanced Search Forum HELP FORUMS Excel General Sort Macro: Run-time Error 1004 Excel Training / Excel Dashboards Reports If this is your first visit, be sure error 1004 to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above. Click here to excel vba error view the relaunched Ozgrid newsletter. Results 1 to 5 of 5 Thread: Sort Macro: Run-time Error 1004 Thread Tools Show Printable Version Search Thread Advanced Search October 25th, 2006 #1 corkyo4 View Profile View Forum Posts Member Join Date 23rd April 2006 Posts 94 Sort Macro: Run-time Error 1004 Hi, I have created a macro which sorts 4 columns of data in asceding order numerically. I have assigned this macro to a button so that when the button is clicked, the columns of data are sorted. Unfortunately when the button is pressed i get a vba run-time error 1004. I havent a clue how to resolve this. I recorded the macro useing the excel recorder function as opposed to typing it out manually. The code created is given below. If i click debug when the error pops up, the code referring to column f:f is highlighted yellow. Can anyone help in stopping this error occuring. Thanks VB: Sub MacroAscending() ' ' MacroAscending Macro ' Macro recorded 25/10/2006 ' ' Columns("F:F").Select Range("A1:F185").Sort Key1:=Range("F1"), Order1:=xlAscending, Header:= _ xlGuess