Newbie needs macro help Excel 2000

Greetings,

I need a little bit of assistance in creating a macro in Excel 2000, pc
platform.

I would like the macro to ask for a beginning number and an ending number.
The macro would then populate the corresponding number of rows in column A
(original number) and in column B a number starting with 1 and increasing by
1 until the ending number is met.

For example, the macro asks for beginning number (I would enter 123) and
ending number is 130: cell A1 would contain 123, cell A2 contains 124, cell
A3 contains 125, cell A4 contains 126, cell A5 contains 127, cell A6
contains 128, cell A7 contains 129, cell A8 contains 130 (eight rows); cell
B1 would contain 1, cell B2 would contain 2...cell B8 would contain 8.

Is my question clear?

Any and all help/suggestions would be greatly appreciated.

TIA.
Dave

Dave,

Two versions - one that loops, and one that doesn't.

Sub TryNow()
Dim i As Integer
Dim Counter As Integer

Counter = 0

For i = InputBox("Starting Number") To InputBox("Ending number")
Counter = Counter + 1
Cells(Counter, 1).Value = i
Cells(Counter, 2).Value = Counter
Next i
End Sub

Sub TryNow2()
Dim CStart As Integer
Dim CEnd As Integer

CStart = InputBox("Starting Number")
CEnd = InputBox("Ending number")
Range(Cells(1, 1), Cells(CEnd - CStart + 1, 1)).Formula = "=Row()+ " &
CStart & "-1"
Range(Cells(1, 2), Cells(CEnd - CStart + 1, 2)).Formula = "=Row()"
Range(Cells(1, 1), Cells(CEnd - CStart + 1, 2)).Value = _
Range(Cells(1, 1), Cells(CEnd - CStart + 1, 2)).Value
End Sub

HTH,
Bernie
MS Excel MVP

"deb" <david.brewer@uc.edu> wrote in message
news:%23cRgJ6E5EHA.1192@tk2msftngp13.phx.gbl...
> Greetings,
>
> I need a little bit of assistance in creating a macro in Excel 2000, pc
> platform.
>
> I would like the macro to ask for a beginning number and an ending number.
> The macro would then populate the corresponding number of rows in column A
> (original number) and in column B a number starting with 1 and increasing
by
> 1 until the ending number is met.
>
> For example, the macro asks for beginning number (I would enter 123) and
> ending number is 130: cell A1 would contain 123, cell A2 contains 124,
cell
> A3 contains 125, cell A4 contains 126, cell A5 contains 127, cell A6
> contains 128, cell A7 contains 129, cell A8 contains 130 (eight rows);
cell
> B1 would contain 1, cell B2 would contain 2...cell B8 would contain 8.
>
> Is my question clear?
>
> Any and all help/suggestions would be greatly appreciated.
>
> TIA.
> Dave
>
>

The second macro wrapped, so that should be:

Sub TryNow2()
Dim CStart As Integer
Dim CEnd As Integer

CStart = InputBox("Starting Number")
CEnd = InputBox("Ending number")
Range(Cells(1, 1), Cells(CEnd - CStart + 1, 1)).Formula = _
"=Row()+ " & CStart & "-1"
Range(Cells(1, 2), Cells(CEnd - CStart + 1, 2)).Formula = "=Row()"
Range(Cells(1, 1), Cells(CEnd - CStart + 1, 2)).Value = _
Range(Cells(1, 1), Cells(CEnd - CStart + 1, 2)).Value
End Sub

