Userform's listbox selected items in worksheet's last blank row

[Reggieneo]

Hello Experts,
I have been trying to send the UserForm listbox's selected items in the worksheet horizontally.
I don't understand why I cant fill and send data to the next blank rows.

its always overwriting the existing data. I have tried adding plus 1 row too.


please see code below. much thanks.

  Dim lItem As Long
  Dim Col As Integer
  Col = 35
  Dim sht As Worksheet
  Dim LastRow As Long
  Stop
  Set sht = ActiveSheet
  For lItem = 0 To ListBox1.ListCount - 1
  If ListBox1.Selected(lItem) = True Then
  LastRow = sht.Cells(sht.Rows.Count, Col).End(xlUp).Row + 1

  Sheet135.Cells(LastRow + 1, Col).End(xlUp).Value = ListBox1.List(lItem)
  ListBox1.Selected(lItem) = False
  Col = Col + 1
  End If
  Next
  Unload Me
End Sub

reggieneo

 

[Hui]

You are adding 1 twice to setup the lastrow

LastRow = sht.Cells(sht.Rows.Count, Col).End(xlUp).Row + 1
Sheet135.Cells(LastRow + 1, Col).End(xlUp).Value = ListBox1.List(lItem)

Maybe just remove one of the +1's

You are also referencing potentially a different worksheet
sht.Cells(
+
Sheet135.Cells

 

[Reggieneo]

Hello hui,

Thanks for reply.

I have played on it long before I posted and I have done it also few times as what you mentioned yet I till can't make it work.

I have tried again just now , still the same.

  Dim Col As Integer
  Col = 35
  Dim sht As Worksheet
  Dim LastRow As Long
  'Stop
  Set sht = ActiveSheet
  For lItem = 0 To ListBox1.ListCount - 1
  If ListBox1.Selected(lItem) = True Then
  LastRow = Sheet135.Cells(sht.Rows.Count, Col).End(xlUp).Row
  Sheet135.Cells(LastRow + 1, Col).End(xlUp).Value = ListBox1.List(lItem)
  ListBox1.Selected(lItem) = False
  Col = Col + 1
  End If
  Next

 

[Hui]

Can you attach the file?

 

[Reggieneo]

Hi Hui
please see attached.

 

[Reggieneo]

Hi Hui,
figured it out.
I have moved the reference lastRow above and out of the if statement.

 LastRow = Sheet135.Cells(sht.Rows.Count, Col).End(xlUp).Row ' this line
 
  Col = 35
  Dim sht As Worksheet
  Dim LastRow As Long
 Set sht = ActiveSheet
  For lItem = 0 To ListBox1.ListCount - 1
  If ListBox1.Selected(lItem) = True Then
 
  Sheet135.Cells(LastRow + 1, Col).End(xlUp).Value = ListBox1.List(lItem)
  ListBox1.Selected(lItem) = False
  Col = Col + 1
  End If
  Next