vba 制作宏以将数据输入到 Excel 电子表格中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/924000/
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
Make Macro to input data into Excel spreadsheet
提问by Stewbob
I am using Excel 2003 and I need to create a macro the asks the user for 5 inputs: Date, Project#, Fault, Problem, and Solution. I have never used macros before so I have no idea how to code this. Once the data is entered by the user I want it to go under the appropriate columns. After this is done the macro will not prompt the user for another input until the macro is run again.
我正在使用 Excel 2003，我需要创建一个宏，要求用户提供 5 个输入：日期、项目#、故障、问题和解决方案。我以前从未使用过宏，所以我不知道如何编码。用户输入数据后，我希望它位于适当的列下。完成此操作后，宏将不会提示用户再次输入，直到再次运行宏。
回答by Christian Payne
Sub TestMacro()
Range("A2").Select
ActiveCell.FormulaR1C1 = InputBox("Date")
Range("B2").Select
ActiveCell.FormulaR1C1 = InputBox("Project #")
Range("C2").Select
ActiveCell.FormulaR1C1 = InputBox("Fault")
Range("D2").Select
ActiveCell.FormulaR1C1 = InputBox("Problem")
Range("E2").Select
ActiveCell.FormulaR1C1 = InputBox("Solution")
End Sub
回答by Stewbob
When you start the macro, place this code before you do anything:
当您启动宏时，请在执行任何操作之前放置以下代码：
Dim i As Integer
Range("a1").Select
Range(Selection, Selection.End(xlDown)).Select
i = Selection.Rows.Count
This assumes that you always have some value in 'A1' and that there are no blank rows until you come to the end of the valid rows of entered data.
这假设您在 'A1' 中总是有一些值，并且在您到达输入数据的有效行的末尾之前没有空行。
At this point, you have the value 'i' that is equal to the number of rows already containing data.
此时，您的值“i”等于已包含数据的行数。
Then you can use Christian Payne's code above, but modify the
然后你可以使用上面 Christian Payne 的代码，但是修改
Range("A2").Select
to
到
Cells(i + 1, 1).Select
This will allow your macro to place data in the next blank row below your already existing data.
这将允许您的宏将数据放置在现有数据下方的下一个空白行中。
回答by barrowc
There is a built-in way of doing this. Put your column headers in row 1 of an Excel sheet, select cell A1 then go to the Datamenu and choose Form
有一种内置的方法可以做到这一点。将列标题放在 Excel 工作表的第 1 行，选择单元格 A1，然后转到Data菜单并选择Form
This can be a restrictive solution as you can't do things like populating one field on the basis of another (e.g. give a list of problems which depend on the chosen fault). If you just need something quick and simple then it does the job
这可能是一种限制性的解决方案，因为您不能执行诸如基于另一个字段填充一个字段之类的操作（例如，根据所选故障给出问题列表）。如果您只需要快速简单的东西，那么它就可以完成工作
回答by paulmorriss
To move the active cell down to a3 (i.e. the next row underneath as you ask) use this:
要将活动单元格向下移动到 a3（即您要求的下一行），请使用以下命令：
ActiveCell.Offset(1, -4).Select
回答by SqlRyan
Would something like this do what you need?
这样的事情会做你需要的吗？
Sub TestMacro()
Dim dblRow As Double, dtDate As Date, strProject As String
Dim strFalut As String, strProb As String, strSol As String
dblRow = InputBox("What Row to Enter On")
dtDate = InputBox("Date", , Date)
strProject = InputBox("Project #")
strFalut = InputBox("Fault")
strProb = InputBox("Problem")
strSol = InputBox("Solution")
Range("A" & dblRow).Value = dtDate
Range("B" & dblRow).Value = strProject
Range("C" & dblRow).Value = strFalut
Range("D" & dblRow).Value = strProb
Range("E" & dblRow).Value = strSol
End Sub
