Accessing result of sort

G

Google user

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
 
B

Bob Phillips

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)
 
Top