CountBlank in calculation

[inigo45]

Hallo,
Newbie in VBA:

I want to run a macro for a calculation in either the one way a) or th
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 i
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 A
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 rang
and let the macro look for the FIRST and LAST value and do the same a
above.

Cheers
Juerge

 

[Bob Phillips]

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)

 

[Guest]

When I write the code you provided it gives me an error
"Delete method of Worksheet class failed". Is that because I have to specify the .txt extension at the end of "textImport"?

 

[Tom Ogilvy]

Yes, the sheet would not have the .txt extension. However, since this is
the only sheet in the workbook, you would not want to delete the sheet - a
workbook must have one sheet and if you opened a text file, it would only
have one sheet.

Workbooks("TextImport.Txt").Close Savechanges:=False

would probably be more appropriate.

--
Regards,
Tom Ogilvy

When I write the code you provided it gives me an error
"Delete method of Worksheet class failed". Is that because I have to

specify the .txt extension at the end of "textImport"?

 

[inigo45]

Bob Phillips> Thanks for your reply. How is your suggested functio
implimented in my calculation function (which is shortend lik
y=(b-a)/x, where x is your suggestion).

Could it be:
DarkRed
function BKN(FinishDay as Integer, StartDay as Integer, CountBlank a
Range)
sub function CountBlank()
X=Selection(rng as Range)
end sub
BKN=(FinishDay - StartDay)/(X+1)
end function

Just for reference Count-2 is the same as CountBlank. What is rng (a
Range)?

Cheers
Juerge

 

[Bob Phillips]

Juergen,

If I understand correctly, it would be something like

y = (b-1) /BKN(Activesheet.Range("A1:H10")

I am afraid I don't understand what you are tring to do in your version of
BKN, but you cannot embed a sub within a function.

--

HTH

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

 

[inigo45]

Bob Phillips> Thanks for your reply.
I work with a worksheet with four columns A (for date), B (for day),
(for night), D (for gas). My entries are not every day so these entrie
vary. Example:
5th Feb. 2003 25888 22222 22,88888
6th Feb. 2003
7th Feb. 2003
..
12th Feb. 2003 25989 23333 33,55555.

Now what the macro should do:
1. input StartDay (here 25888),
2. input FinishDay (here 25989),
3. count the cells between StartDay and FinishDay (here seven).

Here: (25989 - 25888) / (7 +1) = _12,625_.

this is BKN=(FinishDay - StartDay)/(????? +1)

????? is the numerical value of the counted cells, which is th
question? How do I involve this in my function resp. in th
calculation?
Any ideas
Cheers Juerge

 

[Bob Phillips]

Juergen,

Are you ready for this?

=(INDIRECT(CHAR(COLUMN($B$1)+64)&MAX(ROW(1:100)*(NOT(ISBLANK(B1:B100)))))-IN
DIRECT("B"&MIN(IF(NOT(ISBLANK(B1:B100)),ROW(B1:B100)))))/(INDEX(A1:A100,MATC
H(INDIRECT(CHAR(COLUMN($B$1)+64)&MAX(ROW(1:100)*(NOT(ISBLANK(B1:B100))))),B1
:B100,0),1)-INDEX(A1:A100,MATCH(INDIRECT("B"&MIN(IF(NOT(ISBLANK(B1:B100)),RO
W(B1:B100)))),B1:B100,0),1)+1)

array formula, so commit with Ctrl-Shift-Enter

--

HTH

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