thank you Ardus but i want this by using a function because i dont know coding
thank you if you could tell me any function to do this.

"Ardus Petus" wrote:

Insert the wollowing code in your sheet's code (right-click on tab, select
Code)

'--------------------------
Private Sub Worksheet_Calculate()
Const strRngCheck = "L5:L15"
Dim rng As Range
Dim iNum As Long

iNum = 0
For Each rng In Range(strRngCheck)
If rng.Value = 0 Then
rng.Offset(0, 1).Value = ""
Else
iNum = iNum + 1
rng.Offset(0, 1).Value = iNum
End If
Next rng

End Sub
'----------------------------

"starguy" a écrit
dans le message de
...

i have formulas in a range L5:L15 which sometimes return some value and

sometimes zero. i want to give them auto numbers in column M in a way
that it
should only count the cell which has some value.
suppose formula in L5 returns some value, L6 also then L7 & L8 have no

value(but formula persists), cell L9, L10, L11 has values then L12 has
no
value L13, L14 has value and L15 has no value (but it has formula in
it)
values in these cells changes and some goes to zero and some return
values.
now i want to give them Auto Numbers in a way that cells with some
value
should only be considered.

is there any function to solve this problem.
regards


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=526263