Excel Vba Sort 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 Stack Overflow the company Business Learn more about hiring
Runtime Error 1004
developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question excel vba error 1004 sort reference not valid x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; excel vba error 1004 application-defined or object-defined error it only takes a minute: Sign up excel vba sort error 1004 up vote 0 down vote favorite i'm using this 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
Excel Vba Error 1004 Select Method Of Range Class Failed
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. 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
Excel Vba Error 1004 Document Not Saved
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 references ActiveWorksheet which could be changing while the macro is running. After restarting Excel it is working which could be because the other sheets were closed but not reopened after you restarted Excel. –nhee Apr 27 '15 at 13:37 | show 4 more comments 1 Answer 1 active oldest votes up vote 0 down vote ac
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss excel vba error 1004 method range of object _global failed the workings and policies of this site About Us Learn more
Excel Vba Error 1004 Autofilter Method Of Range Class Failed
about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow excel vba error 1004 delete method of range class failed Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each http://stackoverflow.com/questions/29896890/excel-vba-sort-error-1004 other. Join them; it only takes a minute: Sign up Excel VBA Run-Time Error 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 http://stackoverflow.com/questions/8681990/excel-vba-run-time-error-1004 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 = 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
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings http://stackoverflow.com/questions/26037735/excel-vba-sort-error-1004-on-apply and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation http://www.vbforums.com/showthread.php?504219-Excel-VBA-Sort-Help-Error-1004 Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it error 1004 only takes a minute: Sign up Excel VBA Sort, error 1004 on .Apply up vote 0 down vote favorite I'm having issues using sort in VBA. I saw this thread for which the answers don't work for me: Sort in VBA I believe there is a maximum nr of records on which you can sort. Is that correct? I want to excel vba error sort on 4 criteria in a sheet/table with 188,000 records. I always get an error on the .Apply statement: "run-time error '1004': application-defined or object-defined error" Below my code: Sub Sort_Table() Dim sht As Worksheet Set sht = ActiveWorkbook.Worksheets("Sheet1") sht.Activate With sht.ListObjects("Table1").Sort .SortFields.Clear .SortFields.Add Key:=Range("Table1[Date]"), SortOn:=xlSortOnValues, Order:=xlAscending ', DataOption:=xlSortNormal .SortFields.Add Key:=Range("Table1[Country Code]"), SortOn:=xlSortOnValues, Order:=xlAscending ', DataOption:=xlSortNormal .SortFields.Add Key:=Range("Table1[Rating]"), SortOn:=xlSortOnValues, Order:=xlAscending ', DataOption:=xlSortNormal .SortFields.Add Key:=Range("Table1[Segment]"), SortOn:=xlSortOnValues, Order:=xlAscending ', DataOption:=xlSortNormal .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub excel vba sorting excel-vba share|improve this question edited Sep 25 '14 at 13:40 smagnan 788523 asked Sep 25 '14 at 11:41 Wouter Duerinck 11 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote Potential problem Actually maybe you shouldn't the Apply inside the With since your object is really updated after the end of the With block. For instance here your parameters (.SortFields etc ...) are not yet set when you use Apply. I'm not 100% sure because I doesn't have EXCEL to test right now, and it se
this is your first visit, be sure 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. Results 1 to 12 of 12 Thread: Excel VBA Sort Help Error 1004 Tweet Thread Tools Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Jan 11th, 2008,04:48 AM #1 jlmcintyre View Profile View Forum Posts Thread Starter New Member Join Date Jan 2008 Posts 4 Excel VBA Sort Help Error 1004 Hi all When i run this code i get a Run-time Error '1004': Application - Defined or Object Defined Error. Selection.Sort Key1:=Range(Selected_Range), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal For info: Selected Range = "A2" The total sort area is "A2:T23" Can anyone tell me why this is happening? Thanks JLM Last edited by jlmcintyre; Jan 11th, 2008 at 05:04 AM. Reply With Quote Jan 11th, 2008,05:06 AM #2 westconn1 View Profile View Forum Posts PowerPoster Join Date Dec 2004 Posts 21,925 Re: Excel VBA Sort Help Error 1004 i tried your code, i had problem with dataoption1, maybe it depends on excel version code works when i deleted the last parameter i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part come back and mark your original post as resolved if your problem is fixed pete Reply With Quote Jan 11th, 2008,05:12 AM #3 jlmcintyre View Profile View Forum Posts Thread Starter New Member Join Date Jan 2008 Posts 4 Re: Excel VBA Sort Help Error 1004 I removed the last parameter but still got the same error. Reply With Quote Jan 11th, 2008,05:18 AM #4 jlmcintyre View Profile View Forum Posts Thread Starter New Member Join Date Jan 2008 Posts 4 Re: Excel VBA Sort Help Error 1004 not sure if this will help any vb Code: Selected_Range = "A" & Mid(StartPosition, 4) Selected_Range_End = "T" & Mid(EndPosition, 4) 'Add sort in here ActiveSheet.Range(Selected_Range & ":" & Selected_Range_End).Select 'sort data ascending Selection.Sort Key1:=Range(Selected_Range), Order1:=x