find cell address of first non empty cell in a row

[Guest]

I'm trying to find the cell address of the first non-empty cell in a row.

This formula gives me the the position within the range of the first non
empty cell but not the cell address

=MATCH(TRUE,H16:AB16<>"",0)

it simply returns and interger such as 4 when the fourth cell in the range
is the first non empty cell.

somehow I need to take that and convert it into a cell address

Thanks,

--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[Guest]

Something like

=IF(COUNTA(H16:AB16)>0,ADDRESS(16,MATCH(TRUE,H16:AB16<>"",0)+7,4,TRUE),"-")

entered as an array formula with Ctrl+Shift+Enter rather than just enter.

Worked for me.

 

[Steve Yandl]

There is probably an easier way but the example below would give you the
address for the first non empty cell in row 5.

________________________________

intRow = 5
If Not Cells(intRow, 1).Text = "" Then
addyRight = Cells(intRow, 1).Address
Else
addyRight = Cells(intRow, 1).End(xlToRight).Address
End If
MsgBox addyRight
__________________________________

Steve

 

[eliano]

Hi Tom.

From the italian NG by fernando cinquegrani:

=INDIRIZZO(16;CONFRONTA("x";A16:O16 & "x";0))

I beg you pardon, but we have no news of Norman Jones from July, 15.
Have you some notice of our friend ?
Excuse me for the trouble and for my poor english. Thanks in advance
Eliano

 

[eliano]

Sorry Tom.
Obviously:

=INDIRIZZO(16;CONFRONTA("x";H16:AB16 & "x";0)+7)

INDIRIZZO = ADDRESS
CONFRONTA = MATCH
Formula Array
( = (,)

Regards
Eliano