☰

在 Excel (2003) 宏中用连续数字填充单元格

tag-icon • tag-icon microsoft-excel vba
在 Excel (2003) 宏中用连续数字填充单元格

我需要用连续的系列填充 excel 列，在本例中从 -500 到 1000。我有一个宏可以做这件事，但它需要很多行才能完成看似应该是一个函数的事情 [例如 FillRange(A2:A1502, -500, 1000, 1)]。但如果该函数存在，我找不到它。下面的代码是否尽可能简单和优雅？

'Draw X axis scale
Cells(1, 1).Value = "mV"
Cells(2, 1).Value = -500
Cells(3, 1).Value = -499
Cells(4, 1).Value = -498

Dim selection1 As Range, selection2 As Range

Set selection1 = Sheet1.Range("A2:A4")
Set selection2 = Sheet1.Range("A2:A1502")

selection1.AutoFill Destination:=selection2

答案1

Range("A1")=-500
Range("A1").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Stop:=500, Trend:=False

答案2

Sub FillASeries()

    With Sheet1.Range("a1")
        .Value = -500
        .AutoFill .Resize(1501, 1), xlFillSeries
    End With

End Sub

我认为它不是一个单一的功能，但这是我能使程序变得最短的程度。

答案3

您正在寻找的不是一个功能。

在 A2 中输入 -500，然后按“Ctrl+Enter”（接受数字并选择单元格）

然后进入“编辑”菜单-->“填充”-->“系列”

将“系列”设置为“列”

将“停止值”设置为 1000

点击“确定”你就会得到想要的结果。

答案4

Sub Test()

    Call NumberSeriesI(Sheet1.Range("A1"), "MySeries", -1000, 500, 1)

End Sub

Sub NumberSeriesI(StartCell As Range, Header As String, FirstN As Integer, LastN As Integer, StepN As Integer)

' Integer version

    Dim i As Integer ' Value
    Dim r As Integer ' row

    StartCell.Cells(1).Value = Header ' Cells(1) makes sure it only uses the first cell of passed-in range,
                                        ' in case you pass in a multi-celled range for StartCell
    i = FirstN
    r = 1

    Application.ScreenUpdating = False ' Much faster, so the screen is not refreshed until all the values in place

    For i = FirstN To LastN Step StepN
        StartCell.Cells(1).Offset(r, 0).Value = i
        i = i + StepN
        r = r + 1
    Next i

    Application.ScreenUpdating = True

End Sub ' NumberSeriesI

相关内容