Free Microsoft Excel 2013 Quick Reference

Sort VBA

I need some code to attach to a button that will sort data in H3:CA770 in descending order, the catch is I need this to sort on by the column to the most right with data in it.

I use rows 1 & 2 as a header so there will always be data in that, so I need the button to sort my worksheet based on the last column with data in it. The sheet will be updated monthly so I dont want to creat 12 different sort buttons.

Thanks,
Travis


Hi,

I have this VBA error 1004 for sorting.

I tried on some PC. Some PC have this error while some PC do not have it.
It puzzle me and not sure if it is due to different Microsoft office version.
PC that do not have this error is using Microsoft office 2002.
PC that have this error is using Microsoft office 2000 & 2003.


	VB:
	
Selection.Sort Key1:=Range("C71"), Order1:=xlAscending, Header:=xlGuess, _ 
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
DataOption1:=xlSortNormal 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you for any advise!

Gon

Hi,

If any one could give me some help on these two issues, I would be most grateful. thay are as follows:

1. I would like to get a vba/macro code for a sort function. i.e. that I can sort F3:F150 based on value size (which would sort all the other corresponding cells of course). This is very much like the sort function available in excel , however I need a code as the data is going to be updated as it is linked from another sheet.

2. I would also like to have a vba that would then colour the rows according to certain citeria, like an if statement, e.g. if F3:f150 is less than 49.99 colour all cells in that row with data in yellow; if f3:f150 is between 50 and 100 colour all cells in that row with data in blue; and, if f3:f150 is greater than 101 colour all cells in that row with data in red.

any help you can offer would be great!

Many Thanks,

Greg

Hi guys

My first post on this forum, I had been searching for a way to sort data by background color and this forum had the perfect answer BUT I have run into an issue - when I fill down the cell formula it does not change according to the referenced cell????

Example
Eli Abt -4142
Emma Barnett -4142
Akshat Vipin -4142
Louise Pryer -4142

The cell I am referencing (Louise Pryor) has already had conditional formatting applied and the background of the cell is bright blue, but the Background colour reference is still -4142

The VBA module I am using is (as per several examples I have seen):

Function GetBackgroundColor(MyRange As Range)
GetBackgroundColor = MyRange.Interior.ColorIndex
End Function

With a call of "=GetBackgroundColor(Cell range)"

Can anyone spot my error please, I can obviously go through manually but it has become a matter of principle now to get it to work

Thanks in advance for any help that may be offered

Sean

Hello,

I am a new member to this forum and I have an VBA related issue that I am hoping someone can help me figuring out the solution.

I have used the following VBA in excel 2007 and it workd fine. It is just that when I tried to use the same VBA in 2003 I get run time error 438. Here's the VBA and the line (bold) which is highlighted when the VBA is excuted in 2003.
Private Sub OptionButton1_Click()
Application.ScreenUpdating = False
Range("B4:I10").Select
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("C4:C10") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet3").Sort
        .SetRange Range("B4:I10")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B3").Select
Application.ScreenUpdating = True
End Sub

I appreciate if some one can let me know what's wrong here and how to fix it, keeping in mind that the above VBA woks fine in 2007 but gives the run time error 438.

Regards,

Hi ihave a simple inserction sort algo:
The prob is when i inisialize the Arry Redemption with values from my sheet it takes 0 Value for an empty cell (as exemples Redemption(4)or Redemption(3))
so my sort algo get konfused and puts 0 s at the bigining

Any help


	VB:
	
 
 
Dim j, k As Integer 
Redemption(0) = Cells(8, 3).Value 
Redemption(1) = Cells(9, 3).Value 
Redemption(2) = Cells(10, 3).Value 
Redemption(3) = Sheets("Wcake").Range("C11") 
Redemption(4) = Cells(12, 3).Value 
 
Dim temp1, temp2 As Double 
 '***************************************************************
 'Sort
 '***************************************************************
For j = LBound(Redemption) To UBound(Redemption) 
    For k = j To UBound(Redemption) 
        If (Redemption(j) > Redemption(k)) Then 
            temp1 = Redemption(j) 
            Redemption(j) = Redemption(k) 
            Redemption(k) = temp1 
        End If 
    Next k 
