CODEWITHSUNDEEP
excelvba
r z
r z

Reputation: 99

Looping through ListBox to enter values into sheet array

I would like to find the cells (or Rows) in Column B, Sheet1, who have matching values placed into ListBox2. Then, I'd like to change the value of a cell 4 columns over (using an Offset command).

I believe using a For loop is the most efficient way of going thru the values placed into ListBox2. I tried using a Forloop to go thru all values placed into ListBox2.List. Upon calling a value, the code would look for this value in Column B. Once found, it would "remember" the Row in which this value was found. Then, the code would use a Range/Offset command to change the value of a cell 4 columns over in that Row. 

Private Sub ButtonOK_Click()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim SerialList As Range
    Dim SerialRow As Long
    Dim i As Long
    Set wb = ActiveWorkbook
    Set ws = ActiveWorkbook.Worksheets("Sheet1")
    Dim strFind As Variant

With ws

    For i = 0 To Me.ListBox2.ListCount - 1

    Set SerialList = ws.Range("B:B").Find(What:=Me.ListBox2.List(i))
            SerialRow = SerialList.Row
            If Not SerialList Is Nothing Then
                ws.Range("B", SerialRow).Offset(0, 4).Value =     Me.ListBox2.List(i) 'error occurs here!
                MsgBox (ListBox2.List(i) & " found in row: " & SerialList.Row)
            Else
                MsgBox (ListBox2.List(i) & " not found")
            End If
    Next i

End With

End Sub

The MsgBoxes do say the correct ListBox2.List(i) value and the correct SerialList.Row, meaning that the program is correctly finding the row in which the list box value is located. However, I get an error saying that my range is not correctly defined at line "ws.Range("B", SerialRow)....."

How do I select the cell I'm searching for to correctly set it to =Me.ListBox2.List(i)?

Upvotes: 1

Views: 43

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

Couple of fixes:

Dim lv

'....

For i = 0 To Me.ListBox2.ListCount - 1
    lv = Me.ListBox2.List(i)
    Set SerialList = ws.Range("B:B").Find(What:=lv, LookAt:=xlWhole) '<< be more explicit
    'don't try to access SerialList.Row before checking you found a match...     
    If Not SerialList Is Nothing Then
        ws.Cells(SerialList.Row, "F").Value = lv '<< Cells in place of Range
        MsgBox (lv & " found in row: " & SerialList.Row)
    Else
        MsgBox (lv & " not found")
    End If
Next i

Upvotes: 1

Related Questions