YTL Excel #112: VLOOKUP Problem (Sort Ascending)

Hi All

I currently have a workbook that sorts on specific columns and works fine until I use it on column B. This is the column that has a formula in it right down to row 500.

The problem is that when I sort this column (in an ascending fashion) it puts all my entries at the bottom of the list thus prioritising the blanks!?!?!?

Is there any way get the sort to put the entries at the top like all the other columns that don't have a formula in???

I'm stuck again...

Here is the code for the sort (the extra bits are just to add a colour bar on the sorted column header)

Thanks in advance if anyone can help

Code:

Sub Column_B()

   Range("B5:H" & Cells(Rows.Count, "H").End(xlUp).Row).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("B4").Interior.ColorIndex = 10
    Range("C4").Interior.ColorIndex = 53
    Range("D4").Interior.ColorIndex = 53
    Range("G4").Interior.ColorIndex = 53
    Range("H4").Interior.ColorIndex = 53
    Range("B5").Select

End Sub

This option enables to sort from the Filter dropdown list
identical to the Sort A to Z/Sort Z to A or Sort by Color).

Am using Excel 2007. As I type =VLOOKUP the function comment/Help appears and says "By default the table must be sorted in ascending order"
So I enter my lookup table in ascending order

1 A
2 B
3 C

and the function works. A VLOOKUP value of 2 returns B
But if I reenter the table in descending order

3 C
2 B
1 A

the function still returns the correct value.
So does a VLOOKUP require a table in ascending order or not?

I guess I've sorta answered my own question already but why does Excel say VLOOKUP tables should be sorted in ascending order when that's not true?

Hi,

I need to disable sort ascending and sort descending in a worksheet of the workbook but still being able to use the auto-filter.. is there a macro to do that ?

otherwise, is there a way to bring the data back to their original state after sorting a column of dates in ascending order for example ?

cheers
ash

Hello;

I have an external query and want to sort by schedule date - only problem is there are some items without a start date and these pull up first - I'd like see first the ones with a date, then follow with those without. Is there a way to do it? Thanks for any help!

Hello,

Unfortunate the circumstances, I am not very good with excel
Currently I have the following code to insert the copied range into the combobox. The following code works fine.

Quote:

With Sheet1
.Range(.Range("D3"), .Range("D" & .Rows.Count).End(xlUp)) _
.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("P3"), Unique:=True
fClient.List = .Range(.Cells(4, 16), .Cells(.Rows.Count, 16).End(xlUp)).Value
End With


However, when following is entered (added), the code does not work... what I am trying to do is to "sort ascending" the range so that it would display alphabetical order in the combobox shown. The name of the worksheet is BalanceSheet

Quote:

With Sheet1
.Range(.Range("D3"), .Range("D" & .Rows.Count).End(xlUp)) _
.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("P3"), Unique:=True
fClient.List = .Range(.Cells(4, 16), .Cells(.Rows.Count, 16).End(xlUp)).Value
Sheet1.Range(“P3″, Sheet1.Range(“P65536″).End(xlUp)).Sort _
Key1:=Range(“P3″), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With

If you require any further information, please let me know and I will upload what information is required.
Your assistance is much appreciated. Thanks so much in advanced.

Hello,
I have data in a spreadsheet with approximately 100 rows. In column F, the following values are allowed:
Subscription
Redemption
Transfer In
Transfer Out
Switch In
Switch Out


Is there any way to sort them in this order, rather than ascending or descending? (I want all of the Subscriptions together, then the Redemptions, and so on...)

Thanks!

I have multiple graphs that I want create from one table. I want to make a graph for each column. I want to be able to view all three graphs with the data in ascending order without changing the table. There is one post out there on this forum that uses VBA. I can't get it to work for my table. Can someone help me figure this out. File is attached.

Thank you,
Sonia

Hope you can help, I've searched the forums in earnest but can't find anything similar...

I'm building a sheet that lists accounts which have a negative balance, with the balance in column B.

Whenever I add a new account with attendant information, I'd like to be able to click 'Save' and have the new row sort itself by the balance, with the other lines preserving existing formatting and the new row adopting the new formatting.

This may explain:


Before-

Column B
-100
-50


A new line is added in the row immediately below the existing-

Column B
-100
-50
-75


Click 'save' and hey presto-

Column B
-100
-75
-50




Does that make sense?

I know it sounds very easy but I've tried some VBA code that seems like it would work but I can't even get the two individual elements (i.e., "save document" and "update sorting") to work independently!

Please help!

Hi

I am still very new to macros and most of what I have done I have managed to find from the internet and then make a few changes to suit what I am looking for. This time around though I don't know what I have done wrong and why it is not working.

I want to sort my spreadsheet by Column A and then Column B, Row 1 does have a header.

I want/need to use a macro as I have merged cells and I have been told the only way I will get to sort my spreadsheet with merged cells is through a macro? ~ Please tell me if I have been wrongly informed.

When I searched the internet this was the result but I can't get it to work.

Code:

Sub SortDateTime()
On Error Resume Next
ActiveSheet.Unprotect
  ActiveSheet.Range("A2:I998").Sort _
    Key1:=Range("A2"), Order1:=xlAscending, _
    Key2:=Range("B2"), Order2:=xlAscending, _
    Header:=xlYes
ActiveSheet.Protect DrawingObjects:=True, _
  Contents:=True, AllowFiltering:=True
End Sub

I do have other macros running and they are working perfectly.
Code:

Sub AutoFilterToDo()
On Error Resume Next
ActiveSheet.Unprotect
'check for filter, turn on if none exists
  If Not ActiveSheet.AutoFilterMode Then
    ActiveSheet.Range("A1:I1").AutoFilter
  End If
  'show tasks with no calculated hours
  Range("A1:I1").AutoFilter Field:=7, Criteria1:=""
ActiveSheet.Protect DrawingObjects:=True, _
  Contents:=True, AllowFiltering:=True
End Sub
Sub ClearFilter()
On Error Resume Next
ActiveSheet.Unprotect
'removes AutoFilter if one exists
ActiveSheet.AutoFilterMode = False
ActiveSheet.Protect DrawingObjects:=True, _
  Contents:=True, AllowFiltering:=True
End Sub
Sub AutoFilterShowAll()
On Error Resume Next
ActiveSheet.Unprotect
'check for filter, turn on if none exists
  If Not ActiveSheet.AutoFilterMode Then
    ActiveSheet.Range("A1:I1").AutoFilter
  End If
  ActiveSheet.ShowAllData
ActiveSheet.Protect DrawingObjects:=True, _
  Contents:=True, AllowFiltering:=True
End Sub

Are the above macros possibly interfering with the new sort macro that I am trying to add?

Like I said I am so new to macros the only thing that I change in a macro is the range to suit my needs. I did this with all the macros that you see.

The only one that is not doing anything is the Sort macro.

Can someone please help me with this, I realise that it is a big ask since I really don't know what I am doing with macros.

Any help/advice would be really appreciated.

Thanks in advance for reading this post and further Thanks if you are able to assist