CODEWITHSUNDEEP
excelvba
Mike Geiger
Mike Geiger

Reputation: 11

I am trying to find all numbers between a given range

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

Upvotes: 1

Views: 530

Answers (3)

T.M.
T.M.

Reputation: 9948

If you dispose of MS 365 you might use the new Sequence() function as WorksheetFunction avoiding loops completely (see section b)):

Syntax
=SEQUENCE(rows,[columns],[start],[step])

You calculate the needed number of rows by substracting the lower start value from the higher ending value plus +1. The columns no equals 1, the start value is userdefined, the step is +1 by default.

This results in a vertical 2-dim array which can be written to any target (see section c)).

Option Explicit

Sub WriteSequence()
'a) definitions
    Dim lo As Long, hi As Long
    lo = InputBox("Beginning Range")
    hi = InputBox("Ending Range")
'b) get sequence as vertical 2-dim array    
    Dim seq
    seq = WorksheetFunction.Sequence(hi - lo + 1, 1, lo)
'c) write to any target
    Sheet1.Range("A1").Resize(hi - lo + 1, 1) = seq
End Sub

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54838

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

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152585

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

Upvotes: 2

Related Questions