将数据从下拉框复制到表格中,连续的行中。

huangapple go评论78阅读模式
英文:

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(&quot;_items&quot;)

Dim LastRow As Long
&#39;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 &lt; 2 Then LastRow = 2 &#39;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(&quot;_items&quot;)

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(&quot;CajaMes&quot;).Object
      Set cbCon = ws.OLEObjects(&quot;CajaConcepto&quot;).Object
      Set cbCjV = ws.OLEObjects(&quot;CajaValor&quot;).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

huangapple
  • 本文由 发表于 2020年1月6日 02:39:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/59603035.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定