find cell address of first non empty cell in a row

B

BillyRogers

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
 
T

Tom Ogilvy

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.
 
S

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
 
E

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
 
E

eliano

Sorry Tom.
Obviously:

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

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

Regards
Eliano
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top