VBA to number rows in a column but starting from a specific number

[GKDVBA]

Hi there

I would like to create a vba script when ran an input.box comes up and asks you to "enter number you want to start numbering from" and when the number is entered in the input box. e.g "7654", excel starts numbering each cell in column A from 7654 until there is an empty cell in column B.

Thanks

 

[VoG]

Try

Sub test()
Dim x
Dim LR As Long, i As Long
x = Application.InputBox("Enter start number", 1)
If TypeName(x) = "Boolean" Then Exit Sub
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    Range("A" & i).Value = x + i - 1
Next i
End Sub

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG10Mar13
[COLOR="Navy"]Dim[/COLOR] Message, Title, Default, MyValue
Message = "Enter Fisrt Value"
Title = "Auto Number"
Default = "1"
MyValue = InputBox(Message, Title, Default)
[COLOR="Navy"]With[/COLOR] Range("A1")
    .value = MyValue
    .AutoFill Destination:=Range("A1:A" & Range("B1").End(xlDown).Row), Type:=xlFillSeries
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[GKDVBA]

This works great, thanks. However, column A is labelled 'ID' and when running these macros it over writes cell A1, is it possible to start the numbers in the next empty cell in column A?

 

[VoG]

Try

Sub test()
Dim x
Dim LR As Long, i As Long, LR2 As Long
x = Application.InputBox("Enter start number", 1)
If TypeName(x) = "Boolean" Then Exit Sub
LR = Range("B" & Rows.Count).End(xlUp).Row
LR2 = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR - LR2
    Range("A" & LR2 + i).Value = x + i - 1
Next i
End Sub

 

[GKDVBA]

this is fantastic. thank you

 

[GKDVBA]

If i wanted to do the same thing but instead of counting from a number entered, e.g'234' but instead copy the same number for each cell. so the number entered in the input box, e.g '234' to repeat in each cell in column C until there is an empty cell in column B and also start this number in the first empty cell in column C

 

[VoG]

Try

Sub test2()
Dim x
Dim LR As Long, LR2 As Long
x = Application.InputBox("Enter number", 1)
If TypeName(x) = "Boolean" Then Exit Sub
LR = Range("B" & Rows.Count).End(xlUp).Row
LR2 = Range("C" & Rows.Count).End(xlUp).Row
Range("C" & LR2 + 1 & ":C" & LR).Value = x
End Sub