I have read a lot, but still can't figure out the best way to do this:

I have 60 cells aligned horizontally: (Jan-05 to Dec-09)
Above the dates I have a project cash flow.

First I need to report the date at the first occurence of cash flow (a
negative number).

Secondly, I need to report the date at the last occurence of cash flow
(a positive number).

This is so I could set up for the XIRR function. The problem is that
the cash flow appears in various dates and durations based on the
project parameters, so I can't lock the XIRR calc to specific cells.

Any help or guidance is greatly appreciated!

Scan the row from left to right and look for the first value less than zero,
then continue to scan for the last value greater than zero

So assuming the values are stored in row A column 1 to 60, after you run it
xneg will contain the column number with the first negative value (or zero
if there is not one) and xpos will contain the last postive value (or zero
if there is none or if there is no negative value found).

Dim xc as integer, xneg as integer, xpos as integer
xneg = 0 : xpos = 0
for xc = 1 to 60
if cells(1,xc) < 0 And xneg = 0 then xneg = xc
if cells(1,xc) 0 And xneg 0 then xpos = xc
next

Hope this provides a template to work with.

--
Cheers
Nigel



wrote in message
oups.com...
I have read a lot, but still can't figure out the best way to do this:

I have 60 cells aligned horizontally: (Jan-05 to Dec-09)
Above the dates I have a project cash flow.

First I need to report the date at the first occurence of cash flow (a
negative number).

Secondly, I need to report the date at the last occurence of cash flow
(a positive number).

This is so I could set up for the XIRR function. The problem is that
the cash flow appears in various dates and durations based on the
project parameters, so I can't lock the XIRR calc to specific cells.

Any help or guidance is greatly appreciated!