Auto-Numbering

[starguy]

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

 

[Ardus Petus]

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" <[email protected]> a écrit
dans le message de
news:[email protected]...

 

[starguy]

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]

I don't think you can do this with a function

 

[broro183]

Hi Starguy,

Try entering this forrmula into cell M5 & copying down:
=IF(L5>0,MAX($M$4:M4)+1,0)

note: the dollar sign is only included in the first part of the max
function - this locks the starting point of the range to check at M4
(if M5 was used a circular reference would be created).

The above formula will enter a zero in any row of col M where col L has
no value, to make it appear that col M is empty on these rows use:
=IF(L5>0,MAX($M$4:M4)+1,"")

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Ardus Petus]

Your motto came true!

--
AP

"broro183" <[email protected]> a écrit
dans le message de
news:[email protected]...

 

[broro183]

Beauty
Pleased I could help.

I use it for numbering instruction sheets, as I find it's quicker to
correct than having to reautofill etc after deciding another step needs
to be added in for end users' clarity.

Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Guest]

Hi Starguy,


OK, well, it really depends what you want to do and from the information
below it doesn't appear to clear. So I will give you a few options. If you
don't want to count the zero's but count the number of cells with data above
zero in it, you can use the following formula:

=countif(L5:L15,">0")

if you are wanting to calculate the results in a manner that you don't want
the zero's to affect the total (say for example, mean calculations, medium)
then you could employ an if statement as follows:

=if(<your_formula>=0,"",<your_formula>)

N.B. <your_formula> is the existing formula in the cell range L5:L15.

Auto Numbering, you could use an if statement in combination with the above
as follows:

in row J5:J15 have the following:

for cell J5 => =countif(L5,">0")

then in row K5:K15 have the following:

=if(<your_formula>=0,"",sum(J5:<current J cell>)

For more intuitive examples, it might be good to post examples of the
formulae that you are using in the cell range, data isn't necessary, just the
formula.

Hope that helps.


Franksta.

 

[Guest]

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.

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" <[email protected]> a écrit
dans le message de

 

[Guest]

have a suggestion, you can check your previous post for this.


Thanks,

Franksta.

 

[starguy]

thank you all for your suggestions
i found Broro's formulas helpful

thank you all again for contributing.