Next j 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a macro that shows the sort dialog box. Makes it easy for my users.

Here's the code I've been using:
Application.Dialogs(xlDialogSort).Show

However, this doesn't seem to work in Excel 2007, any ideas?

Here's the complete code:

Sub Sort()
ActiveSheet.Unprotect ("123456")
Rows("4:1500").Select
Application.Dialogs(xlDialogSort).Show
ActiveSheet.Protect ("123456")
Exit Sub
End Sub

Any help would be appreciated.

Can any one figure out why this code is producing an error:
    Range("A2:F200").Sort _
        Key1:=Range("A2:F200").Cells(1, 1), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        Key2:=Range("A2:F200").Cells(1, 2), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        Key3:=Range("A2:F200").Cells(1, 3), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
When I run the code...I get the following error:

Run-time error '450':
Wrong number of arguments or invaild property assignment

If I remove Key3 from the code...it runs fine.

Am I missing something, here??

Running Excel 2007

Hi All

Trying to run a macro for a sort.

If fixed column - not a problem, but I want the macro to sort if column
month tally's with B1

E.g. if Month 1-3-06 then sorts column D if 1-4-06 sorts column E etc.

I have created a little formula that high-lights the current month - i.e.
=IF($B$1=D5,"COLUMN /","") if that helps?

A B C D
E
Enter Current Month 01/03/2006 Start
THEN CLICK BELOW RUN SORT ROUTINE
COLUMN/
HELP Point
Name BRANCH 01/02/2006 01/03/2006 01/04/2006

This is Code if FIXED COLUMN.

Rows("5:81").Select
Application.CutCopyMode = False
Selection.sort Key1:=Range("G5"), Order1:=xlDescending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Any help appreciated.

Paul T

Hi,

I have a sort vba that sorts by two ranges, the first is name that is just a text value. The second contains a formula which if true gives a letter if false returns a blank entry. The data in my spreadsheet is filled down to row 1000 and It sorts fine Descending but sorts including the blank data when changed to Ascending.

Can a sort be changed to exclude blank data?

The vba:

Sub sortpri_asc()

   Application.Goto Reference:="rangevclhdr"
    Selection.Sort Key1:=Range("AH5"), Order1:=xlAscending, Key2:=Range("H5") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    Range("A4").Select
    Cells(5, 1).Select

End Sub
Thank you in advance,

JP

