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

 

[JE McGimpsey]

One way:


Public Sub FillIntegralSeries()
Const sTITLE As String = "Fill Series"
Dim vResult1 As Variant
Dim vResult2 As Variant
Dim nNumRows As Long
Dim bValid As Boolean
vResult1 = Application.InputBox( _
Prompt:="Enter starting number:", _
Default:=1, _
Title:=sTITLE, _
Type:=1)
'Check if user cancelled
If TypeName(vResult1) = "Boolean" Then Exit Sub
Do
bValid = True
vResult2 = Application.InputBox( _
Prompt:="Enter ending number (greater than " & _
vResult1 & "):", _
Default:=vResult1 + 1, _
Title:=sTITLE, _
Type:=1)
'Check if user cancelled
If TypeName(vResult2) = "Boolean" Then Exit Sub
nNumRows = vResult2 - vResult1 + 1
If nNumRows > ActiveSheet.Rows.Count Then
bValid = False
MsgBox "Ending value too large"
ElseIf nNumRows < 0 Then
bValid = False
MsgBox "Ending value must be less than start value"
End If
Loop Until bValid
With Range("A1:B1")
.Item(1).Value = vResult1
.Item(2).Value = 1
.AutoFill _
Destination:=.Resize(nNumRows, 2), _
Type:=xlFillSeries
End With
End Sub