我正在使用此脚本插入填充行,其中在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 SubIn 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 SubSo, 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.
更多推荐
发布评论