Blank cell contains a value when code is executed

Question

I’m using a userform with 12 listboxes (numbered 2-13). Each list box could contain 0-8 items assigned by user from main listbox1. I run the following code to output the content of each list box (12 boxes) to sheet “Tray” when a button is pressed.

Each listbox is then output into corresponding columns of each tray from columns B-M. Listbox2 fills column 1 of each tray and so on. A maximum of 4 trays can be filled. The code checks the 1st well of each tray and if it contains a value it assumes the tray is full & begins filling the next tray.

Problem: If the first tray contains a blank column(listbox) and the second tray contains values in the same listbox, the code will fill blank column of the frist tray with values that should be in the second tray. Please see pictures below and updated code below:

Listboxes 2,3 and 4 for Tray 1 (note listbox3 is empty)

Listboxes 2,3 and 4 for tray 2 (note listbox3 has data)

Code ran two times: Listbox3 from tray2 appears in tray1 (erroneously!!!)

Expected output:

Sub Worklist()
'
Dim Var, VarName As Variant
Dim i, DblDashPos, FirstPeriodPos, lngColNum, lngRowNum As Long
Dim item As ListBox

Const cstrNames As String = "Listbox2,Listbox3,Listbox4,Listbox5,Listbox6,Listbox7,Listbox8,Listbox9,Listbox10,Listbox11,Listbox12,Listbox13"

Application.ScreenUpdating = False

lngColNum = 2

For Each VarName In Split(cstrNames, ",")


    If UserForm2.Controls(VarName).ListIndex <> -1 Then 'if listbox is not blank

        If Sheets("Tray").Cells(4, lngColNum).Value = 0 Then
        'checks if value in row 3 column "lngColNum" is empty
            lngRowNum = 4
            ThisWorkbook.Sheets("Tray").Range("C2").Value = UserForm2.TextBox1.Value

        ElseIf Sheets("Tray").Cells(15, lngColNum).Value = 0 Then 'checks if value in row 14 column "lngColNum" is empty

            lngRowNum = 15

            ThisWorkbook.Sheets("Tray").Range("C13").Value = UserForm2.TextBox1.Value

        ElseIf Sheets("Tray").Cells(26, lngColNum).Value = 0 Then 'checks if value in row 14 column "lngColNum" is empty

            lngRowNum = 26

            ThisWorkbook.Sheets("Tray").Range("C24").Value = UserForm2.TextBox1.Value

        Else 'otherwise assumes tray starts in row 5, column "lngColNum"

            lngRowNum = 37
            ThisWorkbook.Sheets("Tray").Range("C35").Value = UserForm2.TextBox1.Value

        End If


        For i = 0 To UserForm2.Controls(VarName).ListCount - 1
            Var = UserForm2.Controls(VarName).List(i)

            DblDashPos = InStr(1, Var, "--")
            FirstPeriodPos = InStr(1, Var, ".")
            Sheets("Tray").Select
            ActiveSheet.Cells(lngRowNum, lngColNum) = Left(Var, DblDashPos - 1) & Right(Var, Len(Var) - FirstPeriodPos + 1)

            lngRowNum = lngRowNum + 1
        Next i

    End If


    lngColNum = lngColNum + 1

Next

Application.ScreenUpdating = True

End Sub

Thank you very much!

Answer 1

The problem is that you're only testing the column that corresponds to the ListBox to see if the cell is empty. If you want to test that all of the columns in a "tray" are empty, you need to test once for the entire sheet. Something like this (untested because I'm too lazy to rebuild your form):

Private Function FindFirstUnusedRow(sheet As Worksheet) As Long
    Dim testColumn As Long, testRow As Long
    Dim used As Boolean

    For testRow = 4 To 37 Step 11
        used = False
        For testColumn = 2 To 13
            If IsEmpty(sheet.Cells(testRow, testColumn)) = False Then
                used = True
                Exit For
            End If
        Next testColumn
        If used = False Then
            FindFirstUnusedRow = testRow
            Exit For
        End If
    Next testRow
End Function

Then in your code, call it before your loop:

Sub Worklist()
    Dim var As Variant
    Dim i As Long, dashPos As Long, periodPos As Long, colNum As Long
    Dim rowNum As Long, Dim sheet As Worksheet

    Application.ScreenUpdating = False
    Set sheet = ThisWorkbook.Sheets("Tray")
    rowNum = FindFirstUnusedRow(sheet)

    If rowNum = 0 Then
        Debug.Print "All trays full."
        Exit Sub
    End If

    Dim current As ListBox
    For colNum = 2 To 13
        Set current = UserForm2.Controls("Listbox" & colNum)
        If current.ListIndex <> -1 Then 'if listbox is not blank
            sheet.Cells(rowNum - 2, colNum).Value = UserForm2.TextBox1.Value
            For i = 0 To current.ListCount - 1
                var = current.List(i)
                dashPos = InStr(1, var, "--")
                periodPos = InStr(1, var, ".")
                sheet.Cells(rowNum + i, colNum) = Left$(var, dashPos - 1) & _
                                    Right$(var, Len(var) - periodPos + 1)
            Next i
        End If
    Next colNum
    Application.ScreenUpdating = True
End Sub

A couple other notes: You can ditch the Sheets("Tray").Select line entirely - you never use the selection object. Same thing with the mixed references to ActiveSheet and ThisWorkbook.Sheets("Tray"). Grab a reference and use it.

Also, these lines don't do what you think they do:

Dim Var, VarName As Variant
Dim i, DblDashPos, FirstPeriodPos, lngColNum, lngRowNum As Long

Of all the variables you declare, everything is a Variant except lngRowNum. If you want to combine declarations on one line like that, you still need to specify a type for each variable, or they'll default to Variant. See the example code above.