arrays - 具有最大范围Excel VBA的列上的序号

Question

我对 VBA 完全陌生，我一直在尝试解决基本问题。
我只想用从 1 到 N 的序列号填充一列。 N 号将是特定单元格上的值。

所以 N 值在 C4 单元格上，例如 5 ，我想从 B2 输出 - BN = 1,2,3,4,5
我有这个代码基于有点类似的问题和我对周期的了解，但我无法让它工作......

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

有时弹出的错误是 total(i) = i 行上的“超出范围”，我真的不知道发生了什么......

Answer 1

数组到工作表

在所有三种情况下，而不是 For i = ...您可以使用:For i = LBound(total) To UBound(total) .

Transpose最大限制为 65536项目，所以研究
第三种不使用它的解决方案。

代码

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