Send list box value to first unused cell in column

[ArtZ]

I want to send the current ListBox value to a list in column U via a Command Button click. The following code works somewhat except that it starts the list after the used cells in the "middle" of column U, rows 36 to 3000 instead at row 2, the first unused row.

Can anyone suggest how to modify the code place list box values starting in the first unused cell in column?

Private Sub CommandButton1_Click()
Dim lngLastRow As Long
Dim lngCol As Long
Dim lngIndex As Long

lngLastRow = Range("U" & Rows.Count).End(xlUp).Row + 1
lngCol = 21
Cells(lngLastRow, lngCol) = ListBox1.Value

End Sub

Part II if I am allowed to request at the same time, is to simultaneously place the value in cell P32 in a list in column V?

Can the above code be modified to place both the current list box value in the first unused cell in a list in column U, while at the same time placing the current value in cell P32 in a list starting at first unused cell in a list in column V.

Any help is greatly appreciated.

Thanks,

Art

[protonLeah]

I tested the posted code; it works as expected. So I'm not sure what you mean by "first unused cell". If you have a column with some groups of used cells with blank cells in between, then that code won't work; you'll have to check each cell in the entire column for blanks. It would help if you posted a copy of your workbook.

[ArtZ]

Ben,

Thanks for your response. What is happening is that the listbox value is placed in cell U3001 starting the list at that cell instead of cell U2, under the column heading in U1. U2 is the first unused cell in column U. Cells U2:U35 are blank while there numerical values in cells U36:U3000.

Can the code that I posted be modified to place the listbox list value to begin in cell U2?

Thanks,

Art

[protonLeah]

Try the following test code:

Option Explicit
Sub test()
     Dim ListData As Range, C As Range
     Dim LastV As Long
     
     Set ListData = Sheets("sheet1").Range("u2:u35")
     
     'find the last row in column V
     LastV = Sheets("Sheet1").Cells(Rows.Count, "V").End(xlUp).Row
     
     'loop through cells R2 - R35 to find the first blank and copy listbox value
     For Each C In ListData
          If C = "" And C.Row < 36 Then
               C.Value = ListBox1.Text
               
               'copy P32 to next cell in col V
               Range("V" & LastV).Value = Range("P32").Value
               Exit Sub
          End If
     Next C
End Sub

[ArtZ]

I tried putting your code in as follows:

Private Sub CommandButton1_Click()
     Dim ListData As Range, C As Range
     Dim LastV As Long
     
     Set ListData = Sheets("Stock Data").Range("u2:u35")
     
     'find the last row in column V
     LastV = Sheets("Sheet1").Cells(Rows.Count, "V").End(xlUp).Row
     
     'loop through cells R2 - R35 to find the first blank and copy listbox value
     For Each C In ListData
          If C = "" And C.Row < 36 Then
               C.Value = ListBoxStocks.Text
               
               'copy P32 to next cell in col V
               Range("V" & LastV).Value = Range("P32").Value
               Exit Sub
          End If
     Next C

When I execute the code, I receive an error 9, "subscript out of range" message. Am I doing something wrong?

Any suggestions?

Art

[royUK]

Try

Option Explicit

Private Sub CommandButton1_Click()
    Dim lngLastRow As Long
    Dim lngCol As Long
    Dim lngIndex As Long
    lngCol = 21
    lngLastRow = Range(35, lngCol).End(xlUp).Row + 1
    If lngLastRow < 35 Then
        Cells(lngLastRow, lngCol) = ListBox1.Value
    Else: MsgBox "No empty row available", vbCritical, "Error"
    End If
End Sub

[protonLeah]

What are your sheet names? You may need to replace the 'sheet1' in:

'find the last row in column V
LastV = Sheets("Sheet1").Cells(Rows.Count, "V").End(xlUp).Row

with the actual sheet/tab name.