Return the first cell reference from a named list

**zinny** · 09-18-2008, 05:31 AM

i'm trying to return the reference of the first value of a named list. For example
i have the list "names" which is from C5:C7.
C
5 Ben
6 John
7 Daniel
8

I have another worksheet which needs to use the cell address of the first name in the
named list "names". ie return "C5". How do i do this?

I Can use INDEX(names,1) to get the value of the first cell but i need the cell reference.

**rwgrietveld** · 09-18-2008, 05:52 AM

How about this:

=ADDRESS(ROW(names);COLUMN(names))
=ADDRESS(ROW(names)+1;COLUMN(names))
=ADDRESS(ROW(names)+2;COLUMN(names))

**zinny** · 09-18-2008, 07:52 AM

works great on the worksheet. however doesn't work in VBA. It says sub or function not defined and points to "Row". my function needs to return a range "ADDRESS(ROW(names),COLUMN(names)):ADDRESS(ROW(names)+2,COLUMN(names))" as a string.
how would i do this in VBA?

**rwgrietveld** · 09-20-2008, 10:43 AM

this is actually very simple

Function ReturnFirstAddressAsString(names As Range) As String
  ReturnFirstAddressAsString = CStr(names.Address) '$C$5:$C$7
  'ReturnFirstAddressAsString = CStr(names.Resize(1, 1).Address) '$C$5
End Function

Call this function as

NameStr = ReturnFirstAddressAsString(range("names"))

**mikerickson** · 09-20-2008, 11:04 AM

In a Spreadsheet formula =OFFSET(myList,0,0,1,1) returns the first cell of myList as a range.
In VB

Range("myList").Cells(1,1)

does the same thing.

If the address of that cell is needed (rather than the cell itself), the spreadsheet function ADDRESS or the property .Address would be used.