Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
stop macro if no SQL connection found | Excel Programming | |||
Stop a Macro if no Data found | Excel Programming | |||
Autoformat numbering...how to stop it | Excel Discussion (Misc queries) | |||
Stop the loop when found. | Excel Programming | |||
How to stop sequential numbering | Excel Programming |