Newbie needs macro help Excel 2000

[deb]

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 Deitrick]

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

 

[Bernie Deitrick]

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

 

[deb]

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]

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

 

[deb]

Bernie,

Thanks again for your help.
Dave

Dave,


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.


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.


You're welcome.

HTH,
Bernie
MS Excel MVP