Sorting Madness

[JSnow]

I'm using Excel 2003 and sorting rows by one of eight different column
headings. I've placed a transparent shape over each heading and assigned one
of the eight different sort macros to each corresponding column. Columns B,
C, D, H, I, and J work just fine. Columns K and L are giving me strange
output. Here's the macro for column K "FIN IMPACT":

Sub sortImpact()
ActiveSheet.Unprotect
Dim strSrt As Long
strSrt = MsgBox("Do you want to sort your policies by FINANCIAL
IMPACT?", 4 + 32, "Sort Table")
If strSrt = vbYes Then
Range("B2:L101").Sort Key1:=Range("K2"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
ActiveSheet.Protect
End Sub

Instead of listing the data from most expensive to least expensive starting
w/ row 2, the data sorts in the correct order but lists ending on row 101.
For example: if I have two rows, I expect the data to sort on rows 2 & 3.
Instead it sorts on rows 100 & 101 (the last two rows). The only columns
that sort like this are K & L. The only difference between these macros and
others, which work normally, is the sort key range.

 

[Dave Peterson]

Maybe the values in those cells are not really numbers. Try formatting the
cells as General (or anything but text) and reentering the values.

Debra Dalgleish uses the same sort of techique here:
http://contextures.com/xlSort02.html#Rectangles

 

[JSnow]

You are wise, Dave Peterson. Although I'm not at my file (which is at work),
I think you're on to something. Those two pesky columns, K & L, are the only
two in which there are formulas. The sort must consider the "blank" rows
first then consider the rows with an output other than "". The formula for
those cells in columns K & L is something like
"=if(I2="","",sum(C2,D2,E2,I2))".

If anyone has an idea on that, I'm all ears.

 

[Dave Peterson]

If you sort in ascending order, then real numbers comes first, then cells that
evaluate to "" (like your formula), then text, then real empty cells.

You may want to modify your formula so that it evaluates to something that would
sort first (-99999999 maybe???).

Then use format|conditional formatting to hide the values.

Or insert a helper column and use a formula in that column that accomplishes the
same thing:

=if(a1="",-999999,a1)

You can sort the data, hide or delete the column when you're done.

 

[JSnow]

Dave, thanks for your help. I tried it your way this morning but it did some
things with the aesthetics that I didn't like. So, being the good vba
learner that I am, I created the following code:

Dim rw As Integer
rw = Target.Row

Dim kcell As String
Dim lcell As String
kcell = "K" & rw
lcell = "L" & rw

Dim kf As String
Dim lf As String
kf = "=H" & rw & "-I" & rw
lf = "=SUM(E" & rw & ",F" & rw & ",G" & rw & ",I" & rw & ")"

If Target.Column = 2 Then 'if entering an amount for the claim
If Target.Row > 1 Then
If Target.Value <> "" Then
ActiveSheet.Unprotect
Range(kcell).Value = kf
Range(lcell).Value = lf
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
Range(kcell).ClearContents
Range(lcell).ClearContents
ActiveSheet.Protect
End If
End If
End If

Now those cells are empty unless data is input at the beginning of the row.
Clever huh? ;p

 

[Dave Peterson]

You may want to check to see if you're changing more than one cell in column B
(like clearing them or using copy|paste or dragdown or using ctrl-enter).

And you may want to stop the code from calling itself when you change a cell on
the worksheet.

And I'd use .formula instead of .value when working with formulas.

So maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myRngToCheck As Range

With Me
Set myRngToCheck = Intersect(Target, .Range("B2:b" & .Rows.Count))
End With

If myRngToCheck Is Nothing Then
Exit Sub
End If

On Error GoTo ErrHandler:

Application.EnableEvents = False
For Each myCell In myRngToCheck.Cells
With myCell
Me.Unprotect
If Trim(.Text) = "" Then
.Offset(0, 9).Resize(1, 2).ClearContents
Else
.Offset(0, 9).Formula _
= "=" & Me.Cells(.Row, "H").Address(0, 0) _
& "-" & Me.Cells(.Row, "I").Address(0, 0)
.Offset(0, 10).Formula _
= "=sum(" & Me.Cells(.Row, "E").Resize(1, 3).Address(0, 0) _
& "," & Me.Cells(.Row, "I").Address(0, 0) & ")"
End If
Me.Protect
End With
Next myCell

ErrHandler:
Application.EnableEvents = True
End Sub

Dave, thanks for your help. I tried it your way this morning but it did some
things with the aesthetics that I didn't like. So, being the good vba
learner that I am, I created the following code:

Dim rw As Integer
rw = Target.Row

Dim kcell As String
Dim lcell As String
kcell = "K" & rw
lcell = "L" & rw

Dim kf As String
Dim lf As String
kf = "=H" & rw & "-I" & rw
lf = "=SUM(E" & rw & ",F" & rw & ",G" & rw & ",I" & rw & ")"

If Target.Column = 2 Then 'if entering an amount for the claim
If Target.Row > 1 Then
If Target.Value <> "" Then
ActiveSheet.Unprotect
Range(kcell).Value = kf
Range(lcell).Value = lf
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
Range(kcell).ClearContents
Range(lcell).ClearContents
ActiveSheet.Protect
End If
End If
End If

Now those cells are empty unless data is input at the beginning of the row.
Clever huh? ;p