使用 VBA 按钮单击填充新行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2589453/
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
Populate new row with VBA button click
提问by AME
I am trying to create a list that adds a new row of data each time a button is clicked. I have the following code assigned to the button when clicked:
我正在尝试创建一个列表，每次单击按钮时都会添加新的数据行。单击时，我将以下代码分配给按钮：
PurchaseDate = InputBox("Enter Purchase Date:")
Sheets("TrackRecord").Select
i = 0
Row = i + 1
Range("A2").Select
ActiveCell.FormulaR1C1 = Row
Range("B2").Select
ActiveCell.FormulaR1C1 = "=Dashboard!R26C4*(1/Dashboard!R26C12)"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=Dashboard!R26C2"
Range("D2").Select
ActiveCell.FormulaR1C1 = PurchaseDate
Range("E2").Select
ActiveCell.FormulaR1C1 = "=Dashboard!R26C8 + R2C4"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=Waterfall!R[8]C[5]"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:I2"), Type:=xlFillDefault
Range("F2:I2").Select
End Sub
This code works fine, but I'd like it populate the next row below instead of overwriting the same row each time the button is clicked. I know that I have to iterate through the "Range("A2").select" section, e.g. "Range("A2").select" --> "Range("B2").select" .. But I don't know how to do this in VBA for Excel. That's why I am asking you folks ; ) .
这段代码工作正常，但我希望它填充下面的下一行，而不是每次单击按钮时覆盖同一行。我知道我必须遍历 "Range("A2").select" 部分，例如 "Range("A2").select" --> "Range("B2").select" .. 但我不不知道如何在 VBA for Excel 中执行此操作。这就是我问你们的原因；)。
Thanks,
谢谢，
回答by Michael Rodrigues
If you want to persist the next row even when you close excel and open it again, it's better to find the last row each time.
如果你想在关闭excel并再次打开它时保留下一行，最好每次找到最后一行。
Dim Row As Long
Row = GetNextRow(Sheets("TrackRecord"), "A")
PurchaseDate = InputBox("Enter Purchase Date:")
Sheets("TrackRecord").Select
Range("A" & Row).Select
ActiveCell.FormulaR1C1 = Row
Range("B" & Row).Select
ActiveCell.FormulaR1C1 = "=Dashboard!R26C4*(1/Dashboard!R26C12)"
Range("C" & Row).Select
ActiveCell.FormulaR1C1 = "=Dashboard!R26C2"
Range("D" & Row).Select
ActiveCell.FormulaR1C1 = PurchaseDate
Range("E" & Row).Select
ActiveCell.FormulaR1C1 = "=Dashboard!R26C8 + R2C4"
Range("F" & Row).Select
ActiveCell.FormulaR1C1 = "=Waterfall!R[8]C[5]"
Range("F" & Row).Select
Selection.AutoFill Destination:=Range("F" & Row & ":I" & Row), Type:=xlFillDefault
Range("F" & Row & ":I" & Row).Select
End Sub
Private Function GetNextRow(sheet As Worksheet, column As String) As Long
'Look for the first empty row in the specified column
Dim Row As Long
For Row = 1 To 65535
If sheet.Range(column & Row).Formula = "" Then
GetNextRow = Row
Exit For
End If
Next Row
End Function
