使用 VBA 将表单值插入 Excel 电子表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13108171/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-11 18:16:28 来源:igfitidea点击:
Inserting form values into Excel spreadsheet using VBA
提问by methuselah
I'm trying to insert form values into my Excel spreadsheet using vbabut my current code is inserting values into the same row.
我正在尝试使用vba将表单值插入到我的 Excel 电子表格中，但我当前的代码正在将值插入到同一行中。
- As my table starts from row 3, I want to start from there and continue by automatically shifting to the next row each time. The rows are already set and I don't want to insert new rows but overwrite the current 'empty' rows.
- When 202 rows (maximum no. of rows available) have been entered then I want the spreadsheet to return an error message dialog.
- 当我的表从第 3 行开始时，我想从那里开始并继续每次自动移到下一行。行已经设置，我不想插入新行而是覆盖当前的“空”行。
- 当输入 202 行（最大可用行数）时，我希望电子表格返回一个错误消息对话框。
How can I go about achieving this?
我怎样才能做到这一点？
Current Code
当前代码
Private Sub btnSubmit_Click()
Dim ws As Worksheet
Set ws = Worksheets("main")
' Copy the data to the database
ws.Rows("4:4").Insert Shift:=xlDown
ws.Range("A3").Value = cbo_deptCode.Value
MsgBox ("Booking request has been successfully made")
End Sub
采纳答案by brettdj
something like this
像这样的东西
Private Sub btnSubmit_Click()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Worksheets("main")
Set rng1 = ws.Cells(Rows.Count, "a").End(xlUp)
If rng1.Row > 202 Then
MsgBox "202 Rows exceeded"
Else
rng1.Offset(1, 0) = cbo_deptCode.Value
End If
End Sub
回答by salih0vicX
Please try this and let us know if you have any questions or concerns:
请尝试此操作，如果您有任何问题或疑虑，请告诉我们：
Sub Button1_Click()
Dim ws As Worksheet
Dim i As Long
Set ws = Worksheets("main")
' Copy the data to the database
i = Cells(Rows.Count, 1).End(xlUp).Row + 1 'Get last empty cell in column A
If i > 202 Then
MsgBox "Row 203"
Exit Sub
End If
Range("A" & i).Value = cbo_deptCode.Value
MsgBox ("Booking request has been successfully made")
End Sub
