CountBlank in calculation
 

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

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

CountBlank in calculation
 

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/

CountBlank in calculation
 

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"?

CountBlank in calculation
 

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

Daniel Brown wrote in message
...
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"?

CountBlank in calculation
 

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

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

CountBlank in calculation
 

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 " wrote in message
...
Bob Phillips Thanks for your reply. How is your suggested function
implimented in my calculation function (which is shortend like
y=(b-a)/x, where x is your suggestion).

Could it be:
DarkRed
function BKN(FinishDay as Integer, StartDay as Integer, CountBlank as
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 (as
Range)?

Cheers
Juergen


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

CountBlank in calculation
 

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).

He (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

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

CountBlank in calculation
 

Juergen,

Are you ready for this?

=(INDIRECT(CHAR(COLUMN($B$1)+64)&MAX(ROW(1:100)*(N OT(ISBLANK(B1:B100)))))-IN
DIRECT("B"&MIN(IF(NOT(ISBLANK(B1:B100)),ROW(B1:B10 0)))))/(INDEX(A1:A100,MATC
H(INDIRECT(CHAR(COLUMN($B$1)+64)&MAX(ROW(1:100)*(N OT(ISBLANK(B1:B100))))),B1
:B100,0),1)-INDEX(A1:A100,MATCH(INDIRECT("B"&MIN(IF(NOT(ISBLAN K(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)

"inigo45 " wrote in message
...
Bob Phillips Thanks for your reply.
I work with a worksheet with four columns A (for date), B (for day), C
(for night), D (for gas). My entries are not every day so these entries
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).

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

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

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


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