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
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