Create a list of values between 2 numbers with a variable step size

Question

I am trying to list all values between 2 numbers, but the interval for each group varies. For example, I want to list all numbers between (and including) 1 - 5 in increments of 0.5 and all values from 40 - 140 in increments of 10 as shown here below. The total number of inputs will vary (I currently have 15), so I'm trying to avoid writing a new loop for each new input.

I found original code from hiker95 that does exactly what I want except for the variable step size, and I can't for the life of me figure out how to modify it correctly. Any help would be greatly appreciated- I assume the error has to with the step size being called at the end of the loop? Original code is below:

Sub RangeToList()
' original code by hiker95, 08/21/2014, ME800450
' Create list of all values between upper and lower parameter values with specified interval


Dim w1 As Worksheet, wr As Worksheet
Dim a As Variant, i As Long
Dim r As Long, lr As Long, nc As Long, c As Range
Dim MyStart As Long, MyStop As Long, n As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
lr = w1.Cells(Rows.Count, 1).End(xlUp).Row
a = w1.Range("A1:B" & lr).Value
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wr = Sheets("Results")
With wr
  .UsedRange.ClearContents
  For i = 1 To lr
    nc = nc + 1
    .Cells(1, nc).Value = a(i, 1)
    .Cells(2, nc).Value = a(i, 2)
  Next i
  For Each c In .Range(.Cells(1, 1), .Cells(1, lr))
    MyStart = .Cells(1, c.Column)
    MyStop = .Cells(2, c.Column)
    n = (MyStop - MyStart) + 1
    .Cells(3, c.Column) = "Test Points"
    .Cells(4, c.Column) = MyStart
    With .Range(.Cells(4, c.Column), .Cells(n + 3, c.Column))
      .DataSeries Step:=1, Stop:=MyStop
    End With
  Next c
  .Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Answer 1

Assuming your sheet is set up like so, the below examples will output per your example worksheet. I don't know any way to acheive this in 1 loop but you could follow the below examples and incorporate 2 or more loops into your routine, reusing the 2nd example method.

NOTE: These examples are written on a new worksheet in a new workbook. The workbook/worksheets have not been qualified and 'Sheet1' is assumed by default.

'This example will output per your 1st criteria. 
Private Sub ExampleForLoopWithHalfStep()
    Dim LoopCounter As Double
    Dim RowCounter As Long
    RowCounter = 5
    
    For LoopCounter = 1 To 5 Step 0.5
        RowCounter = RowCounter + 1
        Cells(RowCounter, 2).Value = LoopCounter    'Starts at cell B6
    Next LoopCounter
End Sub

'This example will output per your 2nd criteria. 
Private Sub ExampleForLoopWithTenStep()
    Dim LoopCounter As Double
    Dim RowCounter As Long
    RowCounter = 5
    
    For LoopCounter = 40 To 140 Step 10
        RowCounter = RowCounter + 1
        Cells(RowCounter, 3).Value = LoopCounter    'Starts at cell C6
    Next LoopCounter
End Sub

These examples loop both from 1 to 5 stepping 0.5 with each iteration providing the output in increments of 0.5 AND from 40 to 140 stepping 10 with each iteration providing the output in increments of 10.

---

Now if we want to make it a bit more dynamic, we can reference the 'Inputs' section of your sheet with variables or range references, like so:

'This example will output per your 1st criteria. 
Private Sub ExampleForLoopWithHalfStep()
    Dim LoopCounter As Double
    Dim RowCounter As Long
    Dim MinValue As Long
    Dim MaxValue As Long
    Dim StepValue As Double
    
    RowCounter = 5
    MinValue = Range("B2").Value
    MaxValue = Range("C2").Value
    StepValue = Range("D2").Value
        
    For LoopCounter = MinValue To MaxValue Step StepValue
        RowCounter = RowCounter + 1
        Cells(RowCounter, 2).Value = LoopCounter    'Starts at cell B6
    Next LoopCounter
End Sub

'This example will output per your 2nd criteria. 
Private Sub ExampleForLoopWithTenStep()
    Dim LoopCounter As Double
    Dim RowCounter As Long
    Dim MinValue As Long
    Dim MaxValue As Long
    Dim StepValue As Double
    
    RowCounter = 5
    MinValue = Range("B3").Value
    MaxValue = Range("C3").Value
    StepValue = Range("D3").Value
    
    For LoopCounter = MinValue To MaxValue Step StepValue
        RowCounter = RowCounter + 1
        Cells(RowCounter, 3).Value = LoopCounter    'Starts at cell C6
    Next LoopCounter
End Sub

Both sets of examples are doing the exact same thing, just the 2nd can allow you to change the criteria of the loop by updating your 'Input' ranges on the worksheet (Low, High and Interval cells).

---

Avoiding separate loops

This is assuming the inputs are laid out like in your sample data; without knowing the ins and outs of your data, it's difficult to write a 100% sound solution, however this modified code of the above routine will dynamically run through all inputs and output the required values column by column (as shown below.

Private Sub ExampleDynamicForLoop()
    Dim LoopCounter As Double
    Dim RowCounter As Long
    Dim MinValue As Long
    Dim MaxValue As Long
    Dim StepValue As Double
    Dim InputRange As Range
    Dim TargetCell As Range
    Dim RangeCounter As Long
    
    RowCounter = 5
    
    Set InputRange = EstablishInputCount(1, 1) 'Change this to reference the correct StartRow and TargetColumn to suit your data.
    For Each TargetCell In InputRange
        RangeCounter = RangeCounter + 1
        If TargetCell.Value Like "Input*" Then
            MinValue = TargetCell.Offset(0, 1).Value
            MaxValue = TargetCell.Offset(0, 2).Value
            StepValue = TargetCell.Offset(0, 3).Value
            For LoopCounter = MinValue To MaxValue Step StepValue
                RowCounter = RowCounter + 1
                Cells(RowCounter, RangeCounter).Value = LoopCounter    'Starts at cell C6
            Next LoopCounter
            RowCounter = 5
        End If
    Next TargetCell
End Sub

This also uses this Function in the same code module:

Private Function EstablishInputCount(ByVal StartRow As Long, ByVal TargetColumn As Long) As Range
    Dim LastRow As Long
    With Sheet1
        LastRow = .Cells(.Rows.Count, TargetColumn).End(xlUp).Row
        Set EstablishInputCount = .Range(.Cells(StartRow, TargetColumn).Address, .Cells(LastRow, TargetColumn).Address)
    End With
End Function

Using the same example data as before, I tested this with your 2 provided inputs along with a new input and it output as expected:

Keep in mind, this is hard coded to output from Row 6 starting at Column B so you will need to adjust these references in the code as required, along with the location of your inputs.