在excel中插入行，并在特定单元格中输入值(Insert row in excel with a value in a specific cell)

在excel中插入行，并在特定单元格中输入值(Insert row in excel with a value in a specific cell)

我正在使用此脚本插入填充行，其中在Excel文件的列中生成非顺序行。

Sub InsertValueBetween() Dim lastrow As Long Dim gap As Long Dim i As Long, ii As Long Application.ScreenUpdating = False With ActiveSheet lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = lastrow To 3 Step -1 gap = .Cells(i, "A").Value - .Cells(i - 1, "A").Value If gap > 1 Then .Rows(i).Resize(gap - 1).Insert End If Next i lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row .Cells(3, "A").Value = .Cells(2, "A").Value + 1 .Cells(2, "A").Resize(2).AutoFill .Cells(2, "A").Resize(lastrow - 1) End With End Sub

除了添加这些新行之外，我希望它们在B列中也有一个特定的值。我正在尝试实现它但没有结果。

有人可以帮帮我吗？

I'm using this script to insert fill with rows where non-sequential is produced in a column of an excel file.

Sub InsertValueBetween() Dim lastrow As Long Dim gap As Long Dim i As Long, ii As Long Application.ScreenUpdating = False With ActiveSheet lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = lastrow To 3 Step -1 gap = .Cells(i, "A").Value - .Cells(i - 1, "A").Value If gap > 1 Then .Rows(i).Resize(gap - 1).Insert End If Next i lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row .Cells(3, "A").Value = .Cells(2, "A").Value + 1 .Cells(2, "A").Resize(2).AutoFill .Cells(2, "A").Resize(lastrow - 1) End With End Sub

In addition to adding these new rows I want them to also have a specific value in column B. I'm trying to implement this but with no result.

Anybody could help me?

最满意答案

解决这一挑战的一种方法是使用Range变量。 以下是一些经过深思熟虑的代码，它贯穿整个过程：

Sub InsertValueBetweenRev2() Dim Target As Range '<~ declare the range variable '... declare your other variables '... do other stuff For i = lastrow To 3 Step -1 gap = .Cells(i, "A").Value - .Cells(i - 1, "A").Value If gap > 1 Then .Rows(i).Resize(gap - 1).Insert 'the next line sets the range variable to the recently 'added cells in column B Set Target = .Range(.Cells(i, 2), .Cells(i + gap - 2, 2)) Target.Value = "Cool" '<~ this line writes text "Cool" into those cells End If Next i '... the rest of your code End Sub

总而言之，我们知道gap - 1将要添加gap - 1行，并且我们知道从第i行开始添加新行。 使用该知识，我们将列B中刚刚添加的单元格分配给Range然后将该Range的.value设置为所需的任何值。

One way you could tackle this challenge is with a Range variable. Here is some heavily-commented code that walks through the process:

Sub InsertValueBetweenRev2() Dim Target As Range '<~ declare the range variable '... declare your other variables '... do other stuff For i = lastrow To 3 Step -1 gap = .Cells(i, "A").Value - .Cells(i - 1, "A").Value If gap > 1 Then .Rows(i).Resize(gap - 1).Insert 'the next line sets the range variable to the recently 'added cells in column B Set Target = .Range(.Cells(i, 2), .Cells(i + gap - 2, 2)) Target.Value = "Cool" '<~ this line writes text "Cool" into those cells End If Next i '... the rest of your code End Sub

So, to sum it up, we know that gap - 1 rows are going to be added, and we know that the new rows are added starting at row i. Using that knowledge, we assign the just-added cells in column B to a Range then set the .value of that Range to whatever is needed.