制作 Excel 宏以快速输入数据 ::: VBA ::: Excel :::
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/370717/
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
Making Excel macro for fast data entering ::: VBA ::: Excel :::
提问by Jox
I have a job to enter survey results (in paper form) to excel. I've never written any macro in Office :(
我有一份工作要输入调查结果（以纸质形式）以取得优异成绩。我从来没有在 Office 中写过任何宏 :(
Here I what I basically need:
在这里，我基本上需要的是：
- I have predefined columns (|A|B|...|AG|AH|)
- All surveys are grouped into groups. All surveys from same group have few (like predefined) same columns. It's always same columns that 'define' group
- All other survey answers are in numerical type [1..10].
- Columns are not in same order as answers in servey
- I want macro that will take my input (for example '1575'), and first place predefined values to that 'group' to |A| |B| |C|, and then |E| = 1, |D| = 5, |F| = 7, |G| = 5, and automatically start entering next row.
- 我有预定义的列 (|A|B|...|AG|AH|)
- 所有调查都分组。来自同一组的所有调查几乎没有（如预定义的）相同的列。“定义”组总是相同的列
- 所有其他调查答案都是数字类型 [1..10]。
- 列的顺序与 servey 中的答案顺序不同
- 我想要宏将接受我的输入（例如“1575”），并将该“组”的预定义值首先放置到 |A| |乙| |C|，然后是 |E| = 1, |D| = 5, |F| = 7, |G| = 5，并自动开始进入下一行。
Anything that will give me a clue how to write this macro in more than welcome
任何能给我一个线索如何编写这个宏的东西都非常受欢迎
Huge thanks for reading this...
非常感谢您阅读本文...
EDIT1: I suppose question is not clear enough... I need macro that will read my keyboard input ( '1575' ) and write integers '1' '5' '7' and '5' to predefined rows. For now, I have an idea to make a form, but I need event handler that will change focus to next input when I press a key, as I want to avoid pressing TAB all the time...
EDIT1：我想问题还不够清楚......我需要宏来读取我的键盘输入（ '1575' ）并将整数 '1' '5' '7' 和 '5' 写入预定义的行。现在，我有一个制作表单的想法，但我需要事件处理程序，当我按下一个键时，它会将焦点转移到下一个输入，因为我想避免一直按 TAB...
回答by Fionnuala
It seems to me you need a small userform with just a textbox, a range to show the order of data entry, say:
在我看来，你需要一个只有一个文本框的小用户表单，一个显示数据输入顺序的范围，比如：
Group A | E | D | F | G
A组| E | D | F | G
And a little code to go with the form, say:
还有一些与表单一起使用的代码，例如：
Dim LastCol As Integer
Dim CurRow As Integer
Private Sub UpdateCells()
Dim Col As Variant
Dim ColumnOrder As Range
'Range that specifies data entry order'
Set ColumnOrder = Range("A3:A15")
LastCol = LastCol + 1
If LastCol > WorksheetFunction.CountA(ColumnOrder) Then
LastCol = 1
CurRow = CurRow + 1
End If
Col = Range("A3").Offset(0, LastCol)
Range(Col & CurRow) = TextBox1.Value
TextBox1 = ""
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
UpdateCells
End If
End Sub
Private Sub UserForm_Initialize()
CurRow = ActiveCell.Row
End Sub
Edit Re Comment
编辑重新评论
In Excel, the last row can be a bit tricky in that Excel does not update the range after deletions until the workbook is saved. One one these may suit:
在 Excel 中，最后一行可能有点棘手，因为在保存工作簿之前，Excel 不会在删除后更新范围。其中之一可能适合：
Function FindLastRow()
'Assuming that data starts in A1'
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
FindLastRow = r
End Function
.
.
Sub LastRow()
LastRowA = ExecuteExcel4Macro("GET.DOCUMENT(10)")
LastRowB = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
End Sub
Further information: http://www.tek-tips.com/faqs.cfm?fid=2115
回答by Dick Kusleika
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sText As String
Application.EnableEvents = True
If Target.Column = 1 Then 'Four digits entered in col A
sText = CStr(Target.Value) 'convert to string
If Len(sText) = 4 Then
Target.Offset(0, 4).Value = Left$(sText, 1) 'write to E
Target.Offset(0, 3).Value = Mid$(sText, 2, 1) 'D
Target.Offset(0, 5).Value = Mid$(sText, 3, 1) 'F
Target.Offset(0, 6).Value = Mid$(sText, 4, 1) 'G
End If
End If
Application.EnableEvents = True
End SubIf you enter your four digits in column A, this will spread them to the correct columns. It goes in the worksheet's module, not a standard module.
如果您在 A 列中输入四位数字，这会将它们分散到正确的列中。它进入工作表的模块，而不是标准模块。
回答by KnomDeGuerre
If I understand the question correctly, you want the user to be able to type numbers only, without using enter or arrow to finalize the entry.
如果我正确理解了这个问题，您希望用户只能输入数字，而无需使用 Enter 或箭头来完成输入。
For example, typing "1253216..." instead of "1 2 5 ..."
例如，输入“1253216...”而不是“1 2 5 ...”
If so, unfortunately there is no event that corresponds to individual characters going into the formula bar. The only events that are remotely related are Selection Change and Change. However, I don't think there is any way to use these for this purpose (they are not triggered during formula bar typing).
如果是这样，不幸的是，没有与进入公式栏的单个字符相对应的事件。唯一与远程相关的事件是选择更改和更改。但是，我认为没有任何方法可以将它们用于此目的（它们不会在公式栏输入期间触发）。
One idea: Make and entry column with text format and instruct the users to type the answers in one big string (like "1253216" etc.), then put formulas in each of the other cells that parses out the nth character from the input column and changes it to a value. For example:
一个想法：使用文本格式制作和输入列，并指示用户在一个大字符串中输入答案（如“1253216”等），然后将公式放入其他每个单元格中，从输入列中解析出第 n 个字符并将其更改为一个值。例如：
Apply range name "Inputs" to column A, range name "N" to row 1, then in any of the other cells, put this formula:
将范围名称“输入”应用于 A 列，将范围名称“N”应用于第 1 行，然后在任何其他单元格中输入以下公式：
=VALUE(MID(Inputs,N,1))
The drawback is, the user won't know if they made some error until after they finish typing the whole string of numbers and press enter or arrow.
缺点是，用户在输入完整个数字字符串并按 Enter 或箭头后，才知道他们是否犯了错误。
