Getting Sort Function To Work On Defined Dynamic Range
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,,,,
My problem is in this defined range in "C30,D30,E30, F30 & G30" there is a possibility of more than one entry. If that is the case the entry would be in "C31,D31,E31, F31 & G31" If this is the case I need to pick the later which is "E31, F31 & G31" not
I want to do is sort in ascending sequence, a range defined as rTrainRow which is a row of text data. Each cell in the row can contain space, blank, one Alphabetic character or a special character such as $, %, (, +, etc.
I have a Ranking tab that has the persons name, weeks, months that subtotal into 2 columns, MTD and YTD. I have set up with 2 Macros to sort based on two columns based on their selection of MTD or YTD.
What I did not take into consideration is if a person was added at the bottom of the list.
Is there a way to modify this to include rows that may be added?
How might the code below be changed to handle any number of rows? Right now it's set (from a recorded macro) to go down through row 237, but I need it to sort anything from just a few to maybe a thousand.
We have 2 dynamic ranges (input, output) on different sheets (sample data below).
Sheet1> Input Dynamic range 5 columns
Sheet 2>Output Dynamic range 4 columns - Include only rows IN=1 - Sorted by LEVEL (BIG to SMALL) and BUY (SMALL to BIG) - Keep duplicates
What formulas should I place on Sheet2 avoiding Pivot tables or VBA?
Sheet1> Input (16 data rows) A B C D E Level Buy Sell Firm IN 16620 4.00 null F1 OUT 16610 5.10 0.80 F1 OUT 16600 11.40 6.60 F1 1 16590 24.50 18.60 F1 1 16580 44.90 37.10 F1 1 16570 66.90 59.60 F1 1 16560 84.40 78.70 F1 1 16550 95.00 90.30 F1 1 16540 99.80 95.60 F1 OUT 16530 100.00 98.00 F1 OUT 16611 6.66 0.497 F2 OUT 16600 9.09 1.96 F2 OUT 16589.1 20 12.5 F2 1 16578.2 41.66 33.33 F2 1 16567.3 73.33 65.21 F2 1 16556.4 90.9 84.61 F2 1 16545 98.5 91.66 F2 OUT
Sheet2> output (10 data rows) A B C D Level Buy Sell Firm 16600 11.4 6.6 F1 16590 24.5 18.6 F1 16589.1 20 12.5 F2 16580 44.9 37.1 F1 16578.2 41.66 33.33 F2 16570 66.9 59.6 F1 16567.3 73.33 65.21 F2 16560 84.4 78.7 F1 16556.4 90.9 84.61 F2 16550 95 90.3 F1
Depending on time of day and computer I am sitting in front of I am using:
WinXP Pro SP2 with Excel 2003 Win7 Pro SP2 Excel 2007 Win7 Pro SP2 Excel 2010 on PC Win7 Pro SP2 Excel 2010 on Mac Mini running Boot Camp OSX Excel for Mac 2011
I have only tried this on Win7 Pro SP2 Excel 2007 but need it to work on all.
I enter the values 0, 1, 2 ... 89, 90 in cells A1 to A91
I select A1:A91 and name the range 'angle'
I create a user defined function:
Public function sindeg(value As Double) as Double sindeg = sin(worksheetfunction.radians(value)) end
I want 'value' for the function in a cell to be replaced by the corresponding value in the same row (or column) in the named range 'angle'. For example (using commas as column separators). This works for Excel functions like sin, cos, radians, etc.
Column A = input. Columns B, C, D, E and F all calculate the same value by row. Column G fails with a #VALUE. In row 1 the value of angle[1] = 0 therefore column D = C = B = sin(0) = 0. In row 2 angle[2] = 1 therefore B = C = D = 0.017452
How can I get the user defined function sindeg(value) in column G to accept the named range variable 'angle' like the Excel function radians(value) accepted it in column D?
This functionality should work horizontally as well as vertically. For example enter 'angle' A1 to CM1 then have sindeg(angle) filled from A2 to CM2. It should also work in the case where the named range 'angle' is a single cell.
I am sure that this is a simple variable type definition problem in my user defined function: should the input variable be defined as type Range? Or something more exotic?
The brute force approach is to have the function determine the input value by passing in the named range, working out dimensions, calculating offset between the cell the function is in and top (left) of named range, then counting down (right) to pick the correct value. However I cannot see adding all that code to EVERY function. Occam's Razor says there has to be an easier way since Excel built in functions seem to do it readily.
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My Public Function SortRange(rngToSort As Range, valCol As Integer) Dim Swapper As Variant Dim i As Integer, _ j As Integer, _ k As Integer
For i = 1 To rngToSort.Rows.Count For j = 1 To rngToSort.Rows.Count - i If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then For k = 1 To rngToSort.Columns.Count Swapper = rngToSort(j, k) rngToSort(j, k) = rngToSort(j + 1, k) rngToSort(j + 1, k) = Swapper Next k End If Next j Next i SortRange = rngToSort End Function
i used excel as a calculator but when i needed to make some effort to reach a solution for my problem it let me down, so i will write my problem i have a record in my data base and i want excel to look if the record is in another range and apply some maths. so i wrote this formula
I have created a Macro in VBA which takes a data set, puts in in a pivot table which I then use to sort/select etc. after that I copy the data in a new sheet as ' values only' with paste special.
As the source date is variable (number of lines can vary) I have set the macro to take the variable number of rows into account.
Evertyhing works as it should.
Now my question:
in the final sheet, I want to fill the first empty column at the end of all filled-in columns with a count function. selecting the last column works, but I have problems with making the COUNT function variable. It should count the number of cells with a value in it. However as the number of columns can vary, the count function should take that into account. Furthermore, it should never take the first and last column into account. Here's where I got stuck: ...
I want to use a dynamic range in order to use this formula below in column A as long as there is something entered in column H = SUMIF($N:$N,A5,$I:$I). any ideas on syntax and will this be using the offset function within excel. I know I have posted a couple times about getting this formula to replicate itself down certain columns as long as someone enters text in another. I looked into the offset reply information somone posted but it seemed a little over my head.
I have a worksheet where the number of rows will change on a weekly basis and I would like to print the sheet out without having to change the print range every week.
I have tried to use the OFFSET function in the print range in print setup but once this has been entered and printed, it changes to the actual print range the OFFSET returns.
I am having some trouble with a variable range selection within a regression. I keep getting an "application-defined or object-defined error." I've isolated each statement to find that the code that is causing the regression not to work is below (the error for that line of code states that the Select method of Range class failed):
I have seen one of your posts in which you make a graph update based on dynamic named ranges. Here is my problem, I already defined the name of my ranges as:
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
I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from C1:C600 to C1048572:C595 - or some other strange range).
P.S. What it's doing isn't so important as when the cell references are right it works. But just for full info: It's an OFFSET function, starting at A1. It will look at a list of companies and use a Match function to find out how many rows to offset (based on where the first instance of a particular company. And it uses the COUNTIF to make the range the same number of rows as there are entries for that company.
when you type in a builtin function in the formula bar such as =DAY(), a small callout box appears below telling you the syntax - in this case "DAY(serial_number)". Is it possible to achieve this for a user defined function?
I have a financial dataset which I need to "clean" before manipulating/analysing.
Each row of the data represents a completed transaction and the first step is to delete rows that are done with particular (internal) clients; the client data (the client's name) is in Column D.
Currently, I delete the unwanted data by autofilter and delete (code below), however this means the clients to be deleted are only defined within the code. I would prefer to have a worksheet within the workbook where the user defines the clients by adding or subtracting their name from a list.
I have created a dynamic range for the client list by inserting a Named variable along the lines of "=OFFSET(!$B$3,1,0,COUNTA($B$3:$B$200)-1," which works fine.
However how do I work this list into my code so that it works when new clients are added or deleted?
Current code for deleting unwanted client data:
Sub filterdelete()
Dim LastRow As Long LastRow = ActiveSheet.UsedRange.Rows.Count
Using Microsoft Access to pull from a database work orders. Some of those are what we call "dummy" work orders and have a letter in them, real work orders are signified by a 10 digit number. I would like to sort and eliminate the letters to find appropriate data. I am assuming I will need to copy into excel and do some sort of formatting but not sure where to start.
