CODEWITHSUNDEEP
arraysvba
Tim
Tim

Reputation: 206

VBA add array to a list

Sub RangeBulkAmend()

   Set list = list.CreateInstance
     Dim c As Range
      Dim i As Long
      Dim myarr() As Variant

      For Each c In Selection

           list.Add c.value     
       Next c

            ReDim myarr(list.Count - 1)

         For i = 1 To list.Count - 1
             myarr(i) = list.Items(i)
             msg = msg & vbCrLf & myarr(i)

          Next i



          {{ListWindow.ListBox1.list = myarr}}

             Load ListWindow

             ListWindow.Show
end sub

i have an error on the compile as I try to pass my array to a list the code with double braces that where the compiler points too but if I highlight i get the message Object variable or With block variable not set any help will be gladly appreciated thank you in advance please note the list refereed to in the code above is my own custom list the issue is sending the array to the list box at the double braces checked the code it produces something now to extract that to a list box

Upvotes: 0

Views: 1809

Answers (1)

Scott Craner
Scott Craner

Reputation: 152525

If it is only your intention to load a list box with the selected cells values then:

Sub RangeBulkAmend()

    Dim myarr() As Variant

    myarr = Selection.Value

    Load ListWindow
    ListWindow.ListBox1.List = myarr
    ListWindow.Show
End Sub

Will do it

Or for that matter simply skipping the whole and just assigning the selection.Value to the listbox also works:

Sub RangeBulkAmend()

    Load ListWindow
    ListWindow.ListBox1.List = Selection.Value
    ListWindow.Show
End Sub

To mass add to an existing list in a list box try this:

Sub RangeBulkAmend()
    Load ListWindow
    Dim myarr() As Variant
    Dim oldarr() As Variant
    Dim t&, i&

    myarr = Selection.Value

    t = ListWindow.ListBox1.ListCount

    ReDim oldarr(0 To (ListWindow.ListBox1.ListCount + UBound(myarr, 1) - 1)) As Variant
    For i = 0 To UBound(oldarr)
        If i < ListWindow.ListBox1.ListCount Then
            oldarr(i) = ListWindow.ListBox1.List(i)
        Else
            oldarr(i) = myarr(i - t + 1, 1)
        End If
    Next i

    ListWindow.ListBox1.List = oldarr
    ListWindow.Show modal
End Sub

Upvotes: 1

Related Questions