Accessing result of sort
 

G'day!


Basically I'd like to sort a region and then step thru the
sorted region one row at a time, extracting the appropriate
columns. Without any manual manipulations on the original
sheet. No matter how I juggle it, I seem to always come
up with a row count of 1. I must be missing one critical
aspect somewhere.

Here is a simplified version of my UDF:
Function format()
Dim outputGreen As String
Dim outputRed As String

Rows("1:6").Select
Selection.Sort Key1:=Range("B3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

outputGreen = ""
outputRed = ""
For i = 1 To Selection.Rows.Count Step 1
If ("green" = Selection.Cells(i, 2)) Then
outputGreen = outputGreen & "Entry " & Str(i) & " is " &
Str(Selection.Cells(i, 3)) & vbCr
Else
outputRed = outputRed & "Entry " & Str(i) & " is " &
Str(Selection.Cells(i, 3)) & vbCr
End If
Next i

format = outputGreen & vbCr & outputRed
End Function


Worksheet:
green 67
red 37
red 4
green 666
red 2
red 98

Desired result:
Entry 1 is 67
Entry 2 is 666
Entry 3 is 37
Entry 4 is 4
Entry 5 is 2
Entry 6 is 98
(whereas the order within 1-2 and 3-6 is immaterial.)


I've searched all over the 'net and in the help but still
have yet to find anything addressing this exact problem so I
thot I'd try here. I know I'm probably overloading Excel by
trying to do this, but it seemed like an interesting exercise.

I'm using Excel 97/Win98SE and Excel 2002/Win2000.

Thanks in advance for any tips.


James

Accessing result of sort
 

I am afraid your basic premise will not work.

What is happening is that you are calling Format from a worksheet.
Unfortunately, a worksheet function cannot change the cell attributes, just
return a result. So when you select rows 1:6, it doesn't happen, and the
selection remains with the cell that the function is entered in, hence only
1 row.

Similarly, you are not going to be able to sort in a worksheet function.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Google user" wrote in message
om...
G'day!


Basically I'd like to sort a region and then step thru the
sorted region one row at a time, extracting the appropriate
columns. Without any manual manipulations on the original
sheet. No matter how I juggle it, I seem to always come
up with a row count of 1. I must be missing one critical
aspect somewhere.

Here is a simplified version of my UDF:
Function format()
Dim outputGreen As String
Dim outputRed As String

Rows("1:6").Select
Selection.Sort Key1:=Range("B3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

outputGreen = ""
outputRed = ""
For i = 1 To Selection.Rows.Count Step 1
If ("green" = Selection.Cells(i, 2)) Then
outputGreen = outputGreen & "Entry " & Str(i) & " is " &
Str(Selection.Cells(i, 3)) & vbCr
Else
outputRed = outputRed & "Entry " & Str(i) & " is " &
Str(Selection.Cells(i, 3)) & vbCr
End If
Next i

format = outputGreen & vbCr & outputRed
End Function


Worksheet:
green 67
red 37
red 4
green 666
red 2
red 98

Desired result:
Entry 1 is 67
Entry 2 is 666
Entry 3 is 37
Entry 4 is 4
Entry 5 is 2
Entry 6 is 98
(whereas the order within 1-2 and 3-6 is immaterial.)


I've searched all over the 'net and in the help but still
have yet to find anything addressing this exact problem so I
thot I'd try here. I know I'm probably overloading Excel by
trying to do this, but it seemed like an interesting exercise.

I'm using Excel 97/Win98SE and Excel 2002/Win2000.

Thanks in advance for any tips.


James