ComboBox value to next empty cell in table.

[MarkOW]

Hi, I am using the following code to export data from a ComboBox to the next empty cell in a range.

Private Sub CommandButton6_Click()
 Dim LastRow As Long
        With ThisWorkbook.Sheets("Data")
            LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
            With .Range("B" & LastRow)
                .Value2 = ComboBox3.Value
                MsgBox "Entry Exported To List", vbInformation
            End With
        End With
End Sub

I have changed my data from a range to a table, the ComboBox selection is now pasting the next next empty row in the table instead of next empty cell in row.
Help if you can please.
Many thanks

 

[HaHoBe]

Hi MarkOW,

maybe

Private Sub CommandButton6_Click()
Dim LastRow As Long
With ThisWorkbook.Sheets("Data")
  LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
  With .Range("B" & LastRow + 1)
    .Value2 = ComboBox3.Value
    MsgBox "Entry Exported To List", vbInformation
  End With
End With
End Sub

Ciao,
Holger

 

[MarkOW]

Hi MarkOW,

maybe

Private Sub CommandButton6_Click()
Dim LastRow As Long
With ThisWorkbook.Sheets("Data")
  LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
  With .Range("B" & LastRow + 1)
    .Value2 = ComboBox3.Value
    MsgBox "Entry Exported To List", vbInformation
  End With
End With
End Sub

Ciao,
Holger

Thanks, that was my original code but it gave the following error.

Thanks
Mark

 

[HaHoBe]

Hi MarokOW,

why dio you use Value2 (I would have expected either Value or Text), what is the content you want to copy? Any code involved for the sheet you try to write to?

Holger

 

[MarkOW]

Hi MarokOW,

why dio you use Value2 (I would have expected either Value or Text), what is the content you want to copy? Any code involved for the sheet you try to write to?

Holger

Value2 came from a spreadsheet I have been using successfully, Value gives the same error.

 

[HaHoBe]

Hi MarkOW,

in the version I use (Excel2019) the code supplied delivers the correct row to write to either without ListObject or with Listobject. As that may not be the cause for the error raised and you mentioned you have used a similar approach before and as you are the only person to give details on what differs between the original workbook and the one which causes the error: what is the content you try to put into the cell and are there any event codes being triggered in the actual workbook?

Maybe update your profile to show what Excel version you use on which platform (I don't have access to any newer versions on Windows nor to any on a Mac so I would be out of this).

Holger

 

[MarkOW]

Hi MarkOW,

in the version I use (Excel2019) the code supplied delivers the correct row to write to either without ListObject or with Listobject. As that may not be the cause for the error raised and you mentioned you have used a similar approach before and as you are the only person to give details on what differs between the original workbook and the one which causes the error: what is the content you try to put into the cell and are there any event codes being triggered in the actual workbook?

Maybe update your profile to show what Excel version you use on which platform (I don't have access to any newer versions on Windows nor to any on a Mac so I would be out of this).

Holger

I ended up splitting my table into separate tables (1 column per ComboBox) and this solved my issue.
Thanks for your input