Solved

NUMBERING OF CELLS

Posted on 2011-03-23
186 Views
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
``````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
``````
0
Question by:fordraiders
• 6
• 4
• 2
• +1

LVL 17

Expert Comment

ID: 35198660
The line: Set myRange = Range(Cells(3, 1), Cells(myRange.Row, 1))

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?
0

LVL 85

Assisted Solution

Rory Archibald earned 250 total points
ID: 35198677
Something like:
``````Sub FillNum()
Dim myRange As Range , lngLastRow as long
lnglastrow = cells(rows.count, "Q").End(xlup).Row
Set myRange = Range(Cells(3, 1), Cells(lnglastrow, 1))
With myRange
.NumberFormat = "General"
.FormulaR1C1 = "=""0000""&ROW()-2"
.NumberFormat = "@"
.Value = .Value
End With
End Sub
``````
0

LVL 3

Author Comment

ID: 35198679
yes, but it needs to read all rows in my sheet
0

LVL 6

Expert Comment

ID: 35198685
I changed the way that MyRange is being set.
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
``````
0

LVL 6

Expert Comment

ID: 35198697
Rorya.... our solutions are the same, and are posted at same minute... lol
0

LVL 85

Expert Comment

ID: 35198708
Great minds... :)
0

LVL 3

Author Comment

ID: 35198750
fernando long time no hear hope you are well?
0

LVL 3

Author Comment

ID: 35198817
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...

0

LVL 6

Accepted Solution

FernandoFernandes earned 250 total points
ID: 35198834
I'm ok thanks for asking... actually I tend to come and go every now and then ... :)
I am addicted to forums :-)

``````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 = "=TEXT(ROW()-2,""00000"")"
.NumberFormat = "@"
.Value = .Value 'The best Paste Special Values :-) I love to see it
End With
End Sub
``````
0

LVL 3

Author Comment

ID: 35199027
fernando, Can it start on "00001" instead of "00000"..

Thanks
0

LVL 6

Expert Comment

ID: 35199054
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 ?
0

LVL 3

Author Comment

ID: 35199086
ooops sorry...Thanks again to all
0

LVL 3

Author Closing Comment

ID: 35199102
Thanks very very much !
0

Featured Post

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,â€¦
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is aâ€¦
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo â€¦