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

问题描述

我很熟悉Excel中的VBA，我有一个基本的用户形式，用于将数据放入工作表中。来自表单的数据是进入单元格B13到G13，然后在下一行的下一个完成之后每隔一个条目就可以完成。 B14-G14。

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

  Private Sub CommandButton1_Click（）
 
 Dim lngWriteRow As Long 
 
 Dim ws As Worksheet 
设置ws =工作表（Sheet1）
 
 lngWriteRow = ws.Cells（Rows.Count，1）_ 
 .End（xlUp）.Offset（1，0）。行
 
如果lngWriteRow< 13然后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 
  

我该如何实现？ （以下行已有数据）

提前感谢

解决方案

这行错误：

  lngWriteRow = ws.Cells（Rows.Count，12）_ 
 .End（xlUp）.Offset（1，0）.Row 
  

因为你是指第12列，您不会改变，因此该行保持不变。

使用此代替

  lngWriteRow = ws.Cells（Rows.Count，2）_ 
 .End（xlUp）.Offset（1，0）.Row 
  pre> 
 
 
 编辑： 
 
 
 
如果你想要一个 strong> offset，启动数据输入@ row 13，使用这个：
  lngWriteRow = ws.Cells（Rows.Count ，2）_ 
 .End（xlUp）.Offset（1，0）.Row 
 
如果lngWriteRow< 13然后lngWriteRow = 13 
  
您不能使用 Offset（12,0） / code>，因为你会每次使用它！
 
 
 
 编辑 
 
 
 
为了清楚这一点，这个工作在一个空的工作表上，将代码粘贴成工作表宏并多次击中F5。所以，除非有解释，这是什么问题，我认为这个问题解决了。
 私有子测试（）
 
 Dim lngWriteRow As Long 
 
 Dim ws As Worksheet 
设置ws = Worksheets（Sheet1）
 
 lngWriteRow = ws.Cells（Rows。 Count，2）_ 
 .End（xlUp）.Offset（1，0）.Row 
 
如果lngWriteRow< 13然后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 
  
 编辑 
 
 
 
经过一些邮寄，这里是解决这个谜语的：说，在这些下面有填充的单元格，将被输入。
 
 
 
所以对于col-B它更像是
  title-row 
 row13 
 row .. 
 row .. 
 row63 
 space 
其他东西
  
基本上建议的更正工作 - 但是他们在整体上查找了B列中最后一个填充的单元格这是问题。
 
 
 
这是解决方案：
  lngWriteRow = ws.Cells（ws.Range（B 12：B63）Rows.Count，2）_ 
 .End（xlUp）.Offset（1，0）.Row 
  
然后给你一些解释的方式：
 
 
 
你不能使用（Rows.Count， 1）而不是（Rows.Count，2），因为您正在列BG中添加数据，即2-7。你必须使用2-7，因为方式，你正在寻找最后一行。如果您使用1，您正在寻找A列中的最后一个值，当您尝试添加新数据时，该值不会更改。
 
 
 
您不能使用 Offset（12,0），因为每次插入数据时都会创建一个偏移量，所以你最终会排成行12行。
 
 
 
最后，您不能使用 Rows.Count ，因为这是65536左右，您在添加的数据下方有数据。  End（xlUp）将从太远的位置进行查找，并停留在列B中的最后一个单元格，其中包含数据，但这不会是B13，除非有在B14-B65536中没有数据。
 
 
 
希望这有助于了解这里的动态。
 
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.
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
 解决方案 
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.
Use this instead
lngWriteRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
Edit:
If you want an initial offset, to start the data-input @ row 13, use this:
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!
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.
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
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.
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.
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.
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.
Hope this helps to understand the dynamics here.
                        
这篇关于通过Excel中的用户窗体将数据输入到电子表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！