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

On Thu, 31 Jul 2003 01:08:18 -0700, "Richard"
wrote in microsoft.public.excel.programming:

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

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

--
Michael Bednarek, IT Manager, Tactical Global Management
Waterfront Pl, Brisbane 4000, Australia. "POST NO BILLS"
http://mcmbednarek.tripod.com/

Thanks Chrissy for your prompt reply. Looks the money.
Kind regards,
Richard

-----Original Message-----
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


.

Thanks Michael,
Appreciate the comment but a macro would be more suitable
for my application at this point.
Kind regards,
Richard

-----Original Message-----
On Thu, 31 Jul 2003 01:08:18 -0700, "Richard"

wrote in microsoft.public.excel.programming:

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

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

--
Michael Bednarek, IT Manager, Tactical Global Management
Waterfront Pl, Brisbane 4000, Australia. "POST NO BILLS"
http://mcmbednarek.tripod.com/
.

I take it you noticed I got it wrong and used A13 and not A14.

Chrissy.

Richard wrote
Thanks Chrissy for your prompt reply. Looks the money.
Kind regards,
Richard

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

-----Original Message-----
I take it you noticed I got it wrong and used A13 and not
A14.

Chrissy.

Richard wrote
Thanks Chrissy for your prompt reply. Looks the money.
Kind regards,
Richard


.