|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Trying to get my list to autosort when a number changes. Need help tweaking my VBA macro
I have a table with a list of numbers on the left column and text on the right column. The list of numbers are referencing other cells to get their values. The spreadsheet is a skill inventory for where I work, and as you fill it out, it will adjust a score for different categories (like communicator, leader, technical etc.) The cells in the list, are referencing the cells that have the scores for each category. The intent of the list is to automatically sort in real-time the categories you have the highest score in. I then have 2 formulas (LARGE functions) that pull the highest and second highest number from the list. Then I use 2 VLOOKUP functions that use the numbers I pulled out from the LARGE functions in order to go through the list and pull out the text of the corresponding highest and second highest number in the right column. I have code that I will paste below that will automatically sort the list if I change a number in the list, and then the LARGE and VLOOKUP functions work correctly. The problem is, as you fill out the inventory, the numbers change in the cells that the numbers in my list are referencing, which in turn change the numbers in the list, HOWEVER, since I am not actually changing the numbers myself in the list, it does not autosort.
I need the list to autosort not just when I manually change a number in the cells in the list, but whenever there is a change in one of the numbers on the list. Here is my current code. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("K:K")) Is Nothing Then Range("K20").Sort Key1:=Range("K21"), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End If End Sub I would appreciate any help! I uploaded the document as well/ Matthew Skinner |
#2
|
|||
|
|||
I am assuming here but it appears that your code works fine. What may not work is when you change a value on the first sheet and then go to see the sort results. This will not change until something is changed in column K. However if you insert your same code on the worksheet activate sub it should work. When you say manually change the numbers I was confused because the numbers in column K are all formulas that if changed you would lose the formula.
Let me know if the worksheet activate fixes the issue. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
List Style Numbering picks up out of order number from LATER list | spthomas | Word | 12 | 12-16-2013 05:23 PM |
Tweaking preset pivot table styles | New Daddy | Excel | 1 | 12-12-2013 05:20 PM |
Number List Problem! | Mole Patrol | Word | 2 | 09-18-2013 11:46 AM |
Problem copying number list to new section | iamoakey | Word | 1 | 09-12-2012 01:02 PM |
Number List not behaving | ECPL_3 | Word | 0 | 11-13-2009 11:52 AM |