插入编号的单元格＆＃x2b;基于单元格值

Question

我已经设法根据单元格值插入行，例如，如果A1单元为20，则运行宏，在A1下出现20行，这些行是空白的，我需要A1以下的20个新单元格为1至20（ A1中的数字）如果可能的话，让我知道。

欢呼阿德里安

Answer 1

尝试以下操作：

Sub counter()
Dim i as integer
for i = 2 to cells(1, 1) + 1
    cells(i, 1) = i - 1
next i
End Sub

Answer 2

在单元格下插入一个整数序列

一个活动表的基本示例

• 请注意，这不会插入行，它只是将整数序列写入以下a1下方的单元格。

Sub IntegersBelow()
    With Range("A1")
        .Resize(.Value).Offset(1).Value _
            = .Worksheet.Evaluate("ROW(1:" & CStr(.Value) & ")")
    End With
End Sub

应用于您的实际用例

• 调整常数部分中的值。

Sub InsertIntegersBelow()
 
    ' Use constants to change their values in one place instead
    ' of searching for them in the code (each may be used multiple times).
    Const wsName As String = "Sheet1"
    Const fRow As Long = 3
    Const Col As String = "E"
    
    ' Reference the workbook ('wb').
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing the code
    
    ' Reference the worksheet ('ws').
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    
    ' Calculate the last row ('lRow'),
    ' the row of the last non-empty cell in the column.
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, Col).End(xlUp).Row
    
    ' Validate the last row.
    If lRow < fRow Then
        MsgBox "No data in column range.", vbInformation
        Exit Sub
    End If
    
    Dim cCell As Range ' Current Cell
    Dim cValue As Variant ' Current Cell Value
    Dim r As Long ' Current Row
    
    For r = lRow To fRow Step -1 ' loop backwards
        Set cCell = ws.Cells(r, Col) ' reference the current cell...
        cValue = cCell.Value ' ... and write its value to a variable
        If VarType(cValue) = vbDouble Then ' is a number
            cValue = CLng(cValue) ' ensure whole number
            If cValue > 0 Then ' greater than 0
                ' Insert the rows.
                cCell.Offset(1).Resize(cValue) _
                    .EntireRow.Insert xlShiftDown, xlFormatFromLeftOrAbove
                With cCell.Offset(1).Resize(cValue)
                    ' Write the values.
                    .Value = ws.Evaluate("ROW(1:" & cValue & ")")
                    ' Apply formatting.
                    .ClearFormats
                    .Font.Bold = True
                End With
            'Else ' less than or equal to zero; do nothing
            End If
        'Else ' is not a number
        End If
    Next r
    
    MsgBox "Rows inserted.", vbInformation
    
End Sub