So I am trying to make a macro that will use the custom sort with a string variable called myString. However, whenever I try to run this I get a type mismatch error. Any ideas?

    ActiveWorkbook.Worksheets("My
Sheet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("My Sheet").Sort.SortFields.Add Key:=Range _
        ("A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        myString, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("My Sheet").Sort.SortFields.Add Key:=Range _
        ("B1:B5"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("My Sheet").Sort
        .SetRange Range("A1:B5")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


Hi,
I have a figure in cell c2 of my spreadsheet and I need to use some sort VBA loop and arrays to alter the figure 10 times, to 20, 21, 22, 23, 24, 25, 26, 27, 28 and finally to 29. I need a 2 second pause between each change in the figure and the current cell being altered should have a blue colored background.
Any help at all would be greatly appreciated!
Thanks

I apologize if the title is cryptic... I've been at this for days and I've tried so many formula combinations that I'm not sure what to call this anymore.

The attached sheet probably explains it best, but basically I have a dynamically updating spreadsheet that allows end-user-defined attributes + categories for those attributes, and their definitions and placement are all user-driven. The end result is the percentage-ordered doughnut chart that has weighted categories as the base and the attribute scores that comprise each category encircling it. (I know, pies and doughnuts are bad, bad, bad).

I need to get the attribute scores sorted by (1) category weight then (2) high to low... AND aligned in ONE column. Plus I need the attribute names next to the ordered data so I can make the chart. What formulas do I need in the two right-most columns?

Thanks!

Edit: I have the first two columns under "What I want" figured out already.

Edit: I resorted to a copy-paste.value-sort VBA solution that will do the trick. Not as elegant as I would have liked, but it's time to move on.

Dear All,

I need help with my VBA code which is not working. I am trying to use the following VBA code to sort my database based on a particular column – i.e. column B:


	VB:
	
 Range) 
    If Not Intersect(Target(1, 1), Range("A:L")) Is Nothing Then 
        Range("A:L").Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlGuess, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

My database has 12 columns (A to L). The first 5 rows take up the headings – with all the headings formatted the way we require them. This means that – for some of the row headings, some of the cells are merged; the heading cells are not all equal in size. However, from Row 6 – the row from which we start entering data, all the cells are of the same size.

We use Excel User form to enter data onto the database. This is working OK.

The problem is with the sorting of the database based on column B (which contains Names of members). The first data entry row is Row 6 and the cell containing the first name is B6. In order to run the sort code, I right-clicked the sheet name (at the VBA editor) and pasted the code. But it does not seem to work. At some point, it comes up with an error message as follows:

“This operation requires the merged cells to be identically sized)."

I need help on what I should do to get the code to work. We require the database heading rows (rows 1 to 5) to remain the way they are – formatted. Please is there anything I can do so that upon clicking the ‘Add Data’ command button on the user form, the data will be added to the database and the sort code will run automatically and sort the database based on column B (not minding the fact that the cells making up the headings in rows 1 to 5 are not identically sized.

I have attached an abridged copy of the database (zip file)

Thanks for your anticipated help.

Buddy

Hi All,

I've got a simple range of cells A1:B## (goes down various lines), Row 1 are my headers
In VBA can I sort the range of cells by the length of the string in Column A without having another column C with Len(A2) in it?

Thanks,
Ranmaru2

We are trying to simplify a process we have with a macro since it takes to long each morning the way we have it now. We add a new sheet to the workbook everyday and label it the current date. We need a macro that will

1)delete columns H-AE on the new sheet (those will always be the same on each new sheet).
2)do a vlookup with the first sheet
3)sort the new worksheet based on the answers from the vlookup in descending order

I am kinda new to VBA and still trying to learn a lot. Any help that would be received would be greatly appreciated.

At my company we have a program that we consistantly use to export data to excel. Unfortunatley the program codes business units with a combination of its own code and our internal code making it very time consuming to sort and decipher. Is there anyway to use VBA to automatically convert these names to our standard code. I was thinking maybe a v-lookup but i am not sure how to use a v lookup with more than 3 columns of data to sort through. I would greatly appreciate any bright idea

thanks

Hi,

I have IP addresses from cell A1 to cell A200 (this range will change from time to time). Is there a VBA macro to sort the IP addresses?

Thank you.

Hi I have a Code where i want to sort a data with multiple columns and varying number of rows.
the data should be sorted using 3 columns.
Sort column B, then Sort column E then Column S.
data should be arranged Ascending Column B, Ascending Column E then Descending Column S
Data on all 3 Columns are just numbers, all of them have headings.

