Macro to insert numbers

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

I use a column A3 to A14 into which I insert consecutive
numbers using individual macros. This is not the ideal way
to do it. Could anyone help with a macro that allows me to
have a message box asking how many records I need to have
and then the macro inserts the chosen number in the cells
starting from A3. Say I pick five, then the macro would
just insert the numbers 1,2,3,4 and 5 in cells A3,A4,A5,A6
and A7. The macro would need to clear the range A3:A14
first so as not to leave any previously entered numbers.
I've had a go at this myself but failed to get it to work.

Thanks for any assistance.
Richard
 
Sub InsertNumbers()
Dim X As String
Dim Y As Integer
Dim Z As Integer

ActiveSheet.Range("A3:A13").ClearContents

X = InputBox("How many numbers do you want", "Enter a number", "1")

If IsNumeric(X) Then Y = CInt(X) Else Y = 0

If Y < 0 Then Y = 0
If Y > 11 Then Y = 11

For Z = 1 To Y
Range("A3").Offset(Z - 1, 0) = Z
Next Z

End Sub

Richard wrote
 
I use a column A3 to A14 into which I insert consecutive
numbers using individual macros. This is not the ideal way
to do it. Could anyone help with a macro that allows me to
have a message box asking how many records I need to have
and then the macro inserts the chosen number in the cells
starting from A3. Say I pick five, then the macro would
just insert the numbers 1,2,3,4 and 5 in cells A3,A4,A5,A6
and A7. The macro would need to clear the range A3:A14
first so as not to leave any previously entered numbers.
I've had a go at this myself but failed to get it to work.

Thanks for any assistance.
Richard
Click to expand...

I'm not sure a macro is needed. Have you tried to enter the starting
number in A3 - then select A3:A14 - then Menu: Edit/Fill/Series... ?
 
Thanks Michael,
Appreciate the comment but a macro would be more suitable
for my application at this point.
Kind regards,
Richard
 
I take it you noticed I got it wrong and used A13 and not A14.

Chrissy.

Richard wrote
 
Hi Chrissy,
Yep, but no problem. I'm constantly amazed at how clever
you all are, so a slip like that is nothing. I've used
your code and it is just great.
Again, your help was very much appreciated.
Regards,
Richard
 
Back
Top