Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 22nd 10, 12:54 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 247
Default Need a UDF to count blank cells

HI , and a good day to all programmers

I need a UDF , which to count non blank cells
above the cell which contain this UDF . When
the UDF find the first non blank cell , to stop
the count .

Example :

A1 - nonblank
A2 - blank
A3 - blank
A4 - blank
A5 - UDF

here , the result of UDF from cell A5 must be 3 .
I need to count only above in the column , not in the
left or right .

Thank you

  #2   Report Post  
Old February 22nd 10, 12:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 247
Default Need a UDF to count blank cells

I need a UDF , which to count non blank cells

* sorry , to count blank cells .
  #3   Report Post  
Old February 22nd 10, 01:50 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default Need a UDF to count blank cells

Hi,

Blank is an often confusing term with regard to Excel and here I've taken
you literally i.e. nothing on the cell, totally empty.

Call with

=countblanks()

Function countblanks()
For x = ActiveCell.Row - 1 To 1 Step -1
If Cells(x, ActiveCell.Column).Formula = vbNullString Then
countblanks = countblanks + 1
Else
Exit Function
End If
Next
End Function

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ytayta555" wrote:

HI , and a good day to all programmers

I need a UDF , which to count non blank cells
above the cell which contain this UDF . When
the UDF find the first non blank cell , to stop
the count .

Example :

A1 - nonblank
A2 - blank
A3 - blank
A4 - blank
A5 - UDF

here , the result of UDF from cell A5 must be 3 .
I need to count only above in the column , not in the
left or right .

Thank you
.

  #4   Report Post  
Old February 22nd 10, 03:02 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,440
Default Need a UDF to count blank cells

After the first line, add:

Dim x as Long
Application.Volatile

The first line because you might get a compile error if you use Option
Explicit, the second because the function will otherwise not recalculate if
you fill a cell above the calling cell afterwards.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike H" wrote in message
...
Hi,

Blank is an often confusing term with regard to Excel and here I've taken
you literally i.e. nothing on the cell, totally empty.

Call with

=countblanks()

Function countblanks()
For x = ActiveCell.Row - 1 To 1 Step -1
If Cells(x, ActiveCell.Column).Formula = vbNullString Then
countblanks = countblanks + 1
Else
Exit Function
End If
Next
End Function

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ytayta555" wrote:

HI , and a good day to all programmers

I need a UDF , which to count non blank cells
above the cell which contain this UDF . When
the UDF find the first non blank cell , to stop
the count .

Example :

A1 - nonblank
A2 - blank
A3 - blank
A4 - blank
A5 - UDF

here , the result of UDF from cell A5 must be 3 .
I need to count only above in the column , not in the
left or right .

Thank you
.


  #5   Report Post  
Old February 22nd 10, 03:19 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 62
Default Need a UDF to count blank cells

hello,

When using the function, it works the first time.

example:
A1:A10 are "Null"
into A11 put the formula =countblanks()
==the result in A11 is 10

Now insert the value 5 in A5
== the result is still 10.

I added Application.volatile in the code of Mike H
as you have suggested.
With the same example as above the result
becomes 4 (the number of blank cell above A5)

it is perhaps the use of 'ActiveCell' that made this behaviour to occur.

I have changed the code of Mike
and replace 'ActiveCell' with
'Application.Caller'.
It seems to improve the behaviour of the function.

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''
Function countnull()
Dim x
Application.Volatile
If TypeName(Application.Caller) = "Range" Then
For x = Application.Caller.Row - 1 To 1 Step -1
If Cells(x, Application.Caller.Column).Formula = vbNullString Then
countnull = countnull + 1
Else
Exit Function
End If
Next x
End If
End Function
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''





"Niek Otten" a écrit dans le message de groupe de
discussion : ...
After the first line, add:

Dim x as Long
Application.Volatile

The first line because you might get a compile error if you use Option
Explicit, the second because the function will otherwise not recalculate
if you fill a cell above the calling cell afterwards.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike H" wrote in message
...
Hi,

Blank is an often confusing term with regard to Excel and here I've taken
you literally i.e. nothing on the cell, totally empty.

Call with

=countblanks()

Function countblanks()
For x = ActiveCell.Row - 1 To 1 Step -1
If Cells(x, ActiveCell.Column).Formula = vbNullString Then
countblanks = countblanks + 1
Else
Exit Function
End If
Next
End Function

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ytayta555" wrote:

HI , and a good day to all programmers

I need a UDF , which to count non blank cells
above the cell which contain this UDF . When
the UDF find the first non blank cell , to stop
the count .

Example :

A1 - nonblank
A2 - blank
A3 - blank
A4 - blank
A5 - UDF

here , the result of UDF from cell A5 must be 3 .
I need to count only above in the column , not in the
left or right .

Thank you
.




  #6   Report Post  
Old February 22nd 10, 03:44 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 247
Default Need a UDF to count blank cells

On 22 feb., 16:19, "Charabeuh" wrote:
hello,
When using the function, it works the first time.


