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.