macro to stop numbering when a letter is found in cell
 

i need filtered files to be numbered... with a twist...
i need it to stop numbering if it incounters a letter or * in the numbering
row.
end user needs EASY, so
i have a macro that i found here, (although i cant seem to find it again...)
love it but cant get it to stop

Sub numbervisible()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set myrng = Range("a2:a" & lr).SpecialCells(xlCellTypeVisible)
counter = 1
For Each c In myrng
c.Value = counter
counter = counter + 1
Next c
End Sub

i can put this code or one simular in the personal.xls so all end user needs
to do is run it...

macro to stop numbering when a letter is found in cell
 

You could try something like this

Sub numbervisible()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set myrng = Range("a2:a" & lr).SpecialCells(xlCellTypeVisible)
counter = 1
For Each c In myrng
If Not IsNumeric(c.Value) And c.Value < 0 Then
c.Value = counter
counter = counter + 1
End If
Next c
End Sub

The < 0 is because VBA will read a zero value as numeric and Excel equates
blank and "" as zero.


"cosmo37" wrote in message
...
i need filtered files to be numbered... with a twist...
i need it to stop numbering if it incounters a letter or * in the
numbering
row.
end user needs EASY, so
i have a macro that i found here, (although i cant seem to find it
again...)
love it but cant get it to stop

Sub numbervisible()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set myrng = Range("a2:a" & lr).SpecialCells(xlCellTypeVisible)
counter = 1
For Each c In myrng
c.Value = counter
counter = counter + 1
Next c
End Sub

i can put this code or one simular in the personal.xls so all end user
needs
to do is run it...

macro to stop numbering when a letter is found in cell
 

great... THANK YOU!!!! (the end user was told by our IT department and our
resident Excel guru that it could not be done)

although it did the opposite of what i needed, (i am sure i confused you)
but an easy fix... for the next guy...
to get the row numbered without changing any alphas, or *...

Sub numbervisible2()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set myrng = Range("a2:a" & lr).SpecialCells(xlCellTypeVisible)
counter = 1
For Each c In myrng
If IsNumeric(c.Value) Then
c.Value = counter
counter = counter + 1
End If
Next c
End Sub

and if you also do not want blanks #'rd add the rest of this line from JLGWhiz
If IsNumeric(c.Value) And c.Value < 0 Then
thanks again!

"JLGWhiz" wrote:

You could try something like this

Sub numbervisible()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set myrng = Range("a2:a" & lr).SpecialCells(xlCellTypeVisible)
counter = 1
For Each c In myrng
If Not IsNumeric(c.Value) And c.Value < 0 Then
c.Value = counter
counter = counter + 1
End If
Next c
End Sub

The < 0 is because VBA will read a zero value as numeric and Excel equates
blank and "" as zero.


"cosmo37" wrote in message
...
i need filtered files to be numbered... with a twist...
i need it to stop numbering if it incounters a letter or * in the
numbering
row.
end user needs EASY, so
i have a macro that i found here, (although i cant seem to find it
again...)
love it but cant get it to stop

Sub numbervisible()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set myrng = Range("a2:a" & lr).SpecialCells(xlCellTypeVisible)
counter = 1
For Each c In myrng
c.Value = counter
counter = counter + 1
Next c
End Sub

i can put this code or one simular in the personal.xls so all end user
needs
to do is run it...