Fordraiders
asked on
NUMBERING OF CELLS
EXCEL 2003
I have a row 1 with column headers
Column A(General type) is to be a Sort column.
Column Q is my column i'm using to end the function.
I will always have an entry in column Q.
I'm pressing a command button that creates a quick numnering of cells
XSort .........ITEM
0001 2CVF4
0002 1A223
0003 DERF4
0004 RETE1
etc...
2000 3E2W3
I'm trying to revamp my numbering of column A
it keeps stopping at row 3
Thanks
fordraiders
I have a row 1 with column headers
Column A(General type) is to be a Sort column.
Column Q is my column i'm using to end the function.
I will always have an entry in column Q.
I'm pressing a command button that creates a quick numnering of cells
XSort .........ITEM
0001 2CVF4
0002 1A223
0003 DERF4
0004 RETE1
etc...
2000 3E2W3
I'm trying to revamp my numbering of column A
it keeps stopping at row 3
Thanks
fordraiders
Sub FillNum()
Dim myRange As Range
Set myRange = Cells.Find(What:="*", after:=[Q2], SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Set myRange = Range(Cells(3, 1), Cells(myRange.Row, 1))
With myRange
.NumberFormat = "General"
.FormulaR1C1 = "=""0000""&ROW()-2"
.NumberFormat = "@"
.Value = .Value
End With
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes, but it needs to read all rows in my sheet
I changed the way that MyRange is being set.
See if it helps.
See if it helps.
Sub FillNum()
Dim myRange As Range
Dim lngLastRow As Long
lngLastRow = Range("Q" & CELLS.ROWS.COUNT).End(xlup).row
Set myRange = Range(Cells(3, 1), Cells(lngLastRow , 1))
With myRange
.NumberFormat = "General"
.FormulaR1C1 = "=""0000""&ROW()-2"
.NumberFormat = "@"
.Value = .Value 'The best Paste Special Values :-) I love to see it
End With
End Sub
Rorya.... our solutions are the same, and are posted at same minute... lol
Great minds... :)
ASKER
fernando long time no hear hope you are well?
ASKER
ok ,
The other funky thing this was doing is tagging zeros at the end..
00009
000010
000011
000012
000013
I need the numbering to stay with 5 digits
00009
00010
00011
00012
00013
sorry Thanks...
The other funky thing this was doing is tagging zeros at the end..
00009
000010
000011
000012
000013
I need the numbering to stay with 5 digits
00009
00010
00011
00012
00013
sorry Thanks...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
fernando, Can it start on "00001" instead of "00000"..
Thanks
Thanks
it should be starting at 1....
because the formula being used is row()-2, and it starts on cell A3, so it should start with 1...
are you sure you didnt change anything in the code ?
because the formula being used is row()-2, and it starts on cell A3, so it should start with 1...
are you sure you didnt change anything in the code ?
ASKER
ooops sorry...Thanks again to all
ASKER
Thanks very very much !
at the moment isn't that just reading: Set myRange = Range(Cells(3, 1), Cells(3, 1)) as you are referencing myRange as part of it which is currently on row 3?