I am trying to find all numbers between a given range-CodePudding

I have to be able to give two inputs a beginning and an ending number in a range. I have to be able to extrapolate all values in between and including the beginning and ending numbers. I have gotten as far as making a loop that gets stuck :) I also have defined the Beginning number and ending number in some input boxes and successfully put the beginning number in A1.

The numbers in my case are always unknown a person will always be able to tell what the beginning and ending numbers are but they will always change.

Sub FindNum()
    Dim iLowVal As Integer
    Dim iHighVal As Integer

    iLowVal = InputBox("Beginning Range")
    iHighVal = InputBox("Ending Range")
    
    Range("A1").Value = iLowVal
    Do Until iLowVal = iHighVal
        Range("A1" & i) = iLowVal   1
           
    Loop
    
    
End Sub

CodePudding user response：

Start now to learn how to use variant arrays. It is quicker to use them and bulk assign to the sheet than to loop the sheet.

When using Arrays it is quicker to use For Loops.

Sub FindNum()
    Dim iLowVal As Long
    Dim iHighVal As Long

    iLowVal = InputBox("Beginning Range")
    iHighVal = InputBox("Ending Range")
    
    Dim itNum As Long
    itNum = iHighVal - iLowVal   1

    Dim arr As Variant
    ReDim arr(1 To itNum)
    
    Dim k As Long
    k = iLowVal

    Dim i As Long
    For i = 1 To itNum
        arr(i) = k
        k = k   1
    Next i
    
    ActiveSheet.Range("A1").Resize(itNum).Value = Application.Transpose(arr)
End Sub

CodePudding user response：

Write an Array of Integers (`For...Next`)

This is just a basic code with a few more options. Play with it (i.e. modify "A1", "A", (1, 0)) to see its behavior so you can improve on it by googling, SOing, asking another question... etc.

Option Explicit

Sub FindNumForNext()
    
    ' Input data.
    Dim nStart As Long: nStart = InputBox(Prompt:="Start Value", _
        Title:="Write an Array of Integers", Default:=1)
    Dim nEnd As Long: nEnd = InputBox("End Value")
    ' Check out 'Application.InputBox' as a better way to input data.
    
    ' Determine the order (asc or desc).
    Dim nStep As Long
    If nStart <= nEnd Then ' ascending
        nStep = 1
    Else ' descending
        nStep = -1
    End If
    
    ' Create a reference to the destination worksheet.
    Dim dws As Worksheet: Set dws = ActiveSheet ' the one you're looking at
    ' Instead of the previous line, it is safer (better) to determine
    ' the exact worksheet where this will be used, e.g.:
    'Dim dwb As Workbook: Set dwb = ThisWorkbook ' workbook containing this code
    'Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet1") ' tab name
    
    ' Create a reference to the first destination cell.
    Dim dCell As Range: Set dCell = dws.Range("A1")
    ' Clear possible previous data.
    dws.Columns("A").Clear
    
    Dim n As Long ' Values (Numbers) Counter (For Next Control Variable)
    
    ' Loop from start to end...
    For n = nStart To nEnd Step nStep
        ' Write the current number to the current destination cell.
        dCell.Value = n
        ' Create a reference to the next destination cell.
        Set dCell = dCell.Offset(1, 0)
        ' 1 means one cell down
        ' 0 means zero cells to the right
    
    Next n ' next value (number)
    
End Sub

Write an Array of Integers (For...Next)

Write an Array of Integers (`For...Next`)