AutoNumber

B

bijan

Hi all,
I need a vba code for autonumber cells from E10 to the a variable number
that I insert in A1, for example if A1=20 it fill E10:E29 with 1-20
Thanks in advance
Bijan
 
M

Mike H

Hi,

Try this

Sub Prime_Lending()
lastval = Range("A1").Value
For x = 1 To lastval
Cells(x + 9, 5).Value = x
Next
End Sub


Mike
 
S

Socko

If you can determine what will be the maximum number of rows at any
point in time, then you can use worksheet functions rather than macro,
as in this case you would not need to call a macro. I have tried
something and I believe this can be the solution to the problem.

In Cell E10, enter formula "=IF(ROW()<$A$1+10,1,"")"

Next in Cell E11, enter formula "=IF(ROW()<$A$1+10,1,"")"

Now, copy the formula down till the row number 10000 (assuming that
there would not be any need to have autonumber more than 9991)

I hope this helps

Selva V Pasupathy
visit: http://socko.wordpress.com/
 
R

Rick Rothstein

Here is a different approach for a macro solution (change the sheet name in
the With statement to the worksheet name you want to apply this
functionality to)...

Sub FillRangeE10DownToRowNumberInA1()
Dim CounterCell As Range
With Worksheets("Sheet3")
Set CounterCell = .Range("A1")
With .Range("E10")
.Resize(.End(xlDown).Row - .Row + 1).Clear
.Value = 1
If CounterCell.Value > 1 Then
.Offset(1).Value = 2
If CounterCell.Value > 2 Then
.Resize(2).AutoFill .Resize(CounterCell.Value)
End If
End If
End With
End With
End Sub

Note that the first line after the second With statement clears from E10
down to the last filled cell in that column. This allows you to change A1 to
a lower number than the current last row and the excess currently filled in
numbers will be deleted. In other words, if A1 contains 100 and E10:E109 is
currently filled in, changing A1 to 20 will clear E10:E109 and then fill in
E1:E29 as you wanted. If you won't need this functionality, you can remove
that one statement.

Also note that if you change your mind and want to number from a different
cell, say K5, then just change the "E10" to "K5" in the second With
statement and the code will work fine with the new cell reference. You can
also change the cell you use to keep the counter in from A1 to any other
cell, say for example B2, by changing the "A1" to "B2" in the Set statement.
 
R

Rick Rothstein

I'm assuming you meant to write this formula to be placed in E11...

=IF(ROW()<$A$1+10,E10+1,"")
 
Top