First cell with value in a range

[Tanya Addison]

how do i find the position of the first cell that contains a value in a range ?
the range is a single row of approximately 20 cells. Some cells will blank

 

[Aladin Akyurek]

=MATCH(1,INDEX((A3:T3<>"")+0,1,0),0)

 

[Andrew Poulsom]

In VBA?

Sub Test()
    If Not IsEmpty(Range("A1")) Then
        MsgBox Range("A1").Address(False, False) & " is the first cell"
    Else
        MsgBox Range("A1").End(xlDown).Address(False, False) & " is the first cell"
    End If
End Sub

 

[YanukA]

amazing!
i wish i could understand whats behind this formula...
i mean, why "1" as the match value?
and why do you add 0 to the index array?

 

[Mike Szczesny]

Can you use something like this? =index(match(true,a3:t3<>"",0)). You need to us Cntrl+Shift+Enter. Hope this helps.

 

[Aladin Akyurek]

amazing!
i wish i could understand whats behind this formula...
i mean, why "1" as the match value?
and why do you add 0 to the index array?

In...

=MATCH(1,INDEX((A3:T3<>"")+0,1,0),0)

The INDEX((A3:T3<>"")+0,1,0) bit unfolds something like this:

INDEX({FALSE,FALSE,TRUE,FALSE,...}+0,1,0)

>>

INDEX({0,0,1,0,...},1,0) [ FALSE+0 >> 0; TRUE+0 >> 1 ]

{0,0,1,0,...} stands as it were for a range, 1 for the first row of that range, and 0 for all columns of that range.

The surrounding MATCH picks out the column of the first occurrence of 1 in the foregoing calculated reference. Note that this is still an array-processing formula, but it needs not the control+shift+enter signal because the array is already calculated by the INDEX expression.

For the record, MATCH(TRUE,A3:T3>0,0) is equivalent and requires the array-processing signal.

 

[YanukA]

Thank you very much!

 

[Aladin Akyurek]

Thank you very much!

You are welcome.