Array Function problem

lucky12341

Board Regular
Joined
Nov 4, 2005
Messages
121
This command keeps giving me an error message, this is what is highlighted, MyArray(0, 1) = head1, any ideas whats wrong?

Private Sub CommandButton14_Click()
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("b2", Range("b65536").End(xlUp))
strFind = Me.TextBox2.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 = Range("g2").Value
head7 = Range("h2").Value
head8 = Range("i2").Value
head9 = Range("f2").Value
With Me.ListBox1
MyArray(0, 1) = head1
MyArray(0, 2) = head2
MyArray(0, 3) = head3
MyArray(0, 4) = head4
MyArray(0, 5) = head5
MyArray(0, 7) = head6
MyArray(0, 8) = head7
MyArray(0, 9) = head8
MyArray(0, 6) = head9
End With
FirstAddress = c.Address
Do
'Load details into Listbox
fndB = c.Value
fndA = 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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Forum statistics

Threads
1,212,144
Messages
6,106,218
Members
448,006
Latest member
oreo2996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top