HTH,
Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:equ0s5F5EHA.3648@TK2MSFTNGP11.phx.gbl...
> Dave,
>
> Two versions - one that loops, and one that doesn't.
>
> Sub TryNow()
> Dim i As Integer
> Dim Counter As Integer
>
> Counter = 0
>
> For i = InputBox("Starting Number") To InputBox("Ending number")
> Counter = Counter + 1
> Cells(Counter, 1).Value = i
> Cells(Counter, 2).Value = Counter
> Next i
> End Sub
>
> Sub TryNow2()
> Dim CStart As Integer
> Dim CEnd As Integer
>
> CStart = InputBox("Starting Number")
> CEnd = InputBox("Ending number")
> Range(Cells(1, 1), Cells(CEnd - CStart + 1, 1)).Formula = "=Row()+ " &
> CStart & "-1"
> Range(Cells(1, 2), Cells(CEnd - CStart + 1, 2)).Formula = "=Row()"
> Range(Cells(1, 1), Cells(CEnd - CStart + 1, 2)).Value = _
> Range(Cells(1, 1), Cells(CEnd - CStart + 1, 2)).Value
> End Sub
>
> HTH,
> Bernie
> MS Excel MVP
>
> "deb" <david.brewer@uc.edu> wrote in message
> news:%23cRgJ6E5EHA.1192@tk2msftngp13.phx.gbl...
> > Greetings,
> >
> > I need a little bit of assistance in creating a macro in Excel 2000, pc
> > platform.
> >
> > I would like the macro to ask for a beginning number and an ending
number.
> > The macro would then populate the corresponding number of rows in column
A
> > (original number) and in column B a number starting with 1 and
increasing
> by
> > 1 until the ending number is met.
> >
> > For example, the macro asks for beginning number (I would enter 123) and
> > ending number is 130: cell A1 would contain 123, cell A2 contains 124,
> cell
> > A3 contains 125, cell A4 contains 126, cell A5 contains 127, cell A6
> > contains 128, cell A7 contains 129, cell A8 contains 130 (eight rows);
> cell
> > B1 would contain 1, cell B2 would contain 2...cell B8 would contain 8.
> >
> > Is my question clear?
> >
> > Any and all help/suggestions would be greatly appreciated.
> >
> > TIA.
> > Dave
> >
> >
>
>

Bernie,

Thanks so much for your help...both solutions works like a champ!

Since I am a newbie with Excel macros, I have a couple of followup
questions:
1) How do I store the macro so it is available whenever I open Excel?

2) I assigned a shortcut key to the macro. Is there a way I can also assign
the macro to a toolbar button? How do I make this button available to all
Excel sessions?

Thanks again for your time and patience.
Dave
"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:equ0s5F5EHA.3648@TK2MSFTNGP11.phx.gbl...
> Dave,
>
> Two versions - one that loops, and one that doesn't.
>
> Sub TryNow()
> Dim i As Integer
> Dim Counter As Integer
>
> Counter = 0
>
> For i = InputBox("Starting Number") To InputBox("Ending number")
> Counter = Counter + 1
> Cells(Counter, 1).Value = i
> Cells(Counter, 2).Value = Counter
> Next i
> End Sub
>
> Sub TryNow2()
> Dim CStart As Integer
> Dim CEnd As Integer
>
> CStart = InputBox("Starting Number")
> CEnd = InputBox("Ending number")
> Range(Cells(1, 1), Cells(CEnd - CStart + 1, 1)).Formula = "=Row()+ " &
> CStart & "-1"
> Range(Cells(1, 2), Cells(CEnd - CStart + 1, 2)).Formula = "=Row()"
> Range(Cells(1, 1), Cells(CEnd - CStart + 1, 2)).Value = _
> Range(Cells(1, 1), Cells(CEnd - CStart + 1, 2)).Value
> End Sub
>
> HTH,
> Bernie
> MS Excel MVP
>
> "deb" <david.brewer@uc.edu> wrote in message
> news:%23cRgJ6E5EHA.1192@tk2msftngp13.phx.gbl...
> > Greetings,
> >
> > I need a little bit of assistance in creating a macro in Excel 2000, pc
> > platform.
> >
> > I would like the macro to ask for a beginning number and an ending
number.
> > The macro would then populate the corresponding number of rows in column
A
> > (original number) and in column B a number starting with 1 and
increasing
> by
> > 1 until the ending number is met.
> >
> > For example, the macro asks for beginning number (I would enter 123) and
> > ending number is 130: cell A1 would contain 123, cell A2 contains 124,
> cell
> > A3 contains 125, cell A4 contains 126, cell A5 contains 127, cell A6
> > contains 128, cell A7 contains 129, cell A8 contains 130 (eight rows);
> cell
> > B1 would contain 1, cell B2 would contain 2...cell B8 would contain 8.
> >
> > Is my question clear?
> >
> > Any and all help/suggestions would be greatly appreciated.
> >
> > TIA.
> > Dave
> >
> >
>
>

