VB Code to auto sort

[JMC57]

I am trying to have data I enter in Col B (300 lines) auto sort Hig to low and can't seem to get it to work. Any Ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:B300")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Me.Sort.SortFields.Clear
Me.Sort.SortFields.Add Key:=Range("B1:B300"), SortOn:=xlSortOnValues, Order:=xlDecending, DataOption:=xlSortNormal
With Me.Sort
.SetRange Range("B1:B300")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.EnableEvents = True
End Sub

 

[shg]

Welcome to the board.

Try putting Option Explicit at the top of the module and give it a try.

 

[JMC57]

This works for ascending 1,2,3,4,5,6. I can't seem to get it to work for decending 6,5,4,3,2,1

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:B300")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Me.Sort.SortFields.Clear
Me.Sort.SortFields.Add Key:=Range("B1:B300"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Me.Sort
.SetRange Range("B1:E300")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.EnableEvents = True
End Sub

Also col A has Drug name and col B has cost. When I sort on B I need the associated drug name in A sort with it. This is beyond me.

 

[shg]

Did you try my suggestion?

 

[Marcelo Branco]

Instead of

Me.Sort.SortFields.Add Key:=Range("B1:B300"), SortOn:=xlSortOnValues, Order:=xlDecending, DataOption:=xlSortNormal

Try

Me.Sort.SortFields.Add Key:=Range("B1:B300"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

As shg suggested, using Option Explicit makes the things easier

M.

 

[JMC57]

Thanks it worked. I need to brush up on my spelling. I did not have to use Option Explicit

 

[JMC57]

Also col A has Drug name and col B has cost. When I sort on B descending I need the associated drug name in A sort with it. For example A1= Aspirin B1=$2.00, A2= Vitamin C B2=$4.00, A3= Tylenol B3=$3.00 should return

A1= Vitamin C B1=$4.00, A2= Tylenol B2=$3.00, A3= Aspirin B3=$2.00

 

[JMC57]

Did you try my suggestion?

I tried but continued to miss the spelling error. Thanks for your help

<TABLE class=tborder style="BORDER-TOP-WIDTH: 0px" cellSpacing=1 cellPadding=6 width="100%" align=center border=0><TBODY><TR title="Post 3130979" vAlign=top><TD class=alt1>Also col A has Drug name and col B has cost. When I sort on B descending I need the associated drug name in A sort with it. For example A1= Aspirin B1=$2.00, A2= Vitamin C B2=$4.00, A3= Tylenol B3=$3.00 should return

A1= Vitamin C B1=$4.00, A2= Tylenol B2=$3.00, A3= Aspirin B3=$2.00 </TD></TR><TR><TD class=thead colSpan=2>Today 07:00 AM</TD></TR></TBODY></TABLE>

 

[shg]

.SetRange Range("[COLOR=red]A[/COLOR]1:E300")

 

[JMC57]

.SetRange Range("[COLOR=red]A[/COLOR]1:E300")

Thank you, thank you and thank you! By the way it works