We help IT Professionals succeed at work.

excel macro

cmoore1
cmoore1 asked
on
I'm looking for a macro to fill a column with incrementing numbers. starting it with the value of the current cell then moving down the spread sheet incrementing the the by 1 with each move until i reach a predetermined number.
Comment
Watch Question

This will stop at 50:
ActiveCell.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=50

Open in new window

Excel VBA Developer
Top Expert 2014
Commented:
This will prompt for the predetermined number, increment subsequent cells by one until that number is reached - unless the predetermined number is less than the value of the starting cell (which will halt the macro)
Sub Increment()
    Dim intFinal As Integer
    intFinal = Application.InputBox("Enter Final Value:", , , , , , , 1)
    If intFinal > ActiveCell.Value Then
        ActiveCell.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=intFinal
    End If
End Sub

Open in new window

Author

Commented:
I must need more than this, I get a compiler erro "expected:named parameter" with tpye highlighted
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
That error would occur if the command in line 5 of my code was split up to two lines like so:
 
ActiveCell.DataSeries Rowcol:=xlColumns,
   Type:=xlLinear, Step:=1, Stop:=intFinal

Open in new window


If you need to format your code like that, add an underscore at the end of the first line so that the compiler will continue reading to the next line, as here:

 
ActiveCell.DataSeries Rowcol:=xlColumns, _
   Type:=xlLinear, Step:=1, Stop:=intFinal

Open in new window


If this still doesn't resolve your issue, let us know.

-Glenn
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
Here is alternate code if that doesn't work.
 
Sub Increment()
    Dim x, intFinal As Integer
    intFinal = Application.InputBox("Enter Final Value:", , , , , , , 1)
    If intFinal > ActiveCell.Value Then
        For x = ActiveCell.Value + 1 To intFinal
            ActiveCell.Offset(x - ActiveCell.Value, 0).Value = x
        Next x
    End If
End Sub

Open in new window


Author

Commented:
Glen
running this galso gives me an error user-defined type not defined.
Highlighting " intFinal As Integer"

Author

Commented:
Glen
running this alternate code also gives me an error user-defined type not defined.
Highlighting " intFinal As Integer"
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
You probably need to Repair or re-install Excel then.  These definitions are part of the Excel object library reference and shouldn't give you any errors whatsoever.  I can't think of any other reason why this wouldn't work.

-Glenn

Author

Commented:
Thanks,
I will try it. I'm out of town currently. I'll try this when I get back.
Thanks again.
Charlie