Dave,

> Since I am a newbie with Excel macros, I have a couple of followup
> questions:
> 1) How do I store the macro so it is available whenever I open Excel?

Store it in your Personal.xls. If you don't have one, record a macro, and
when prompted, choose to store it in Personal.xls.

> 2) I assigned a shortcut key to the macro. Is there a way I can also
assign
> the macro to a toolbar button? How do I make this button available to all
> Excel sessions?

If you rt-click on a commandbar, select "Customise" then choose the
"Commands" tab, then"Macros" under category, you can drag the smiley face to
any commandbar and drop it where you want it. Then right click that button,
and assign a macro to it - from your Personal.xls, preferably. Otherwise,
the file will need to be re-opened when you first click the button.

For more advanced techniques, you can search for "CreateCommandbar" and
Deitrick through google groups, to find macro techniques to create
commandbars that are only open when specific files are open.

> Thanks again for your time and patience.

You're welcome.

HTH,
Bernie
MS Excel MVP

> Dave
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:equ0s5F5EHA.3648@TK2MSFTNGP11.phx.gbl...
> > Dave,
> >
> > Two versions - one that loops, and one that doesn't.
> >
> > Sub TryNow()
> > Dim i As Integer
> > Dim Counter As Integer
> >
> > Counter = 0
> >
> > For i = InputBox("Starting Number") To InputBox("Ending number")
> > Counter = Counter + 1
> > Cells(Counter, 1).Value = i
> > Cells(Counter, 2).Value = Counter
> > Next i
> > End Sub
> >
> > Sub TryNow2()
> > Dim CStart As Integer
> > Dim CEnd As Integer
> >
> > CStart = InputBox("Starting Number")
> > CEnd = InputBox("Ending number")
> > Range(Cells(1, 1), Cells(CEnd - CStart + 1, 1)).Formula = "=Row()+ " &
> > CStart & "-1"
> > Range(Cells(1, 2), Cells(CEnd - CStart + 1, 2)).Formula = "=Row()"
> > Range(Cells(1, 1), Cells(CEnd - CStart + 1, 2)).Value = _
> > Range(Cells(1, 1), Cells(CEnd - CStart + 1, 2)).Value
> > End Sub
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > "deb" <david.brewer@uc.edu> wrote in message
> > news:%23cRgJ6E5EHA.1192@tk2msftngp13.phx.gbl...
> > > Greetings,
> > >
> > > I need a little bit of assistance in creating a macro in Excel 2000,
pc
> > > platform.
> > >
> > > I would like the macro to ask for a beginning number and an ending
> number.
> > > The macro would then populate the corresponding number of rows in
column
> A
> > > (original number) and in column B a number starting with 1 and
> increasing
> > by
> > > 1 until the ending number is met.
> > >
> > > For example, the macro asks for beginning number (I would enter 123)
and
> > > ending number is 130: cell A1 would contain 123, cell A2 contains 124,
> > cell
> > > A3 contains 125, cell A4 contains 126, cell A5 contains 127, cell A6
> > > contains 128, cell A7 contains 129, cell A8 contains 130 (eight rows);
> > cell
> > > B1 would contain 1, cell B2 would contain 2...cell B8 would contain 8.
> > >
> > > Is my question clear?
> > >
> > > Any and all help/suggestions would be greatly appreciated.
> > >
> > > TIA.
> > > Dave
> > >
> > >
> >
> >
>
>

