Solved: Sort Data Based on Value of a Cell

[gimli]

Hey all,

I would like to sort data based on a value entered into a cell. The data must sort by closest values to that cell.

Ex:

Cell F11= 35 (will always be a number)

Cells J8:Q17 contains 10 rows of data

I want to sort that data by column N - closest value to 35 at the top second closest or duplicate second..and so on.

Im hoping to put this as a macro

All help is great...

thanks much

[mbarron]

Try this:

Sub SpecialSort()
Dim i As Integer
Columns(14).Insert
For i = 8 To 17
Cells(i, 14) = Abs(Cells(i, 15) - Cells(11, 6))
Next
Range("J8:R17").Sort key1:=Range("N8"), order1:=xlAscending, Header:=xlNo
Columns(14).Delete
End Sub

[gimli]

Gave me a runtime error 13 "type mismatch" this first time i run it. I run it again and it sorts the data decending with no error message

The data in cell F11 in this case was .028

The data was sorted to show

1st row column N was .033
2nd row column N was .028

I would like it sorted by the closest value to cell F11. In this case it would should have been sorted

1st row column N was .028
2nd row column N was .033

Thanks much

[mdmackillop]

The code works for me. Can you post your workbook?

[gimli]

Hey mdmack,

Here is a workbook for ya to look at...had the day off yesterday or I wouldve posted sooner.

I run the macro and get the runtime error. Then I run it again with no error but the data is not sorting.

Basically I would like the data to sort based on cell F11. I would like column N to show closest number to F11 1st....then the next closest and so on

thanks much

[mdmackillop]

As I said, this works for me, and also with your example. Try stepping through to code to identify the problems.

[gimli]

MD,

Sorry ..yes..it does work as you said with the file I attached as a sample.

After fooling a bit..I get the logic and copied it to my full blown spreadsheet. I didnt realize that the sort option wont work while the sheet is protected...dah

So I unprotected the sheet and I still get an error..... "type mismatch". It seems to crap out before the sort of the column N. It populates the new column with the proper information but stops before it sorts and deletes the column N.

I have the macro as a workbook macro and a sheet macro with same results. Also is there a way to unprotect and the protect the sheet again with the macro..

thanks much


Sub SpecialSort()
Dim i As Integer
Columns(14).Insert
For i = 8 To 17
Cells(i, 14) = Abs(Cells(i, 15) - Cells(11, 6))
Next
Range("J8:R17").Sort key1:=Range("N8"), order1:=xlAscending, Header:=xlNo
Columns(14).Delete
End Sub

[gimli]

Ahh..

Just thought of something...the range j8:r17 on my spreadsheet is basically a lookup table..populating the cells via vlookup. On my test spreadsheet i just copyied the values over for simplicity..

Maybe thats why Im getting the error? As long as the lookups are still integers it should be ok im thinking

[gimli]

Ok...mismatch was due to my lookup table leaving a #REF error in column N when the table was not full of data. In this cast the first 2 rows were populated with data and the last 8 rows were populated with #REF errors..so it crapped when tryin to subtract integer from #REF error.

so its working now...just need some code to unprotect sheet before running the macro and then protecting it again when its done

thanks much

[lucas]

Worksheets("Sheet1").Protect Password:="MyPassword"
'code
Worksheets("Sheet1").Unprotect Password:="MyPassword"

[gimli]

thanks much :thumb

[lucas]

Mark it solved please. Thread tools at the top of the page.