Dim Array Dimension off search results

[lucky12341]

Is there a way to get this search code to set the count of rows to the number of entries found? Instead of Dim MyArray(500, 8) I was thinking Dim MyArray(i, 8) but seems to not work correctly...

Private Sub cmdLocationFindAll_Click()
    Dim MyArray(500, 8)
    Dim FirstAddress As String
    Dim strFind As String 
    Dim rSearch As Range 
    Dim fndA, fndB, fndC, fndD, fndE, fndF, fndG, fndH, fndI As String
    Dim head1, head2, head3, head4, head5, head6, head7, head8, head9 As String
    Dim i As Integer
    i = 1
    Set rSearch = Sheet10.Range("a2", Range("a10425").End(xlUp))
    strFind = Me.txtLocation.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then
            c.Select
            head1 = Range("a2").Value
            head2 = Range("b2").Value
            head3 = Range("c2").Value
            head4 = Range("d2").Value
            head5 = Range("e2").Value
            head6 = "C Qty"
            head7 = Range("g2").Value
            head8 = Range("h2").Value
            head9 = "O Qty"
            With Me.ListBox1
                MyArray(0, 0) = head1
                MyArray(0, 1) = head2
                MyArray(0, 2) = head3
                MyArray(0, 3) = head4
                MyArray(0, 4) = head5
                MyArray(0, 5) = head6
                MyArray(0, 6) = head7
                MyArray(0, 7) = head8
                MyArray(0, 8) = head9
            End With
            FirstAddress = c.Address
            Do
                fndA = c.Value
                fndB = c.Offset(0, 1).Value
                fndC = c.Offset(0, 2).Value
                fndD = c.Offset(0, 3).Value
                fndE = c.Offset(0, 4).Value
                fndF = c.Offset(0, 5).Value
                fndG = c.Offset(0, 6).Value
                fndH = c.Offset(0, 7).Value
                fndI = c.Offset(0, 8).Value
                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fndE
                MyArray(i, 5) = fndF
                MyArray(i, 6) = fndG
                MyArray(i, 7) = fndH
                MyArray(i, 8) = fndI
                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With
    Me.ListBox1.List() = MyArray
End Sub

 

[Norie]

I think you want Redim.

2 problems though:

1 I can't see where it would go in your code.

2 It only allows you to redimension the last dimension of an array.

By the way why don't you just assign values to the array instead of using a variable.

Like this.

MyArray(0,0)=Range("a2").Value
MyArray(0,1)=Range("b2").Value
MyArray(0,2)=Range("c2").Value
MyArray(0,3)=Range("d2").Value
MyArray(0,4)=Range("e2").Value
MyArray(0,5)=C Qty
MyArray(0,6)=Range("g2").Value
MyArray(0,7)=Range("h2").Value
MyArray(0,8)=O Qty

 

[lucky12341]

New to VBA...didn't know I could do that

Basically I want the array to only show the number of rows that are put into it. The only way I know to do this is to set the number of rows to a high number so that it shows all the results, but you can scroll through 500 rows regardless of the number of results.

currently=I use above code to search and say I get 19 results, the results get tossed into the array box but it shows 500 rows regardless.

need=I use above code to search and say get X results, the results get tossed into the array box and it shows X rows only.

Make sense?

 

[Norie]

What is that code actually supposed to do?

 

[lucky12341]

The code searches through sheet10 and finds whatever matches .txtPO (PO items are only located in column B) and places it into an array that is viewable (allows for easy edit changes etc, the sheet has between 15,000-30,000 rows at a time). I use the offsets to basically show me the row in the array since each row is a specific item/location/etc. I need the array though to base its number of rows off the number of results that the code finds and I do not know if this is possible...