ReDim an array of variant - Help

D

doj

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top