INDEED . I was busy to check it

I added Application.volatile in the code of Mike H .....


I use this code :

Sub MYMACRO()

Dim myCell As Range
Dim myRng1 As Range
Set myRng1 = Range("A1:E30")

For Each myCell In myRng1.Cells
If myCell < vbNullString Then
myCell = "=countnull()"
End If
Next myCell

End Sub

and now , IT WORKS great .

I have to thank you - to all three - very much ,
that you used and shared your knowledge and time
helping me . I wish you a good week
  #7   Report Post  
Old November 30th 20, 01:47 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default Need a UDF to count blank cells


Hi everybody

I had posted this a while ago , and I get a good function :

Function countnull()
Dim X
Application.Volatile
If TypeName(Application.Caller) = "Range" Then
For X = Application.Caller.Row - 1 To 1 Step -1
If Cells(X, Application.Caller.Column).Formula = vbNullString Then
countnull = countnull + 1
Else
Exit Function
End If
Next X
End If
End Function

this function brings me the value of cells empty
from above , in the same Column ;
Now , I have need this function to do the same task ,
but counting empty cells in the same Row ; I need to count
to the left cells where this function is .

2) .Second ask , I have need to
have a function doing the same task (meaning counting empty
cells ) but to return me the value of empty cells after it find
a specific value , such as - " 3 " .

3) . Well , if it is possible , I really have need and a function to do
the same task like for I asked just above , but to not do
differences beetween empty or non empty cells , just
bring me the value of number of cells passing until
it find value - 3 - .
thank you .
  #8   Report Post  
Old November 30th 20, 01:53 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default Need a UDF to count blank cells

There is no problem if it is not in a code , is not a UDF ,
it is great and if it is done in formulas , too .
  #9   Report Post  
Old November 30th 20, 02:16 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,832
Default Need a UDF to count blank cells

Hi,

Am Mon, 30 Nov 2020 04:47:57 -0800 (PST) schrieb Xxer Xxes:

this function brings me the value of cells empty
from above , in the same Column ;
Now , I have need this function to do the same task ,
but counting empty cells in the same Row ; I need to count
to the left cells where this function is .

2) .Second ask , I have need to
have a function doing the same task (meaning counting empty
cells ) but to return me the value of empty cells after it find
a specific value , such as - " 3 " .

3) . Well , if it is possible , I really have need and a function to do
the same task like for I asked just above , but to not do
differences beetween empty or non empty cells , just
bring me the value of number of cells passing until
it find value - 3 - .


I don't know if I understood your problem correctly.
Have a look:
https://1drv.ms/x/s!AqMiGBK2qniTge9d...95CDA?e=DHuVMz


Regards
Claus B.
--
Windows10
Office 2016
  #10   Report Post  
Old November 30th 20, 08:24 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default Need a UDF to count blank cells

I tried to work with that functions , but I realize i need
3 UDF ;

1) Function countnull()
Dim X
Application.Volatile
If TypeName(Application.Caller) = "Range" Then
For X = Application.Caller.Row - 1 To 1 Step -1
If Cells(X, Application.Caller.Column).Formula = vbNullString Then
countnull = countnull + 1
Else
Exit Function
End If
Next X
End If
End Function

what is doing this udf in a column , to do it in a row , counting
in the same row , on left side ;

2) second udf : instead to count empty cells ( untill udf find a cell non empty)
and return number of count empty cells , to do tha same loop and count
untill find value 3 ( number three )
- - - - -
i.ll try to make some exemples ;
for 1 ask): now , this formula return so :
in Column C , cell C1 for eg i have value 1 ; C2 ,C3 ,C4 , C5 are empty :introduceing in cell
C5 my actually udf , returns value 4 , and is correct ; it ehows me there are 4 cells
"consecutivelly " empty , before to find the cell C1 , which is not empty ;
well ,now i need to do tha same task , in a Row , not in a column , as i said ,
"to the left " . so , the results of this new udf will be :

assume we have in Row10 , column B , we have value 2 ( the mean is cell is not empty );
c10 and d10 is empty ;
in the same row ( we work with only one row in this udf ) , Column E ,we put this new formula ;
the result must to be 2 ; if in cell d10 is any value , in e10 the result displaied will be 0
( zero ) .
- - - - - -
For second demand ) : udf to count untill find a value , 3 for eg. ignoring wether cells are
empty or not




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to count#cells w/= value in other column and not count blank c aganoe Excel Worksheet Functions 4 April 9th 10 11:36 AM
count blank cells to next value Malika Excel Discussion (Misc queries) 7 March 29th 10 04:36 PM
Count from Blank & Non-Blank Cells Mandeep Dhami Excel Discussion (Misc queries) 4 February 12th 08 04:25 PM
Count the non blank cells vijaydsk1970 Excel Worksheet Functions 3 November 10th 06 01:04 PM
Count the non blank cells vijaydsk1970 Excel Worksheet Functions 1 November 9th 06 03:25 PM


All times are GMT +1. The time now is 09:12 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017