Sequential numbers on column with a maximum range Excel VBA

Question

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...

Firstly, nice little problem to practice with! I don't have excel on this work machine, but I think the array you are creating might be zero-based by default. So the first array index is 0, not 1 - that would mean in your example, it's trying to assign 5 to `Total(5)` which should actually be `Total(4)`. — Samuel Everson, May 28 '20 at 03:08
(I'm not answering because I can't test!) - Put a line break on your `total(i) = i` line (put your cursor on that line and hit F9) and step through the loop (F8 on your keyboard), watch the `total()` in the Locals window - see what the first index is and where in the loop it throws your error. — Samuel Everson, May 28 '20 at 03:12

VBasic2008 · Answer 1 · 2020-05-28T03:48:52.320

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

Thank you for giving not only 1 but 3 solutions. I'll try to apply them and practice with every approach ! — Gabriela Reyes, May 28 '20 at 19:49

Gary's Student · Accepted Answer · 2020-05-28T12:52:27.633

4

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

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

edited May 28 '20 at 12:52

answered May 28 '20 at 03:13

Gary's Student

95,722
10
59
99

Wow! that's very useful, thank you :3 didn't know about the =SEQUENCE property. – Gabriela Reyes May 28 '20 at 19:48
1

ad) Edit#1: you could even use: `Sheet1.Range("B7").Formula2 = "=ROW($A$1:INDEX($A:$A,$C$4))"` to add the dynamic formula set :+) – T.M. Jun 01 '20 at 17:12
1

@T.M. ...........................nice idea..................... – Gary's Student Jun 01 '20 at 17:14

score 3 · Answer 3 · answered May 28 '20 at 03:13

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

Nice answer. I noticed the unappreciative down vote. What a shame. I only would have set the starting number `MyNumber = 1` and put the 'counter line' below the line `StartCell...` thus emulating a `For Next loop` and being able to eventually change the starting number instead of the starting number decreased by `1`. — VBasic2008, May 28 '20 at 05:14
Hello thank you for answering me! you explained it very well although it was a little bit different approach. I wish I could mark more than one correct answer, but I saw the other comment first and it also worked. ;_; thank you I'll also didn't know about the StartCell thing so that's extremely useful. Have a nice day :3 — Gabriela Reyes, May 28 '20 at 05:34

JvdV · Answer 4 · 2020-05-28T07:13:27.593

3

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

edited May 28 '20 at 07:13

answered May 28 '20 at 06:49

JvdV

70,606
8
39
70

I'd have missed your approach: ROW evaluation is a favourite of mine in sequencing; I like to use it e.g. in array restructuring via the [`Application.Index()` function](https://stackoverflow.com/questions/51688593/excel-vba-insert-new-first-column-in-datafield-array-without-loops-or-api-call/51714153?r=SearchResults&s=4|13.6190#51714153). – T.M. May 29 '20 at 08:22

Sequential numbers on column with a maximum range Excel VBA

4 Answers4

Array to Worksheet