Using Sort Function In Macro Crashes
Jun 3, 2009
I have a report I produce where I take an XML file, sort it and run some simple formulas then sort another way and run a few more formulas then update a pivot table. The XML files I'm using have the same headers but vary in length. When I set the macro up for a particular XML file everything works fine. When I import another one (different size) I get the error message:
Run-time error ‘1004’:
Sort method of Range class failed
When I look at the code I see that the range is fixed to the XML file I used when setting the macro up. Is there a way to auto-select the table in the macro so I can run the same macro on any size XML?
View 7 Replies
ADVERTISEMENT
Oct 3, 2009
I have a column of data that is an 850,000+ list of phone numbers. I need to peridoically insert another 10,000+ at a time and see if there are duplicates. Using conditional formatting to highlight the duplicates with a colored cell, and then sorting those records to the top is what I want to do, but it's too much for Excel to handle, and it hangs. It looks like I need to break up the 850k records into smaller chunks. Anyone know what an average pc with Excel 07 can handle? It's a 2.8ghz w/2gb ram.
View 6 Replies
View Related
May 20, 2008
I'm looking for a macro that can sort emails by 2 very distinct ways, with 1 special find function as well. Not too much eh? ........
View 9 Replies
View Related
Jan 25, 2010
When I run a group of macros for some reason after the marcro runs and I hit F2 excel crashes.
I have figured out that it is one of the marcros that makes the excel crash. It is only when I run the solver application. The code is below. Any thoughts why hitting F2 after running this marcro would crash the machine?
View 10 Replies
View Related
Feb 15, 2008
I have created a macro in xl2000 which was working fine - although through corporate upgrades my machine has now been upgraded with xl2003 and the macro runs for a bit and then crashes on the code below, although it only highlights teh 'rnum'
rnum = ActiveCell.Row
Is there a difference between xl2000 & xl2003?
View 9 Replies
View Related
Dec 19, 2009
I have the following strange problem. A simple macro 'ActiveWorkbook.Close' causes excel to crash if I use it with a button on my sheet (a form). When I run the macro via developer > macros > run
the macro runs as expected. Even if a run the macro with alerts (thanks to jproffer) the problem persist.
View 8 Replies
View Related
Mar 20, 2014
I have developed a model for a client which keeps crashing after being used a few times. The crash is major! Excel shuts down and asks to send an error report.
This can happen after being run anywhere from 3 to 20 times. (Clicking a POST button which transfers items to a "database" type sheet).
I developed the model on a PC using Excel 2010. When this was loaded on a Mac, the user tells me that it happened periodically, and I have seen is occasionally on my PC as well.
However, the workbook has now been loaded on a server, with users across the state of Victoria accessing via Sharepoint - most using Excel 2007. Now it is happening very frequently.
I don't think the macro is at fault, it doesn't go into debug. Excel just seems to GIVE UP.
Have you seen this kind of thing before? Is there a way of "flushing buffers" or something so that Excel can keep working?
I am reluctant to post the model because it is full of personnel details, and is 2.5mb.
View 6 Replies
View Related
Jul 26, 2009
I have comment blocked the code that causes Excel to crash.
View 4 Replies
View Related
Dec 20, 2013
The macro does numerous things, including deleting the sheet that was active when it is called. If the sheet is not deleted, there is no crash. Otherwise, Excel crashes as soon as the VBA interpreter hits "Exit Sub". The Excel message is simply "Excel Stopped Working". The macro also saves the workbook before finishing, and the saved workbook opens just fine, but it's unacceptable to have to kill Excel and restart it every time (I am not the main user for this spreadsheet). I'm using Excel 2010 on Windows 7.
View 7 Replies
View Related
Feb 10, 2006
I'm just wondering if any of you can lend some help on a bug i'm encountering. When I press a button on my worksheet to call a macro that brings up a userform, the excel will crash, and give that "do you want to report this error", close down excel, and reopen on a blank worksheet. Strange thing is, this error is avoided if I save the workbook upon opening it, and then call that macro.
The workbook does not read data from external sources, so i dont think is that problem.
Has anyone encountered this?
View 9 Replies
View Related
Feb 28, 2012
I have a VBA macro for Excel 2007 below that loops through a workbook and deletes a picture (shape) in a range at the top of each worksheet.
The macro works fine until a cell which contains a seemingly unrelated data validation list on Sheets(1) is changed. The macro then repeatedly trips up with a 'Run-time error 1004 - Application defined or object defined error'.
The cell with the data validation is outside of the range in which the shapes are deleted and does not set any of the variables in the macro.
Sub DeleteLogos()
Dim Count As Integer
Dim NumberOfWorksheets As Integer
Dim Logo As Shape
Dim LogoZone As Range
NumberOfWorksheets = Worksheets.Count
For Count = 1 To NumberOfWorksheets
With Sheets(Count)
[code].....
View 4 Replies
View Related
Dec 13, 2007
I have a VBA macro that goes through a decently complex system to delete something like 70 old tabs, create new ones, link data from a main page to these tabs, and create 3 plots per tab. When running the script, it will go through something like 50 of the tabs, and then receive an error on something rather trivial that has been done many times before successfully in this macro (like changing the font size on one of the axes on one of the graphs). I tried an On Error Resume Next, and sure enough it ignores the error, but it then does some sort of crazy loop starting over recreating all the tabs incorrectly, and then excel just shuts down. So, that's no good.
Anyway, I can comment out the change of the font size, but it still has the same issue on some other trivial thing. It seems like the system just runs out of room or needs a cache cleared or something. I'm not sure.
View 9 Replies
View Related
Nov 5, 2009
I am looking for a macro to sort my colums in ascending order the range i need sorted is from A7:F107, it will be sorted with the numbers in the A column ie 1 - 107. sometimes different numbers get added and it has to be sorted in number sequence.
If possible i would also like a macro to protect this range after the sort,
View 3 Replies
View Related
Jul 13, 2007
I have a table with a number of columns of data.
Column A cells have a text string that has a number at the start of the line.
The table is constantly adding additional results (rows) every 5 minutes or so.
Can I dynamically identify rows via the number at the start of column A and copy all cells in that row to a different worksheet?
View 14 Replies
View Related
Oct 10, 2009
-Problem on all workbook tabs
-Problem persists when port of worksheet data is copied to new workbook.
-New workbook with newly typed data will allow sort.
-Can't get my existing workbook sort to work.
View 3 Replies
View Related
Jan 8, 2010
I have a vertical list where every other cell is coloured grey. The values contained in the list are regularly sorted into alphabetical order with new values being added and other being removed.
Is there a way to keep the cells alternate grey & white after I have sorted them? Currently, when I sort, if a cell is coloured grey and is moved down one cell the colour is moved and not just the values.
View 2 Replies
View Related
Jul 1, 2014
I have data in table which look like this
A
B
1
NAME
AV
2
SONA
18
[Code] .........
I Want a formula that will enable excel to give me the number of students with Av.
>= 18
>= 15 but not > than 18
>= 10 but not > 15
View 1 Replies
View Related
Feb 5, 2007
In the RANK() function I can rank a value depending on one set of values (one condition), but when you SORT data you can choose a second (and a third) condition.
Is there a way to RANK with two sets of values (two conditions)?
View 9 Replies
View Related
Oct 1, 2008
I have a string that I need to sort.
Below code works beautifully but doesn't sort numbers, any advice to get this to sort numbers as well?
Function Alphabetize(ByVal sText As String) As String
Dim sWords() As String, sTemp As String
Dim i As Long, j As Long, n As Long
'-- clean up text
For i = 1 To Len(sText)
Select Case Mid$(sText, i, 1)
Case " ", "a" To "z", "A" To "Z"
Case Else: Mid$(sText, i, 1) = " "
End Select
Next
'-- remove leading and trailing spaces....................
View 9 Replies
View Related
Feb 4, 2013
How can I sort two columns of function results using the result value? Excel is trying to sort on the function in the cells rather than the results themselves.
View 9 Replies
View Related
Dec 17, 2012
I have 3 columns of data: col. A = name (random order), col. B = Net #, Col. C = Gross #.
I am using =LARGE(C$1:C$4466,ROWS($D$1:D1) to Automatically sort col C in decending order.
I would Like to do another decending sort but only the values in Col C that corespond to a particular name in Col A. Can I imbed a index match function combination within the large function to do this?
Name
Net
Gross
All Sort
SortA
SortB
SortC
SortD
A
508
-200.129
101.685
[Code] ..........
View 6 Replies
View Related
Oct 7, 2008
I am working on a simple employee list with names, phone numbers, employee ID numbers, etc. I have created a VBA form to make new entries or change existing entries. The form has 'next', 'previous', and 'close' command buttons that work well, but I would like to have my data sort by employee name after any of the buttons are clicked.
My data is in worksheet "Employees" with employee names in column A beginning on row 2. Here is what I have entered for the 'Next' command button:
View 5 Replies
View Related
Nov 10, 2009
I'm currently working on a problem that I'm having with Hyperlinks. Basically I want to copy the destination data that the hyperlink points to onto another sheet.
The main problem I'm having here is that I cant seem to find a suitable method to do this....I had the idea of using the hyperlink.follow function and then copying the data once I'm there and then moving back to the source hyperlink. My only problem with that is that I dont know how to find the cell address that the hyperlink is in, once I have found that hyperlink.
Therefore my question is two-fold:
- Is there a way to copy the destination data using some sort of hyperlink function?
- If not, is there a way to find the cell address within a worksheet, once a hyperlink is found on that worksheet?
I've attached the spreadsheet below to give a better idea of what Im doing, along with the "findHyperlinks" macro,within that workbook, that I am currently working on.
View 9 Replies
View Related
Sep 9, 2012
I have following data to sort/filter
Sector
Flt no
origin
[Code]...
Is this possible with excel functions?
View 1 Replies
View Related
Dec 13, 2012
Essentially i get a Runtime 5 error on the bold bit of code....
I am trying to define a range .... by using thexlUp function find the last row ( which works ) i then pass this variable into the sort code and get the error its probably very simple to fix, all it does i sort columnA but finds the last cell .... instead of the whole column,,,,
Sub Macro7()
'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+e
NumberOfRow = Sheets("Sheet1").Range("A3000").End(xlUp).Row
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Sheets("Sheet1").Range("A2", Cells(NumberOfRow)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
[Code] .......
View 2 Replies
View Related
May 7, 2013
I need to create a custom function that can do the following without modifying the selected range. If I have data in a row, I need a function that can give me the following result:
Original unsorted data:
79
45
23
56
1
The custom formula will not modify the selected range, and after saving it or copying somewhere and sorting it, it will provide the following result: if N or count(range)=5 , and p=1, average(second and third smallest values of the range)=34; and if N=4 (lets say that we errase the number 1 data point in the range), average(third and fourth smallest values of the range)=67.5 So it is basically a sort and then getting the averages of specified row numbers depending on N or count(range) Here is what I have.
I'm missing how to create a variable with the sorted range and how to use an index to refer to the rows I want to average:
Function CSORT(r, p)
Application.Volatile
'rs or something similar that will contain the sorted range r
Dim rs As Range
'Sort rs (The following line one doesn't work, so I need one that does)
rs = Application.Range(r).Sort
Dim p As Integer
[code]....
View 1 Replies
View Related
Mar 3, 2008
I'm trying to do is pass the contents of a listbox to a function to sort it into alphabeticalorder. I get a error saying object required
Sub PopulateAll()
Dim lgLastRow As Long
Dim lgRowCounter As Long
Dim intCounter As Integer
Dim olb As MSForms.ListBox
For intCounter = 0 To UBound(vAllEnv)
Me.lstAll.AddItem (vAllEnv(intCounter))
Next intCounter
Set olb = frmOptions.lstAll.........................
View 9 Replies
View Related
Jun 5, 2013
what I do with excel: I have an excel sheet that has over 18,000 rows in it. Since it would be a nightmare to scroll around to find what I want, I use the Custom Sort and Filter options under Editing>Sort&Filter. So for example, I can omit 17,800 rows using a specific setting so that I can work with a more reasonable 200 rows. Moreover, the 200 rows comes from all over the spreadsheet. Meaning their row numbers are not always consecutive.
Here's the problem: Whenever I try to copy anything from this "edited or filtered" excel sheet, the resulting paste is not an exact copy. Excel perfectly copies the first rows up until the point where the row numbers ceases to be consecutive. So, the copy function messes up somehow when the data being copied comes from a different section of the original 18,000 rows.
In case this isn't clear enough...
Let's say that the original file has rows 1,2,3,4,5,6,7,8,9,10
Once I filter/custom sort, I see rows 1,2,3,8,9,10
When I try to copy/paste 2,3,8,9, excel copies 2,3, but messes up the rest of the 8,9. And I end up with a totally useless copy that's generally shorter than it's supposed to be.
View 6 Replies
View Related
Jul 21, 2007
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
Windows("estimate sheet one.xls").Activate
View 2 Replies
View Related
Apr 14, 2009
I have the following code which filters data in a sheet, and loads the numbers into textboxes on a userform. There are two multipage controls, each with several textboxes on them. I have commented out several lines because if I leave any of them in, it crashes excel.
It will work once. But if I close the workbook, re-open it, and run the code again, it crashes.
The thing I find really interesting is that all the 'problem lines' were added in as part of a retro-fit. The person I created this workbook for needed one more column of data. I followed the same programming pattern as what I used on the other, but only these ones seem to cause problems. Any thoughts?
NOTE: The message boxes were just to help me figure out which lines of code were causing the crash. ....
View 9 Replies
View Related
