Sequential numbers on column with a maximum range Excel VBA

user_12065165 0
Viewed 1540

I'm completely new to VBA and I'm been trying to make basic problems to practice. I just wanna fill a column with sequential numbers from 1 to N. The N number will be a value on an specific cell.

So the N value is on the C4 cell for example is 5 , and I wanna output from B2 - BN = 1,2,3,4,5 I have this code based on kinda similar questions and my knowledge of cycles but I can't make it work...



Sub ejemplo()

Dim total() As Variant


maximo = Range("C4").Value

For i = 1 To maximo
total(i) = i
Next i

total = Application.WorksheetFunction.Transpose(total)
Range("B7:B").Value = total





End Sub

The error that sometimes pop out is "out of range" on the total(i) = i line, I really don't know what's happening...

vba excel arrays
4 Answers

Array to Worksheet

  • In all three cases instead of For i = ... you can use:
    For i = LBound(total) To UBound(total).
  • Transpose is limited to a maximum of 65536 items, so study the third solution which doesn't use it.

The Code

Option Explicit

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Writes the numbers from 1 to "maximo" to the column range
'               starting with cell "B7".
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 1D array (0-based, 'one-row')
Sub ejemplo1()

    Dim total() As Variant
    Dim maximo As Long
    Dim i As Long

    maximo = Range("C4").Value
    ReDim total(maximo - 1)

    For i = 0 To maximo - 1
        total(i) = i + 1
    Next i

    total = Application.Transpose(total)

    Range("B7").Resize(UBound(total)).Value = total

End Sub

' 1D array (1-based, 'one row')
Sub ejemplo2()

    Dim total() As Variant
    Dim maximo As Long
    Dim i As Long

    maximo = Range("C4").Value
    ReDim total(1 To maximo)

    For i = 1 To maximo
        total(i) = i
    Next i

    total = Application.Transpose(total)

    Range("B7").Resize(UBound(total)).Value = total

End Sub

' 2D array (1-based, 'one column')
Sub ejemplo3()

    Dim total() As Variant
    Dim maximo As Long
    Dim i As Long

    maximo = Range("C4").Value
    ReDim total(1 To maximo, 1 To 1)

    For i = 1 To maximo
        total(i, 1) = i
    Next i

    Range("B7").Resize(UBound(total)).Value = total

End Sub
user_98140690

Pretty close:

Sub ejemplo()
    Dim total As Variant
    maximo = Range("C4").Value
    ReDim total(1 To maximo) As Long

    For i = 1 To maximo
        total(i) = i
    Next i

    total = Application.WorksheetFunction.Transpose(total)
    Range("B7").Resize(maximo, 1).Value = total
End Sub

NOTE:

  • the ReDim statement
  • the statement with Resize

enter image description here

EDIT#1:

If you are using Excel 365, then the code can be reduced to a single line:

Sub NoLoops()
    Range("B7").Formula2 = "=SEQUENCE(" & Range("C4").Value & ",1,1,1)"
End Sub
user_24746560

The standard method for counting is

i = i + 1

As you repeatedly call this function i counts up. Applied to your problem, the loop For i = 1 To maximo would count the numbe of loops but it doesn't give the initial i, the number to start from. Therefore, what you need is this:-

Dim MyNumber As Integer
Dim i As Integer

MyNumber = 0
For i = 1 To maximo
    MyNumber = MyNumber + 1
Next i

The next task is to define the cells to write to. It's basically the same logic. You need a point to start from, say B2.

Dim StartCell As Range
Set StartCell = Range("B2")

And now you can put it all together.

Dim Maximo As Integer
Dim StartCell As Range
Dim MyNumber As Integer
Dim i As Integer

Maximo = Range("C4").Value
MyNumber = 0
Set StartCell = Range("B2")
For i = 1 To maximo
    MyNumber = MyNumber + 1
    StartCell.Offset(0, i - 1).Value = MyNumber
Next i
user_76754370

I'd always recommend to at least be explicit about your Worksheet reference. I'd use a With statement making use of the sheet's CodeName.

Furthermore, I'd like to add another answer that creates an array through Evaluate(). While this function has a limit of 255 chars, in this exercise that would never be at risk.

Sub Test()

Dim total As Variant

With Sheet1
    total = .Evaluate("ROW(1:" & .[C4] & ")")
    .Range("B7").Resize(UBound(total)).Value = total
End With

End Sub

Once you are comfortable with what you are looking at here, you can do this in one shot without (IMO) ruining readability:

With Sheet1
    .Range("B7").Resize(.[C4]).Value = .Evaluate("ROW(1:" & .[C4] & ")")
End With
user_97581940
Related