I would like to create a UDF to return the "position" (#)
of an item in a list, if the list "was" sorted
alphabetically.

OR

I have not found an existing combination of Excel
functions that can do this if there is let me know as well.

I am just learning VBA and not quite able to do this on my
own yet. I appreciate the help.

Hi
if your list is in A1:A100 try the following function (non VBA
solution):
=MATCH(value,A1:A100,0)
this returns the index number of 'value' in this list

--
Regards
Frank Kabel
Frankfurt, Germany


cae wrote:
I would like to create a UDF to return the "position" (#)
of an item in a list, if the list "was" sorted
alphabetically.

OR

I have not found an existing combination of Excel
functions that can do this if there is let me know as well.

I am just learning VBA and not quite able to do this on my
own yet. I appreciate the help.

Look at the MATCH() function. More details in HELP

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"cae" wrote in message
...

I would like to create a UDF to return the "position" (#)
of an item in a list, if the list "was" sorted
alphabetically.

OR

I have not found an existing combination of Excel
functions that can do this if there is let me know as well.

I am just learning VBA and not quite able to do this on my
own yet. I appreciate the help.

Sorry, I should of been more specific.

The current list is "NOT" alphabetic, I hope that the UDF
could tell me the Position # if the list was sorted
Alphabetic.

Currently the NON-Alphabetic list is "Dynamic/changebale"
and generated by formulas, I hope that on a second sheet I
can create an equivalent Alphabetic listing that updates
automatically using the UDF formula in a column on the
first page with a Vlookup on the second page to create
Alpha list)

Thanks

-----Original Message-----

I would like to create a UDF to return the "position" (#)
of an item in a list, if the list "was" sorted
alphabetically.

OR

I have not found an existing combination of Excel
functions that can do this if there is let me know as
well.

I am just learning VBA and not quite able to do this on
my
own yet. I appreciate the help.
.

I have noticed the possibility of comparing strings with and < an
strangely this seems to work. This macro runs down column A and pic
out the highest "value" string.

'-----------------------------------------
Sub test()
Dim MyString As String
Dim Rw As Long
'---------------------
MyString = ""
Rw = 1
'---------------------
While ActiveSheet.Cells(Rw, 1).Value < ""
If ActiveSheet.Cells(Rw, 1).Value MyString Then
MyString = ActiveSheet.Cells(Rw, 1).Value
End If
Rw = Rw + 1
Wend
MsgBox (MyString)
End Sub
'----------------------------------------

--
Message posted from http://www.ExcelForum.com