英文:
Copy data from combobox to a table, in sucessive rows
问题
我正在使用三个ComboBox来显示特定数据。当我点击按钮时,我想将所选数据发送到给定的表格,该表格位于与执行代码的工作表**不同的工作表**上。到目前为止,这是我的代码:
Private Sub BotonAgregar_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("_items")
ws.Cells(2, 1) = CajaMes
ws.Cells(2, 2) = CajaConcepto
ws.Cells(2, 3) = CajaValor
CajaMes = Empty
CajaConcepto = Empty
CajaValor = Empty
End Sub
在这段代码中,我成功地将所有三个字段的数据发送到名为`_items`的标签页中,但只是第一个实例。如果我想发送更多数据,第一行就会被替换。我希望将所有数据发送到下一个行下面的一行。有什么帮助吗?
英文:
I'm working with three ComboBox'es that display certain data. When I hit a button, I want to send the selected data to a given table, which is located in a different worksheet from where the code is executed. This is what I have, so far:
Private Sub BotonAgregar_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("_items")
ws.Cells(2, 1) = CajaMes
ws.Cells(2, 2) = CajaConcepto
ws.Cells(2, 3) = CajaValor
CajaMes = Empty
CajaConcepto = Empty
CajaValor = Empty
End Sub
In this code, I'm successfully sending all three fields data to tab _items
in the desired cells, but just the fist instance. If I want send further data, the first row is being replaced. I want to send everything to one row under the next one. Any help?
答案1
得分: 1
问题在于您没有更新要发送数据的行号,这就是为什么它一直在覆盖相同行的单元格。
根据您的数据设置方式,有许多方法可以获取表中的最后一个空行,但以下是一个简单的示例:
Private Sub BotonAgregar_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("_items")
Dim LastRow As Long
' 获取最后一行,注意:我们将LastRow加1,以便将数据放入下一个空行
LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
If LastRow < 2 Then LastRow = 2 ' 确保它从第2行开始,而不是更低
ws.Cells(LastRow, 1) = Me.CajaMes.value
ws.Cells(LastRow, 2) = Me.CajaConcepto.value
ws.Cells(LastRow, 3) = Me.CajaValor.value
Me.CajaMes.value = Null
Me.CajaConcepto.value = Null
Me.CajaValor.value = Null
End Sub
现在应该可以满足您的要求。
英文:
The problem is that you are not updating the row number to send the data to, that's why it keeps overwriting the same row's cells.
There are many ways to get the last empty row in a table, depending on how your data is set up, but below is a simple example:
Private Sub BotonAgregar_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("_items")
Dim LastRow As Long
'getting the last row, nb. we add 1 to the LastRow so we put the data in the next empty line
LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
If LastRow < 2 Then LastRow = 2 'just making sure it starts at 2 and not lower
ws.Cells(LastRow, 1) = Me.CajaMes.value
ws.Cells(LastRow, 2) = Me.CajaConcepto.value
ws.Cells(LastRow, 3) = Me.CajaValor.value
Me.CajaMes.value = Null
Me.CajaConcepto.value = Null
Me.CajaValor.value = Null
End Sub
This should now do what you require
答案2
得分: 0
如果那些Caja...
是您用户窗体上的组合框,并且您使用Empty
来使它们不显示任何项目,请测试此代码:
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("_items")
ws.Cells(2, 1).Value = Me.CajaMes.Value
ws.Cells(2, 2).Value = Me.CajaConcepto.Value
ws.Cells(2, 3).Value = Me.CajaValor.Value
Me.CajaMes.ListIndex = -1
Me.CajaConcepto.ListIndex = -1
Me.CajaValor.ListIndex = -1
但是,如果没有选择任何组合值(没有选择任何项目),该函数将在该特定行返回错误...
如果您的组合框是ActiveX类型,位于一个页面上,请尝试以下代码:
Dim ws As Worksheet, cbCM As ComboBox, cbCon As ComboBox, cbCjV As ComboBox
Set ws = ActiveSheet
Set cbCM = ws.OLEObjects("CajaMes").Object
Set cbCon = ws.OLEObjects("CajaConcepto").Object
Set cbCjV = ws.OLEObjects("CajaValor").Object
ws.Cells(2, 1).Value = cbCM.Value
ws.Cells(2, 2).Value = cbCon.Value
ws.Cells(2, 3).Value = cbCjV.Value
cbCM.ListIndex = -1
cbCon.ListIndex = -1
cbCjV.ListIndex = -1
英文:
if those Caja...
are your combo boxes on a UserForm and you use Empty
to make them not showing any item, test this code, please:
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("_items")
ws.Cells(2, 1).value = Me.CajaMes.value
ws.Cells(2, 2).value = Me.CajaConcepto.value
ws.Cells(2, 3).value = Me.CajaValor.value
Me.CajaMes.ListIndex = -1
Me.CajaConcepto.ListIndex = -1
Me.CajaValor.ListIndex = -1
But, if no combo value (no any item selected) the function will return an error at that specific line...
If your combo boxes are ActiveX type, staying on a page, try the next code:
Dim ws As Worksheet, cbCM As ComboBox, cbCon As ComboBox, cbCjV As ComboBox
Set ws = ActiveSheet
Set cbCM = ws.OLEObjects("CajaMes").Object
Set cbCon = ws.OLEObjects("CajaConcepto").Object
Set cbCjV = ws.OLEObjects("CajaValor").Object
ws.Cells(2, 1).value = cbCM.value
ws.Cells(2, 2).value = cbCon.value
ws.Cells(2, 3).value = cbCjV.value
cbCM.ListIndex = -1
cbCon.ListIndex = -1
cbCjV.ListIndex = -1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论