Ошибка 380 vba

Just came back to you… a bit long, but here’s the general thoughts…

This all goes in the code for the ActiveX Control:

Option Explicit

Sub ListBox1_Click()
    Dim rw As Range, strtext As String
    Dim arr As Variant, ai As Long, aj As Long 
    Dim brr As Variant, bi As Long, bj As Long
    strtext = "a" 'I used this when i did my testing
    ReDim arr(11, 0)
    For Each rw In Range("rng")
        If InStr(LCase(rw.Value), strtext) Then
            aj = findaj(arr)
            If Not IsEmpty(arr(1, aj)) Then
                aj = aj + 1
                ReDim Preserve arr(11, aj)
            End If
            For ai = 1 To 11
                arr(ai, aj) = Cells(rw.Row, ai + 1).Value
            Next ai
        End If
    Next rw
    ReDim brr(aj, 11)
    For bi = 0 To aj
        For bj = 1 To 11
            brr(bi, bj) = arr(bj, bi)
        Next bj
    Next bi
    ListBox1.ColumnCount = 11
    ListBox1.List = brr
End Sub

Private Function findaj(ByVal brr As Variant)
    Dim j As Long, meow As String
    j = 0
    Do While True
        On Error GoTo toll
        j = j + 1
        meow = brr(1, j)
    Loop
toll:
    findaj = j - 1
End Function

So there’s a lot going on here… I use two separate arrays, due to how redimming arrays works in VBA. You can only update the second element of the array, so arr(ai,aj) can only have aj updated when I redim preserve while adding a new row to my array.

So we make an array (arr) that captures the data based on VBA’s limitations. Within that array, we use a function, findaj, which intentionally traps an error to determine the appropriate last column in arr (i italicized the use of column, as it’s not truly the case, but it makes sense spatially when thinking about it).

You then convert the array arr to brr in the appropriate order of columns/rows.

Afterwards, you make your .list = brr.

3

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Pick a username

Email Address

Password

By clicking “Sign up for GitHub”, you agree to our terms of service and
privacy statement. We’ll occasionally send you account related emails.

Already on GitHub?
Sign in
to your account