how to find relative posiition of first and last non-blank cell in a range?

[jarahsang]

Hello,

I have a rows of data in which I want to get, for each row, th
position (not the value) of the first and last non-blank cell in th
row.

Basically i'm looking at hours worked by employees (employees are i
rows) and columns have the week (week 1 , week 2 , week 3). I want t
get the start and end duration of each employee.

so if employee A works 0 hours in week 1 , 3 hours for week 2, 0 hour
for weeks 3-5, 4 hours for week 6 and zero hours for weeks 7-10: I wan
to get the value of 2 for start duration (first non-zero value occur
in week 2), and I want to get value of 6 for end duration (week 6 i
the last non-zero value).

Is there a way to do this using formulas ...or VBA? I have exce
97...

Thanks for any help/tip... hope the example was clear..

 

[BrianB]

Here are 2 custom functions that work as described. Copy/paste to
macro sheet in the workbook and use them in the same way as usual eg
=LASTV(B2:H2).


Code
-------------------
'-------------------------------------
Function FIRSTV(rg As Range)
FIRSTV = 0
For c = 1 To rg.Cells.Count
If rg.Cells(c).Value <> 0 Then
FIRSTV = c
Exit For
End If
Next
End Function
'--------------------------------------
Function LASTV(rg As Range)
LASTTV = 0
For c = rg.Cells.Count To 1 Step -1
If rg.Cells(c).Value <> 0 Then
LASTV = c
Exit For
End If
Next
End Function
'--------------------------------------

 

[jarahsang]

THanks for the code!... after some more exploring...i found out how t
do it using formulas:

for finding first non-blank value (this is an array formula):

=MATCH(TRUE,LEN(X:X)<>0,0)

press Ctrl+shift+enter (array formula) and X:X is the range you'r
looking at.

for finding last non-blank value (numeric):

=MATCH (9.999999E+306,X:X)

where X:X is the range you're looking at...hope this helps