My loop through an array is not working correctly, and can't seem to get past the first iteration of the loop

MisterSrosis

New Member
Joined
Jul 30, 2014
Messages
12
Code:
'For future purposes it's best to probably find a way to condesne the repating code for the 2 cases of whether user knows 1 or 2 facts about the component that he/she is trying to find
'Returns array of cell addresses and from which workbook and worksheet theyu came from.
Private Function FilterResultsEvenFurther(Results() As Variant) As Variant


Dim ct As Long
Dim ct2 As Long
Dim wkb As Workbook
Dim fileN As Variant
Dim sht As Worksheet
Dim c As Range
Dim arrCnt As Long
Dim cAddressArr() As Variant
Dim conf As Variant
Dim columnBeingAnalyzed As Integer
Dim ce As Range
Dim filterS As String
Dim filterS2 As String


conf = MsgBox("Do you think you know 2 general facts about your specific component? Example: When describing a resistor we consider resistance value, what type of resistor it is (power,variable,ceramic)", vbYesNo)
arrCnt = 0


If conf = vbYes Then
    filterS = Application.InputBox("Enter category that your sepcific component belongs to(Ex: resistor,the categories a resistor can belong to are power, variable, high voltage etc.):")
    filterS2 = Application.InputBox("Enter any further Identification/value/ID number of Component")
    Do
        ct = 0
        fileN = "labInventory" & "\" & Results(ct)
        Set wkb = Workbooks.Open(fileN)
        Set sht = wkb.Sheets(Results(ct + 1))
        sht.Activate
        For Each c In sht.UsedRange.Cells
            If InStr(c.Value, filterS) > 0 Then
                'checking the column in which the first filterstring was found for the 2nd filterString
                columnBeingAnalyzed = c.Column
                For Each ce In sht.UsedRange.Columns(columnBeingAnalyzed)
                    If InStr(c.Value, filterS2) > 0 Then
                        ReDim Preserve cAddressArr(0 To arrCnt + 2)
                        cAddressArr(arrCnt) = c.Address
                        cAddressArr(arrCnt + 1) = sht.Name
                        cAddressArr(arrCnt + 2) = wkb.Name
                        arrCnt = arrCnt + 3
                    Else
                        arrCnt = arrCnt
                    End If
                Next ce
            End If
        Next c
        ActiveWorkbook.Close (False)
        ct = ct + 2
    Loop While Results(ct) <> ""
Else
    filterS2 = Application.InputBox("Enter any further Identification/value/ID number of Component")
    Do
        ct = 0
        arrCnt = 0
        fileN = "labInventory" & "\" & Results(ct)
        Set wkb = Workbooks.Open(fileN)
        Set sht = wkb.Sheets(Results(ct + 1))
        sht.Activate
        For Each c In sht.UsedRange.Cells
            If InStr(c.Value, filterS2) > 0 Then
                ReDim Preserve cAddressArr(0 To arrCnt + 2)
                cAddressArr(arrCnt) = c.Address
                cAddressArr(arrCnt + 1) = sht.Name
                cAddressArr(arrCnt + 2) = wkb.Name
                arrCnt = arrCnt + 3
            Else
                arrCnt = arrCnt
            End If
        Next c
        ActiveWorkbook.Close (False)
        ct = ct + 2
    Loop While Results(ct) <> ""
End If


FilterResultsEvenFurther = cAddressArr


End Function

The Reason I structured my loop like that was because of how I organized the array that I passed into this function ,from this snapshot, you can see the contents of this arrayGyazo - 97a82c766263bce3e6e090e33018a532.pngGyazo - 57fc07d2301f33781bb708ae363ec28f.png, my problem is my loop can't seem to get past the Components Workbook, even though there is only 1 sheet that matches the criteria. So the 0th indice element is the Workbook, and the next element is the worksheet in that workbook, where a result was found.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

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