vba 通过 Excel 中的用户窗体将数据输入电子表格

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/13472425/
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-08 14:28:03  来源:igfitidea点击:

Entering Data into a spreadsheet through a UserForm in Excel

excelvbaexcel-vbaexcel-2010

提问by user1662306

I am new to VBA in Excel, and I have a basic userform which is to place the data into the sheet. the data from the form is to enter in cell B13 through to G13, then every other entry after should be done on the next row down e.g. B14-G14.

我是 Excel 中 VBA 的新手,我有一个基本的用户表单,用于将数据放入工作表中。表格中的数据是在单元格 B13 中输入到 G13,然后每隔一个条目应该在下一行完成,例如 B14-G14。

I have this code already however it isnt entering the data into the correct cell and is repeatedly entering it on the same row...

我已经有了这个代码,但是它没有将数据输入到正确的单元格中,而是在同一行重复输入......

     Private Sub CommandButton1_Click()

Dim lngWriteRow As Long

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

lngWriteRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

If lngWriteRow < 13 Then lngWriteRow = 13

    ws.Range("B" & lngWriteRow) = TextBox1.Value
    ws.Range("C" & lngWriteRow) = TextBox2.Value
    ws.Range("D" & lngWriteRow) = TextBox3.Value
    ws.Range("E" & lngWriteRow) = ComboBox1.Value
    ws.Range("F" & lngWriteRow) = TextBox4.Value
    ws.Range("G" & lngWriteRow) = ComboBox2.Value

End Sub

How would i achieve this? (There is already data on the rows below)

我将如何实现这一目标?(下面的行已经有数据了)

Thanks in advance

提前致谢

回答by Jook

This line here is wrong:

这里的这一行是错误的:

lngWriteRow = ws.Cells(Rows.Count, 12) _
.End(xlUp).Offset(1, 0).Row

Because you are referring to column 12, which you do not alter - hence the row stays the same.

因为您指的是第 12 列,您不会更改该列 - 因此该行保持不变。

Use this instead

改用这个

lngWriteRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row

Edit:

编辑:

If you want an initialoffset, to start the data-input @ row 13, use this:

如果您想要初始偏移量,要开始数据输入@第 13 行,请使用以下命令:

lngWriteRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row

if lngWriteRow < 13 then lngWriteRow = 13

You cannot use Offset(12,0), because you would use it everytime!

您不能使用Offset(12,0),因为您每次都会使用它!

Edit

编辑

Just to be crystal clear, this here works on an empty sheet, when pasting the code as a worksheet-macro and hitting F5 multiple times. So, unless there is explained, what this does wrong, I consider the question solved.

只是要清楚一点,当将代码粘贴为工作表宏并多次点击 F5 时,这适用于一张空表。所以,除非有解释,这有什么问题,我认为这个问题已经解决了。

Private Sub Test()

Dim lngWriteRow As Long

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

lngWriteRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row

If lngWriteRow < 13 Then lngWriteRow = 13

    ws.Range("B" & lngWriteRow) = "test"
    ws.Range("C" & lngWriteRow) = "test"
    ws.Range("D" & lngWriteRow) = "test"
    ws.Range("E" & lngWriteRow) = "test"
    ws.Range("F" & lngWriteRow) = "test"
    ws.Range("G" & lngWriteRow) = "test"

End Sub

Edit

编辑

After some mailing, here is the solution to this riddle: it was not stated, that there are filled cells beneath those, which shall be entered.

经过一些邮寄,这是这个谜语的解决方案:它没有说明,在那些下面有填充的单元格,应该输入。

So for col-B it was more like

所以对于 col-B 来说更像是

title-row
row13
row..
row..
row63
space
other stuff

basically the suggested corrections worked - but they looked for the last filled cell in column B on the whole sheet, which was the problem.

基本上建议的更正有效 - 但他们在整个工作表的 B 列中寻找最后一个填充的单元格,这就是问题所在。

Here is the solution to that:

这是解决方案:

lngWriteRow = ws.Cells(ws.Range("B12:B63")Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row

And to give you some explanaition on the way:

并在途中给你一些解释:

You can't use (Rows.Count,1)instead of (Rows.Count,2), because you are adding Data in the columns B-G, which is 2-7. You have to use 2-7 because of the way, you are looking for the last row. If you use 1, you're looking for the last value in column A, which does not change, when you are trying to add new data.

您不能使用(Rows.Count,1)代替(Rows.Count,2),因为您要在 BG 列中添加数据,即 2-7。你必须使用 2-7 因为方式,你正在寻找最后一行。如果使用 1,则在尝试添加新数据时,您正在查找 A 列中的最后一个值,该值不会更改。

You can't use Offset(12,0), because this would create an offset everytime you insert data - so you would end up with rows 12 rows apart.

你不能使用Offset(12,0),因为这会在你每次插入数据时创建一个偏移量 - 所以你最终会得到相距 12 行的行。

And finally, you can't use Rows.Count, because this is 65536 or so, and you have data beneath the data you are adding. End(xlUp)will lookup from too far down, and stop at the last cell of column B, which has data in it - but this won't be B13, unless there is no data in B14-B65536.

最后,您不能使用Rows.Count,因为这是 65536 左右,并且您在添加的数据下方有数据。End(xlUp)将从太低的位置查找,并在 B 列的最后一个单元格处停止,其中有数据 - 但这不会是 B13,除非 B14-B65536 中没有数据。

Hope this helps to understand the dynamics here.

希望这有助于理解这里的动态。