a)

Function BKN(rngAs Range)
BKN = rng.Count -2
End Function

Call like so =BKN(A345:A432)

b) You can't use this method as a worksheet function, because as soon as you
enter the formula, the selection becomes that cell. If you want to use it in
VBA, it's as simple as

Function BKN()
BKN = Selection.Count -2
End Function

You don't need to worry about Finish and Start Dates, as you state that
these will always be the first and last, ans all between wil be blank, so
the count of blanks is the count of all cells - 2

--

HTHls, then

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"inigo45 " wrote in message
...
Hallo,
Newbie in VBA:

I want to run a macro for a calculation in either the one way a) or the
other way b) as follows:

macro a)

description: I select a range (e.g. A5:A55, this is always different,
could be A345:A432). The first value is FinishDay, the last value is
StartDay, the cells between are all blank and should be counted.

What I did (with no result) is:

Function BKN(StartDay As Integer, FinishDay As Integer, CountBlank As
Range)
BKN = (FinishDay - StartDay) / (Countblank - 1)
End Function

What is the right way?

macro b)
I would prefer that, but is probably not that easy. Just select a range
and let the macro look for the FIRST and LAST value and do the same as
above.

Cheers
Juergen


---
Message posted from http://www.ExcelForum.com/