the code i have come up with is


	VB:
	
 SORT() 
     '
    Range("A1:s" & Range("s65536").End(xlUp).Row).Select 
    Range("G14").Activate 
    ActiveWorkbook.Worksheets("Data_FE1").SORT.SortFields.Add Key:=Range( _ 
    "B2:B2459"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ 
    xlSortNormal 
    ActiveWorkbook.Worksheets("Data_FE1").SORT.SortFields.Add Key:=Range( _ 
    "E2:E2459"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ 
    xlSortTextAsNumbers 
    ActiveWorkbook.Worksheets("Data_FE1").SORT.SortFields.Add Key:=Range( _ 
    "S2:S2459"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ 
    xlSortNormal 
    With ActiveWorkbook.Worksheets("Data_FE1").SORT 
        .SetRange Range("A1:X2459") 
        .Header = xlYes 
        .MatchCase = False 
        .Orientation = xlTopToBottom 
        .SortMethod = xlPinYin 
        .Apply 
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
my problems are:
1. VBA does 3 separate sorting instead of 1 sort with 3 criteria.
2. number of rows may increase or decrease daily so i need the range to be dynamic
3. this will be part of an existing macro

Any help will be very much appreciated.

I have an array of data that corresponds to some heat probes that I am using. There are 15 columns that correspond to to each of the probes, and hundreds of rows based on time intervals that probes are active.

I need to analyse the data, so that a cell returns the name of the column that has the largest value in the array.

I cannot sort the data or use VBA.

I really just want to have one cell have something like "Probe with Max Temp" and the cell next to it give the Column Name.

I've attached a sample of the data, I've come up with a bunch of different ways to get the highest temperature in the array, but no way to output the column that number is in. The sample isn't the actual sheet, but it is similar data.

Also, when I use Match Index, I can't get the match function to work correctly. It's like I can only get the match function to work in a single row or a single column. Thanks

I have a quiz scoreboard set up on a spreadsheet.
The vital columns are A (Team Number) J (Total score) and L (Rank).
To sort the results after collecting the scores for each team, I highlight the rank of cells then click Data>Sort (by column L) and then to reverse this I click Edit>Undo sort. I would like to automate this process by creating a macro (and later a button) which sorts the results without the need to manually highlight the range of cells. I would then like to create a macro that is effectively the Undo Sort command - (if the one macro can toggle between the two results, so much the better). I am a complete VBA novice, so any assistance would be much appreciated. Thank you.

Hi All,

I've searched and tried about 5 different versions of sorts in VBA on here, and none of them do anything. If anyone can provide me with code that will sort all the data in sheetB by column B, with header, ascending order. Dunno if its making a difference, but I'm using XL 2000

Thanks!

hi all,
this is some sort of complicated thing for me

note: "~" means next column
"/" means Or

Lets say i have this data: (only a small number of fake sample)
A1
Data
Jupitor Land St 21 Blk 346 #7-459 Jupitor 869573
Jupitor Way Rd 6 Blk 73 #4-43 Jup 456421
Marssy Round Ave 8 Blk 9 #20-9785 Mars 098234

What i need is using excel vba to create a macro that:
1. identifies the pattern of address according to each heading which they belong to:

Eg.(headings)
B1 ~ C1 ~ D1 ~ E1
Street Address ~ Blk ~ House # ~Postal Code
Jupitor Land St 21 ~ Blk 346 ~ #7-459 ~ Jupitor (869573)
Jupitor Way Rd 6 ~ Blk 73 ~ #4-43 ~ Jup 456421
Marssy Road Ave 8 ~ Blk 9 ~ #20-9785 ~ Mars098234

in this case, each heading that would be sorted would be identify by a unqiue key:
street address >identified by > St/Rd/Ave
Blk > identified by > Blk
House # > identified by > #
Postal Code > identified by > Jupitor/Jup/Mars

in addition, other things like: "," or "(" or ")" if they can be removed from the strings it would be greater for me as the data would be sorted more "nicer" for the user. ---> this part not necessary if its too time consuming

i have further attached a sample of what i need so that u guys wont be confused.
I have little knowledge of sorting in excel vba and hope somebody can help.
Thanks in advanced!!

ps: hope that i am not asking too much :/

Looking for a way (VBA??) to make XL sort data in a specific sheet for, say, column A:E (row 1 containing headings) when closing a workbook.

And when the workbook is opened the marker in the same sheet shall move to the first available (empty) cell in column B.

Any suggestions?

All the best,
/D

If I am a novice at Excel then I am complete Newbie at VBA. I am currently doing the Training by Dave here at OzGrid and very good it is too! I would recommend it to anyone wanting to learn VBA for Excel. I have many books but they bear no relation to real life projects.

Anyway I digress. I have written a simple sort routine that works just fine provided the sheet isn't protected. I do need to protect the sheet so can someone please tell me what is missing from the following code?

Sub SortTable()
' sorts sales people according to highest selling performer

Range("B8:E25").Sort Key1:=Range("c8"),Order1:=xlDescending, _
Key2:=Range("d8"), Order2:=xlDescending, _
Key3:=Range("e8"), Order3:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1,_
MatchCase:=False, _
Orientation:=xlTopToBottom

End Sub

As ever, all help will be most welcome.

Thanks in advance

Dave