I was wondering if someone might be able to write me some code that would allow me to automaticcally sort data in ascending orderany time there was a change in score. The names column is b1:43 and the corresponding score would be in column c1:43. I can't seem to do it.
Autosort
-
-
-
Re: Autosort
if you place the following code behind the relevant sheet module it should work:
CodePrivate Sub Worksheet_Change(ByVal Target As Range) Range("B2:C43").Sort Key1:=Range("C2"), Order1:=xlAscending, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub
this is presuming your data has a header and you want to sort by score
-
Re: Autosort
it doesn't seem to be working
-
Re: Autosort
it works for me. can you upload your spreadsheet?
-
Re: Autosort
i think i did..............it is the standings i want fixed please. thanks for your help
-
-
Re: Autosort
theres nothing there? Heres an example
-
Re: Autosort
how do i do it?
-
Re: Autosort
click post reply, then click on the paperclip to the right of the smiley face.
a new window will open. click on the top "browse" button, find your file, click open then click upload. once uploaded you can close the window. -
Re: Autosort
it's too big i think...........1.2m.
-
Re: Autosort
your quite a gifted man... sorry joking aside...
did you look at my example I uploaded for you? compare it to yours, make sure you are placing the code in the correct module - very important.
To reiterate, pref F11 to go into the VBA editor. Under Microsoft Objects, right click on the sheet that your data is in and select view code. Then paste all of the code I posted earlier into here. This code runs everytime the sheet is changed, so its imperitave it goes onto the correct sheet module.
-
-
Re: Autosort
hey jamie, when i call your programm up and try to change a value it wont let me. it goes into the code box and tells me that i have a compilation error and the named argument is not found....am i really stupid or what?
-
Re: Autosort
no your not stupid, its probably because we are using different versions of Excel. What you could do, which helps when learning vba, is this:
* Go into the VBA editor (F11), view the code of the relevant sheet, and delete all the code except for sub and end sub. You will be left with this:
* close the VBA editor, then go to tools>marco>record new macro. Sort your data in the normal way (data>sort..) then stop the recording.
* go back into the VBA editor, and it will have created a new module. View this code.
* copy all the code between sub and end sub, go into the relevant sheet code, and paste it between the worksheet_change sub and end sub.This code should then run each time a change is made to the worksheet.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!