Bernie,

Thanks again for your help.
Dave

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:uZtmUZG5EHA.4004@tk2msftngp13.phx.gbl...
> Dave,
>
> > Since I am a newbie with Excel macros, I have a couple of followup
> > questions:
> > 1) How do I store the macro so it is available whenever I open Excel?
>
> Store it in your Personal.xls. If you don't have one, record a macro, and
> when prompted, choose to store it in Personal.xls.
>
> > 2) I assigned a shortcut key to the macro. Is there a way I can also
> assign
> > the macro to a toolbar button? How do I make this button available to
all
> > Excel sessions?
>
> If you rt-click on a commandbar, select "Customise" then choose the
> "Commands" tab, then"Macros" under category, you can drag the smiley face
to
> any commandbar and drop it where you want it. Then right click that
button,
> and assign a macro to it - from your Personal.xls, preferably. Otherwise,
> the file will need to be re-opened when you first click the button.
>
> For more advanced techniques, you can search for "CreateCommandbar" and
> Deitrick through google groups, to find macro techniques to create
> commandbars that are only open when specific files are open.
>
> > Thanks again for your time and patience.
>
> You're welcome.
>
> HTH,
> Bernie
> MS Excel MVP
>
> > Dave
> > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > news:equ0s5F5EHA.3648@TK2MSFTNGP11.phx.gbl...
> > > Dave,
> > >
> > > Two versions - one that loops, and one that doesn't.
> > >
> > > Sub TryNow()
> > > Dim i As Integer
> > > Dim Counter As Integer
> > >
> > > Counter = 0
> > >
> > > For i = InputBox("Starting Number") To InputBox("Ending number")
> > > Counter = Counter + 1
> > > Cells(Counter, 1).Value = i
> > > Cells(Counter, 2).Value = Counter
> > > Next i
> > > End Sub
> > >
> > > Sub TryNow2()
> > > Dim CStart As Integer
> > > Dim CEnd As Integer
> > >
> > > CStart = InputBox("Starting Number")
> > > CEnd = InputBox("Ending number")
> > > Range(Cells(1, 1), Cells(CEnd - CStart + 1, 1)).Formula = "=Row()+ " &
> > > CStart & "-1"
> > > Range(Cells(1, 2), Cells(CEnd - CStart + 1, 2)).Formula = "=Row()"
> > > Range(Cells(1, 1), Cells(CEnd - CStart + 1, 2)).Value = _
> > > Range(Cells(1, 1), Cells(CEnd - CStart + 1, 2)).Value
> > > End Sub
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > > "deb" <david.brewer@uc.edu> wrote in message
> > > news:%23cRgJ6E5EHA.1192@tk2msftngp13.phx.gbl...
> > > > Greetings,
> > > >
> > > > I need a little bit of assistance in creating a macro in Excel 2000,
> pc
> > > > platform.
> > > >
> > > > I would like the macro to ask for a beginning number and an ending
> > number.
> > > > The macro would then populate the corresponding number of rows in
> column
> > A
> > > > (original number) and in column B a number starting with 1 and
> > increasing
> > > by
> > > > 1 until the ending number is met.
> > > >
> > > > For example, the macro asks for beginning number (I would enter 123)
> and
> > > > ending number is 130: cell A1 would contain 123, cell A2 contains
124,
> > > cell
> > > > A3 contains 125, cell A4 contains 126, cell A5 contains 127, cell A6
> > > > contains 128, cell A7 contains 129, cell A8 contains 130 (eight
rows);
> > > cell
> > > > B1 would contain 1, cell B2 would contain 2...cell B8 would contain
8.
> > > >
> > > > Is my question clear?
> > > >
> > > > Any and all help/suggestions would be greatly appreciated.
> > > >
> > > > TIA.
> > > > Dave
> > > >
> > > >
> > >
> > >
> >
> >
>
>