Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoNumber
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoNumber
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 "bijan" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoNumber
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoNumber
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. -- Rick (MVP - Excel) "bijan" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoNumber
I'm assuming you meant to write this formula to be placed in E11...
=IF(ROW()<$A$1+10,E10+1,"") -- Rick (MVP - Excel) "Socko" wrote in message ... 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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to set up an AutoNumber in Excel? | Excel Worksheet Functions | |||
Can Excel AutoNumber | Excel Discussion (Misc queries) | |||
lookup and autonumber | Excel Worksheet Functions | |||
autonumber | Excel Discussion (Misc queries) | |||
autonumber values | Excel Worksheet Functions |