Losing data during transfer

[toocold]

Good morning,

I am really hoping this is going to be my last question on lisboxes. Anyway, I am trying to transfer data from a listbox to a spreadsheet. Unfortunately the first entry in the listbox always gets dropped and the specified cell is blank. (Unless I force the cycle to go one less than the number of options in the listbox. If I do this, then the remaining item in the listbox shows up as the selection in the first cell.) Anyway, here is my code. Any ideas on what I am doing wrong here. Been trying to figure this out since yesterday afternoon and I am guessing it is something simple...

Private Sub CommandButton5_Click()

Dim number As Integer
Dim xcount As Integer

number = 0
xcount = ListBox2.ListCount

ListBox2.ListIndex = 0

Do While xcount > 1

If ListBox2.Selected(i) = True Then
Worksheets("HistoricalFS").Cells(11 + number, 2) = ListBox2.List(i)
ListBox2.RemoveItem (i)

End If
number = number + 1
xcount = xcount - 1

Loop

End Sub

[K Dales]

Your loop executes "While xcount >1" - so it does not execute when xcount is
equal to 1. I think you really meant "While xcount >=1" (or, equivalently,
"While xcount >0)
--
- K Dales


"toocold" wrote:

>
> Good morning,
>
> I am really hoping this is going to be my last question on lisboxes.
> Anyway, I am trying to transfer data from a listbox to a spreadsheet.
> Unfortunately the first entry in the listbox always gets dropped and
> the specified cell is blank. (Unless I force the cycle to go one less
> than the number of options in the listbox. If I do this, then the
> remaining item in the listbox shows up as the selection in the first
> cell.) Anyway, here is my code. Any ideas on what I am doing wrong
> here. Been trying to figure this out since yesterday afternoon and I
> am guessing it is something simple...
>
> Private Sub CommandButton5_Click()
>
> Dim number As Integer
> Dim xcount As Integer
>
> number = 0
> xcount = ListBox2.ListCount
>
> ListBox2.ListIndex = 0
>
> Do While xcount > 1
>
> If ListBox2.Selected(i) = True Then
> Worksheets("HistoricalFS").Cells(11 + number, 2) =
> ListBox2.List(i)
> ListBox2.RemoveItem (i)
>
> End If
> number = number + 1
> xcount = xcount - 1
>
> Loop
>
> End Sub
>
>
> --
> toocold
> ------------------------------------------------------------------------
> toocold's Profile: http://www.excelforum.com/member.php...o&userid=31608
> View this thread: http://www.excelforum.com/showthread...hreadid=529717
>
>

[toocold]

Sorry K. Dales, just before I had posted the code I had tried to running it less one loop to see what was happening. I had originally set the code with While xcount > 0. I have also tried For-Next, offset, etc. None of these solve the problem.

A weird thing that I have noticed is that it is always B11 that is left blank. If I change the code to:

Worksheets("HistoricalFS").Cells(10 + number, 2) = ListBox2.List(i)

Whatever was supposed to be placed in B11 ends up being blank. I just can't figure out what is going on. Any ideas on how to fix the code or a better way to get the information from the listbox into the specified worksheet cells is really appreciated!

Cheers,
dw