Number ranges

[stenlake1]

Hi all,

Bit of a tough one and not sure if anyone can help. See the attached spreadsheet.

In column C ("space") is a range of numbers from -13 to 179. What I need to do is to look at the top (-13) and then the bottom number (179) and create a list below (as is red on the excel spreadsheet).

However, as you will see by the red listing, the spacing of the numbers is every 0.5 - and I would also like a way of the user being able to determine this spacing. The first bit is the priority - so can anyone point me in the right direction?

Many thanks,

Chris

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "C"
Dim i As Double
Dim j As Long
Dim iLastRow As Long
Dim nStart As Double
Dim nEnd As Double
Dim nIncrement As Double


With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
nStart = .Cells(2, TEST_COLUMN).Value
nEnd = .Cells(iLastRow, TEST_COLUMN).Value

If IsNumeric(nStart) And IsNumeric(nEnd) Then
nIncrement = InputBox("Supply step value")

If IsNumeric(nIncrement) Then
j = iLastRow + 1

For i = nStart To nEnd Step nIncrement
.Cells(j, TEST_COLUMN).Value = i
j = j + 1
Next i
Else
MsgBox "Invalid step value"
End If

Else
MsgBox "Invalid start/end value"
End If
End With
End Sub


Edited 15-Jun-07 by geekgirlau. Reason: post went crazy - competing for world's longest single line of code!!!

[stenlake1]

Thank you so much.

One little problem...

For i = nStart To nEnd
Step nIncrement.Cells(j, TEST_COLUMN).Value = i
j = j + 1

It keeps returning nIncrement as a "invalid qualifier" - any ideas?

thanks

[Bob Phillips]

Try again



Public Sub ProcessData()
Const TEST_COLUMN As String = "C"
Dim i As Double
Dim j As Long
Dim iLastRow As Long
Dim nStart As Double
Dim nEnd As Double
Dim nIncrement As Double

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
nStart = .Cells(2, TEST_COLUMN).Value
nEnd = .Cells(iLastRow, TEST_COLUMN).Value
If IsNumeric(nStart) And IsNumeric(nEnd) Then

nIncrement = InputBox("Supply step value")
If IsNumeric(nIncrement) Then

j = iLastRow + 1
For i = nStart To nEnd Step nIncrement

.Cells(j, TEST_COLUMN).Value = i
j = j + 1

Next i
Else

MsgBox "Invalid step value"
End If
Else

MsgBox "Invalid start/end value"
End If
End With

End Sub