-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.
I am copying and pasting from two different " timesheet" spreadsheets into a list. One of the timesheets has blank rows. I am attempting to sort the blank rows to the bottom after I paste the data, but every time I do, it either replaces the top row with "true" or deletes the headers,
Sub SortBlankRows()
Dim rngCurrent As Range
Dim c As Range
Dim inUsedRow As Integer
Set rngCurrent = Workbooks("Payroll Summary.xls").Worksheets(1).Range("A1:J1")
inUsedRow = Workbooks("Payroll Summary.xls").Worksheets(1).Range("D65536").End(xlUp).Row
rngCurrent = rngCurrent.Resize(inUsedRow)
rngCurrent.Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range("F1") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End Sub
I have two slicers that are in a hierarchy. These are attached to a Pivot table whose data source is an OLAP cube. Every Partner Parent is part of a Partner Group:
Partner Parents Slicers.png
My problem is that when I click SI Alliance in Partner Group, the Partner Parent slicer does not re-sort in any way. The corresponding selected Partner Parents are scattered throughout the alphabetical list. However, in a different document, I have slicers in a similar situation, except their Pivot table’s data source is a SQL Server database. When I click on a member of the higher up group (Accenture Global Client) the lower level group (Microsoft Account Name) sorts to show only the selected values at the top of the list:
This is with all selected - Accenture Slicers All.png
This is with just one Accenture Global Client selected. Note how the selected Microsoft Account Names have moved to the top of the list - Accenture Slicers Selected.png
How I can configure my Partner Parent/Partner Group slicers to behave like these Accenture/Microsoft slicers? Is there something that I can change in Excel or in the OLAP cube to make this happen? I have already tried right-clicking the slicer and going to Slicer Settings. The settings on the Parent/Partner Group slicers mimic those of the Accenture/Microsoft slicers exactly.
I have a row of sub-headings at row 12 that require Sort Filters. My problem is that I need the filters on selected columns only. It seems that with the standard filter button I get all or nothing.
View 8 Replies View Related Basically, I have a sheet that shows time in Days going downwards (monday tuesday etc.) and is broken out by week. In Column C, I have Project Names. Columns G-O have numbers.
What I need to do is run a custom sort multiple times down the page so that each week has been sorted by Project Name (column C). I was hoping to be to just make a quick macro shortcut to run my custom sort and each new range I selected but I don't know how to make the macro run on the actively selected cells and not hard-coded cells.
i want to automatic sort data very time i open the file or any change in column b.
i have data in column B from B1 to B100
how to collect data from Column B and place them in Column A star from A1 because Column B not Organized!
I mean B1 have data
B2 have null
B3 have data
B4 have nul .... etc
i want to display all Column B in Column A one by one to be Organized.
I would like to create a UDF similar to the SUMIF function but the UDF would be dynamic in that it would sum based on a dynamic range that would change based on the row the user is in.
background: I have a very large input template for 12-18 months for multiple cost categories. I would like to have the UDF in a specific cell above each category that would show the user what the sum of the range in the current row they are inputing data. Each row represents a specific project/task (along with 12-15 descriptive columns) that makes the freeze pane option unusable.
I am looking for a macro or function (VBA) that will modifiy the value of the selected cell or cells. the code should support selection of one cell, a range or multiple ranges.
I envision the user making his range selection(s). Activating a function or clicking a button that would pop open a modal window. The user would have the option to either adjust the values by a % change (i.e. up or down 7%), or incremental change (i.e. up olr down 100 units). The function would overite the value in the cells.
Has anyone ever done something similar? Is it hard to program?
For the sample data below, columns A – C contain survey response
data: 3, 6 and 9. I'm looking for advice on writing a function for column D
that would look at columns A – C and return the # of the column containing
the min value (e.g. A in this example).
A B C D
3 6 9 A
I have a spreadsheet which shows a matrix in pounds. But I would like to have a reference cell somewhere near the top which will automatically show the conversion to kilos of any selected cell. Is anyone aware of a function which will do this?
For example, one pound equals 0.45359237 kilos.
Cell A1 contains the reference function to the active of selected cell. If I click on B1 which contains 12, cell A1 would automatically show 5.45. If I click on cell C1 which contains 20, cell A1 would automatically show 9.09.
So I’m thinking it would be something like
=SelectedCell/0.45359237 or maybe =ActiveCell/0.45359237
I wrote this function to see if a user selected string contained certain phrases. It keeps returning united states so i am guessing its not cycling through the entire list.
Function region2(searchString As String)
Dim result As String
result = "None"
Dim lng() As String
lng = Array("arabic", "belg", "bul", "czech", "dan", "dut", "dutch", "euro", "finnish", "french", "ger", "greek", "greenland", "hebrew", "hung", "iceland", "international", "ital", "nor", "pol", "portu", "russ", "slov", "spanish", "swe", "swi", "turk", "UK", "united kingdom", "states")
Dim Country() As String..........
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?
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.
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?
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
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)?
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....................
I am trying to code a macro that will search through a selected range of cells for key letters, for instance this cell may contain any combination of B, C, Te, Tc, RH, or LH. I would preferably like to search with capitalization being a factor but it is not a deal breaker. Below is a sample of what i have if the cell has a B, C it works for B but ignores the C i need it t o recognize both.
Code:
If InStr(1, ActiveCell.Text, "B") Then Range("O" + CStr(ActiveCell.Row)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
[Code] ........
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 RelatedI 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] ..........
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:
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.
I have following data to sort/filter
Sector
Flt no
origin
[Code]...
Is this possible with excel functions?
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] .......
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]....
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.........................
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.
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 RelatedI 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 am using this code to hide or unhide rows of text on another sheet:
VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String
Dim sValue As String
'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True