I am getting an "Error 9 - subscript out of range" from the following
code when I try to ReDim the array "resultstore"

Any suggestions as to how to solve this?

Thanks in advance


Sub productSearch()
'
Dim resultstore() As Variant
Dim arrayCounter As Integer
Dim counter1 As Integer
Dim counter2 As Integer
Dim tempStore As String
Dim SKU As String
Dim loopCounter As Integer
Dim qty As Variant

' if the input into the text box is blank the do not run this
macro.
If textvalue < "" Then

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

arrayCounter = 0
counter1 = 2
counter2 = 4

this_workbook_name = ThisWorkbook.Name

' Open BomExtract file
Workbooks.Open Filename:=BomExtractFile, UpdateLinks:=False
Worksheets("BomTable").Select

SKU = Range("A1").Offset(counter1).Value
qty = Range("A1").Offset(counter1, counter2 + 1).Value

Do While SKU < ""

Do While qty < ""

tempStore = Range("A1").Offset(counter1,
counter2).Value
If Trim$(textvalue) = Trim$(tempStore) Then

arrayCounter = arrayCounter + 1



' this gives the Error 9 - subscript out of range

ReDim Preserve resultstore(arrayCounter, 2)



resultstore(arrayCounter, 1) = SKU
resultstore(arrayCounter, 2) = qty

End If

counter2 = counter2 + 3
SKU = Range("A1").Offset(counter1).Value
qty = Range("A1").Offset(counter1, counter2 + 1).Value

Loop

counter1 = counter1 + 1
counter2 = 4
SKU = Range("A1").Offset(counter1).Value
qty = Range("A1").Offset(counter1, counter2 + 1).Value

Loop

' Display the results
Windows(this_workbook_name).Activate
Worksheets("Search Results").Select

For loopCounter = 1 To arrayCounter

Range("A1").Offset(loopCounter + 2) =
resultstore(loopCounter, 1)
Range("A1").Offset(loopCounter + 2, 1) =
resultstore(loopCounter, 2)

Next loopCounter

Range("A1") = "The following Parents contain the component you
searched for (" _
& textvalue & " )"

Windows(BomExtractWindow).Close

End If

End Sub

You can only redelare the last element of the array so you need to turn your
array around

ReDim Preserve resultstore(2, arrayCounter)
resultstore(1, arrayCounter) = SKU
resultstore(2, arrayCounter) = qty

or you could use a user defined type (must be declared in a module)

Type InventoryItem
Sku as String
Qty as long
end type

dim resultstore() as InventoryItem

redim preserve resultstore(arrayCounter)
resultstore(arraycounter).Sku = SKU
resultstore(arraycounter).Qty = qty

I like this way becuase you know what each item in the array is and it is
easy to modify by just changing the type...
--
HTH...

Jim Thomlinson


"doj" wrote:

I am getting an "Error 9 - subscript out of range" from the following
code when I try to ReDim the array "resultstore"

Any suggestions as to how to solve this?

Thanks in advance


Sub productSearch()
'
Dim resultstore() As Variant
Dim arrayCounter As Integer
Dim counter1 As Integer
Dim counter2 As Integer
Dim tempStore As String
Dim SKU As String
Dim loopCounter As Integer
Dim qty As Variant

' if the input into the text box is blank the do not run this
macro.
If textvalue < "" Then

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

arrayCounter = 0
counter1 = 2
counter2 = 4

this_workbook_name = ThisWorkbook.Name

' Open BomExtract file
Workbooks.Open Filename:=BomExtractFile, UpdateLinks:=False
Worksheets("BomTable").Select

SKU = Range("A1").Offset(counter1).Value
qty = Range("A1").Offset(counter1, counter2 + 1).Value

Do While SKU < ""

Do While qty < ""

tempStore = Range("A1").Offset(counter1,
counter2).Value
If Trim$(textvalue) = Trim$(tempStore) Then

arrayCounter = arrayCounter + 1



' this gives the Error 9 - subscript out of range

ReDim Preserve resultstore(arrayCounter, 2)



resultstore(arrayCounter, 1) = SKU
resultstore(arrayCounter, 2) = qty

End If

counter2 = counter2 + 3
SKU = Range("A1").Offset(counter1).Value
qty = Range("A1").Offset(counter1, counter2 + 1).Value

Loop

counter1 = counter1 + 1
counter2 = 4
SKU = Range("A1").Offset(counter1).Value
qty = Range("A1").Offset(counter1, counter2 + 1).Value

Loop

' Display the results
Windows(this_workbook_name).Activate
Worksheets("Search Results").Select

For loopCounter = 1 To arrayCounter

Range("A1").Offset(loopCounter + 2) =
resultstore(loopCounter, 1)
Range("A1").Offset(loopCounter + 2, 1) =
resultstore(loopCounter, 2)

Next loopCounter

Range("A1") = "The following Parents contain the component you
searched for (" _
& textvalue & " )"

Windows(BomExtractWindow).Close

End If

End Sub