vba 在特定单元格中使用值在excel中插入行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24181242/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert row in excel with a value in a specific cell
提问by Levimatt
I'm using this script to insert fill with rows where non-sequential is produced in a column of an excel file.
我正在使用此脚本插入填充行，其中在 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
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.
除了添加这些新行之外，我还希望它们在 B 列中具有特定值。我正在尝试实现这一点，但没有结果。
Anybody could help me?
有人可以帮助我吗？
回答by Dan Wagner
One way you could tackle this challenge is with a Rangevariable. Here is some heavily-commented code that walks through the process:
解决这一挑战的一种方法是使用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
So, to sum it up, we know that gap - 1rows 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 Rangethen set the .valueof that Rangeto whatever is needed.
因此，总而言之，我们知道gap - 1将要添加行，并且我们知道新行从 row 开始添加i。使用这些知识，我们分配在B列刚刚细胞加入到Range接盘.value的是Range到任何需要。
回答by DeerSpotter
a Better way of doing it with less variables and faster:
用更少的变量和更快的更好的方法来做到这一点：
Sub InsRowWithText()
Dim LR As Long, i As Long
LR = Range("D" & Rows.Count).End(xlUp).row
For i = LR To 3 Step -1
If Range("D" & i).Value <> Range("D" & i - 1).Value Then
Rows(i).Resize(1).Insert
Range("D" & i).Value = "Test"
End If
Next i
End Sub
This is how i utilized it:
这是我如何使用它：
Sub InsRowWithText()
Dim strMsg As String, strTitle As String
Dim LR As Long, i As Long
Text = "ADD"
strMsg = "Warning: This is a Advanced Function, Continue? "
strTitle = "Warning: Activated Advanced Function "
If MsgBox(strMsg, vbQuestion + vbYesNo, strTitle) = vbNo Then
Exit Sub
Else
Sheets("SAP Output DATA").Select
If Range("D3").Value = Text Then
MsgBox "Detected That This Step Was Already Completed, Exiting."
Exit Sub
End If
application.ScreenUpdating = False
LR = Range("D" & Rows.Count).End(xlUp).row
For i = LR To 3 Step -1
If Range("D" & i).Value <> Range("D" & i - 1).Value Then
Rows(i).Resize(1).Insert
Range("D" & i).EntireRow.Interior.ColorIndex = xlColorIndexNone
Range(("A" & i), ("D" & i)).Value = Text
End If
Next i
End If
Range("D2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select
Range(("A" & ActiveCell.row), ("D" & ActiveCell.row)).Value = Text 'last row doesnt get text for some reason.
ActiveCell.EntireRow.Interior.ColorIndex = xlColorIndexNone
ActiveCell.Offset(1).Select
Range(("D" & ActiveCell.row), ("E" & ActiveCell.row)).Interior.ColorIndex = 17 'purple
application.ScreenUpdating = True
Range("D3").Select
End Sub
