How can I create an array from a range which is comprised of all the cells in one column that are being used and loop over using a for each statement?

[MisterSrosis]

'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 wkbookAndWksheetInfo As String
Dim wkb As Workbook
Dim wrdArr() As String
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 Variant
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")
    For ct = 0 To UBound(Results)
        wkbookAndWksheetInfo = Results(ct)
        wrdArr = Split(wkbookAndWksheetInfo)
        fileN = "labInventory" & "\" & wrdArr(0)
        Set wkb = Workbooks.Open(fileN)
        Set sht = wkb.Sheets(wrdArr(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 columnBeingAnalyzed
                    If InStr(c.Value, filterS2) > 0 Then
                        arrCnt = arrCnt + 1
                        cAddressArr(arrCnt) = c.Address + "" + sht.Name + "" + wkb.Name
                    End If
                Next ce
            End If
        Next c
    Next ct
Else
filterS2 = Application.InputBox("Enter any further Identification/value/ID number of Component")
For ct = 0 To UBound(Results)
    arrCnt = 0
    wkbookAndWksheetInfo = Results(ct)
    wrdArr = Split(wkbookAndWksheetInfo, "")
    fileN = "labInventory" & "\" & wrdArr(0)
    Set wkb = Workbooks.Open(fileN)
    Set sht = wkb.Sheets(wrdArr(1))
    sht.Activate
    For Each c In sht.UsedRange.Cells
        If InStr(c.Value, filterS2) > 0 Then
            arrCnt = arrCnt + 1
            cAddressArr(arrCnt) = c.Address + "" + sht.Name + "" + wkb.Name
        End If
    Next c
Next ct
End If
FilterResultsEvenFurther = cAddressArr
End Function

The problem lies in this part

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
                'When the cell with the keyword is found, I want to somehow reference the entire column the cell was found                 'in, and loop over all of the cells being used in that column to find a second filterstring = filterS2
                columnBeingAnalyzed = c.Column
                For Each ce In columnBeingAnalyzed
                    If InStr(c.Value, filterS2) > 0 Then
                        arrCnt = arrCnt + 1
                        cAddressArr(arrCnt) = c.Address + "" + sht.Name + "" + wkb.Name
                    End If
                Next ce
            End If
        Next c

 

[JLGWhiz]

When you use the c.Column, it is an integer value, although you have declared it variant with the variable columnBeingAnalyzed). To make it work for you, use it as an integer.

For Each ce In Columns(columnBeingAnalyzed)

 

[MisterSrosis]

Changed the code again, and would change the title, because the new problem is that I have the last row of the column I want to filter, but i have no way of referencing that column as A, and also I don't want to hard code it as A, I want it in the general case so that given this expression, the program will just reference anything as the letter
so the end result in the code I will have columnBeingAnalyzed = Range(referenceToA & c.Row: ReferenceToA & lastRow), and then I would jsut have like nested for loops to loop through it and do stuff, problem now is I have ABSOLUTELY NO CLUE HOW TO CREATE THAT REFERENCE.....

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
                lastRow = Cells(Rows.Count, c.Column).End(xlUp).Row
                columnBeingAnalyzed = Range(
                For Each ce In columnBeingAnalyzed
                    If InStr(c.Value, filterS2) > 0 Then
                        arrCnt = arrCnt + 1
                        cAddressArr(arrCnt) = c.Address + "" + sht.Name + "" + wkb.Name
                    End If
                Next ce
            End If
        Next c

Edit: Actually the above response seems like a much more efficient way of handling the problem....I'll give it a try and give you guys an update. :D

 

[JLGWhiz]

Referencing a column or a row is really pretty simple when you have any known range identified in the code, whether in Range("A1"), Cells(1, 1) or variable format, so long as the actual cell location is known for the range then the .Row or .Column can be used to create a row or column reference. For example, If you know you have found a match for a value in column B and you want to now do something to an item in column F on the same row, but don't know the row number, then using the range object variable 'c' you can do either:

Range("F" & c.Row) = 'some value

Or

c.Offset(0, 3) = 'some value

The statements are equal in results. The key is using the know range as an anchor point and the other location is defined in relative reference either by offset or .Row and .Column property to define the row or column as a numerical value.