VBA Code To Sort Data - Run-time Error When No Data To Sort
Mar 4, 2010
I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.
Sub SortMeetings()
Dim iCTR As Integer
Dim yCTR As Integer
Dim zCTR As Integer
zCTR = 11
For iCTR = 12 To 23
For yCTR = 1 To 10
If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then
Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value
zCTR = zCTR + 1
End If
Next yCTR
Next iCTR
Range("AA11:AA" & zCTR).Select
Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
This piece of code runs perfectly on its own but when called at the end of another code it fails and I haven't a clue why. The reference wsTmp is dim'd globally and defined in the main component where it is simply - worksheets("somename")
Run-time error '1004':
The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.
I have a set of Data and I would like to order the data based on a column which is G and contains Dates. I would like to order the data Desc order but this must be done in VBA as its a monthly report and needs to be scheduled.
I am working on a set of data that has a two week time period and specific times of events throughout each day. The dates and times are in separate columns and the time is in 24 hour format HH:MM. I am trying to count the number of occurrences in tenths of the hour or six minute increments, so 1-6 would equal .1 - 7-12 would equal .2 and so on - I really would remove the decimal and just express the value as 1-10. I am then just trying to do a count of the number of occurences for each hour and each day to see where the occurences are grouping. I will then graph this result to see where the clusters occur
I have a worksheet that is automatically updated based on actions in other sheets. There are 10 columns of data (A-J) Headings are on row 6 and data starts on row 8. I have created 3 buttons to sort worksheet by client column (B), year-end column (E) and to bring it to original order (by numbers in column A). I have created the following codes that I have assigned to each button however it does not work.
Sub Macroclient() Columns("A:J").Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub
I used the vba recorder to get the code and didn't change one thing, and now I am getting an error when i click on the command button to execute the code.
error
Run time error 1004 The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By Box isn't the same or blank.
Row 1 = column headings
row 2 is the first set of data, *****, however, most of the data in the column is blank. There are only 2 options, (Yes) or blank, and I am trying to sort the sheet so all the (Yes) appear at the top
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.
I encounter a runtime error '1004' if the "Invoices" sheet is not selected when I run this procedure. The last line of the code is one which is highlighted when I debug.
Sub ProcessData() Dim aiOldRows() As Integer, aiNewRows() As Integer ' Arrays of new/old rows Dim rngRaw As Range 'Data entry area Dim rngInvoices As Range 'Invoices range Dim rngOpenPoint As Range 'Top-left corner of data entry area Set rngOpenPoint = ThisWorkbook.Worksheets("Data Entry").Range("a3") Set rngRaw = Range(rngOpenPoint, rngOpenPoint.End(xlDown).End(xlToRight)) FindNew aiOldRows, aiNewRows, rngRaw InvoiceSequence aiOldRows, rngRaw Set rngInvoices = Range(ThisWorkbook.Worksheets("Invoices").Range("A2"), _ ThisWorkbook.Worksheets("Invoices").Range("A3").End(xlDown).End(xlToRight)) rngInvoices.Sort Key1:=Range("M2"), Order1:=xlAscending End Sub
You'll notice that there are two other procedures (FindNew & InvoiceSequence) being called by this procedure. I don't think those have anything to do with the error, but I can provide the code for those if needed. Oh, and one other secondary question. To declare the ranges rngRaw & rngInvoices I pick the top-left cell of the data and then do:.....................
I have 2 columns on sheet 1 as below. I need a code to put all the data in column B vertically on sheet 2 as the result shows. Please note all cells data will be off various lengths all seperated by a comma.
I have an excel WS1 set up as DB; I want to keep this sheet for data revision. WS2,3, 4, & 5 will be data that is filtered and sorted, using WS1 as source so I want to auto copy the WS1 data. Can I just auto copy WS1 (how do I do that?) then filter and sort in each WS?
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String With CreateObject("VBScript.RegExp") .Pattern = "reg_d+(_)+d+//d" GetString = .execute(txt)(0) End With End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
In the attached spreadsheet I track the performance of my team. I enter the AHT for my team in the work sheet named "AHT Summary".I keep updating this data every month. automatically sort the updated data and rank the agents based on their average AHT. The person with the lowest AHT should be ranked 1. Column is highlighed in green for your reference. Based on this ranking the work sheet named "Ranking" should be updated automatically. Since I am taking a weighted average for all agents, the one who gets the lowest AHT should be ranked 1st . In the ranking work sheet the agent with ranking 1 should be given 100, the second highest ranked person should get 98,third 96,fourth 94 etc.
I have Sheet1 "MASTER" and Sheet2 "Area1" and Sheet3 "Area2" etc...
My MASTER sheet has a list of employee names and the areas they work in. I have employees working in different areas, and I want to pull a list of employee names from the MASTER working in Area1 (sorted on the MASTER sheet) to column A on Sheet2, then pull a list of employee names working in Area2 to column A on Sheet3, etc...
I want it to do this in such a way that if I add an employee to Area1 on the MASTER data, it will populate that employee in the Area1 Sheet.
So basically, I'm looking at one column on the MASTER sheet to see if the area matches. Then looking at another column on the MASTER sheet to get the name. Then taking that name and transposing it to a new sheet corresponding to the area they work in.
I've attached a sample sheet. I want Column A in the Area1 sheet to reflect all names that show up on the Area1 LIST on the MASTER sheet, and nothing else. I'm using VLOOKUP to pull the rest of the data from the MASTER table.
There is a new sample workbook up now. Couldn't update it sooner due to site outage. I've removed irrelevant data to improve readability and focus on what I'm trying to achieve. Again, the main issue is scraping column E from the Master, and populating a list of all employees who match certain values in Column E on the Master in Column A of the other tabs.
In the attached version the "Print Version" tab sorts the values entered into the "Log Entries" tab. When entries are deleted from the "Log Entries" tab they are converted into 'blank' entries and moved to the end in the "Print Version" tab.
However, in earlier versions the "IFERROR" does not appear to work, therefore any blank entries are shown as "#NUM!", which is kind of annoying. If there any way around this? The forumla is shown below. =IFERROR(SMALL('Log Entries'!$A$1:$E$47,ROW('Log Entries'!A1)+COLUMNS('Log Entries'!$A$1:A1)*46-46),"")
to use a Macro in order to batch process a set of data that will have consistent columns, but may have additional rows.
I have attached the file, three tabs.
1st - Sch of Inv(2): The intended goal The data is sorted first by Georgraphic Location (A), then Strategy (C), then Substrategy (E). Column F should be the same formula that is currently there, but updated for column I once the information is pulled (I""/$F$29*100). The *100 is to only display the % symbol once. If there's a better way to calculate percentages and only show the symbol on the first one, that would also help. Column I is the place to calculate the data from Tab 2. Column K is your check figures, which are within $5 (due to a miskey probably, not important). Cell F29 will be a manually hard-coded number on each sheet (which would be entered before the Macro is initiated).
2nd - Cost Query: The data source sheet 6 columns, 5 have relevant data. Manager shortname is the 'primary key', but won't need to be displayed anywhere. Region-Strategy Code-Substrategy Name correspond to (A), (C) and (E) on Tab 1. The goal is to sum by each of these factors, then display the one sum on Tab 1. The pull will include column F, but it has no actual value. I'd prefer to be able to just keep it on Tab 2.
3rd - Pivot of Cost Query: The step inbetween? Here is the proof that the data can be easily pivoted to provide all of the values, but I'm having trouble figuring out how to quickly move those values into the predetermined format of Tab 1.
Questions: Is there something simple that I'm missing to move the data from the pivot into the predetermined format?
How can I use a macro to take the data on Tab 2, move it into a pivot on Tab 3, then move that data to the format (column I) in Tab 1? Is this process easier without the pivot step in the middle?
edit the below code? Having problem with the underlined portion. It is always showing the following error message - "Run-time error '1004' -Application-defined or object-defined error".
I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending
Im using a pivot with 2 rows entrees and 1 column entree. I would like to sort my data from the biggest value to the smallest. It works for the first group, but when i sort the second the first change aswell and mess with my data.
Excel file. The file has two tabs: 'Input' and 'Master'. The 'Input' tab is for users to input any new records, and the 'Master' tab is to retrieve data from the 'Input' tab. Given that some users may want to insert a row in between (rather than add at the bottom).
I used arrays in the 'Master' tab such as:
={IF(ISERROR(1/Input!E2:E250),"",Input!E2:E250)}
Now there came the problem: in the 'Master' tab, the data cannot be sorted when the arrays are used. Otherwise, there is a warning message: You cannot change part of an array.
I've found that sorting a column next to one which has drop down boxes set up in it, does not take the options for the drop down boxes (data validation ) with them when being re-arranged. Is there a way to make this possible, or an alternative format I should be using? see example:
If item 4 is the only one available from supplier 3, (As shown by the drop down box options) how do I sort by say, department, and still have the correct options on hand? Sorting by department, the items move, but the data validation info stays where it is, leaving the supplier 3 option for item 1, which is not correct.
I'm working on a spreadsheet to track student test scores over the course of the school year. Each score set is grouped by student, but we need to be able to realphabetize when new students are added. is there some way to accomplish this?
1) Imagine i have a row 1 with names, column 1 with bank account numbers, and under each name corresponding to each bank account it says "yes" or "no" depending on whether or not they are authorized on the bank account. How do i make 2 drop down lists, 1 with bank account numbers that produces the names which are authorized on the account and visa versa (list with names showing bank a/c numbers). It needs to be able to be updated easily.
2) Suppose I have the table as described above and another excel file with 1 column as bank a/c's and another column is authorized bank users. In this column however each cell just has a bunch of names for example one cell may contain (james, john, jack, jennifer, bill). Is there anyway to check these names against the ones in the table from the other spread sheet?
