CODEWITHSUNDEEP
excelvba
supernovice
supernovice

Reputation: 1

Found a cell with a particular value, how to reference that cell to search the entire column for another value

I am trying to write a program in excel where the user inputs a value, it searches the other worksheets for that value, finds the columns with that value, searches the rows of that column for dates that are later than today's date, and returns a the contents of column 1 of the rows with values later than today's date.

So far I've been able to find the cells with the input value, but I'm stuck with how to reference the column of the found cells to have the entire column searched for the date.

Below is what I have so far, and I think I should enter after "If Worksheets(i).Cells(2, j).Value = Method Then" another If statement: "If Worksheets(i).Cells(?, ?).Value >=Today() Then", but I'm not sure how to reference the cells I would like to search since those are contingent on where they are found in the previous statement. 

Private Sub CommandButton1_Click()

totalsheets = Worksheets.Count
Method = Worksheets("Search a Method").Cells(3, 6).Value

For i = 1 To totalsheets
    If Worksheets(i).Name <> "Search a Method" Then

    lastcolumn = Worksheets(i).Cells(2, Columns.Count).End(xlToLeft).Column

    For j = 2 To lastcolumn
        If Worksheets(i).Cells(2, j).Value = Method Then

        Worksheets("Search a Method").Activate

        lastrow = Worksheets("Search a Method").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("Search a Method").Cells(lastrow + 1, 1).Value = Worksheets(i).Name

        End If
    Next
    End If
Next

End Sub

Upvotes: 0

Views: 67

Answers (1)

Cyril
Cyril

Reputation: 6829

Will build on my suggestion about using .Find(), the below example should give some guidance:

Dim findrng As Range, col As Long, method As String
method = "dog"
With Sheets(1)
    Set findrng = .Rows(2).Find(what:=method, LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
End With
col = findrng.Column
Debug.Print col

I put "cat" in cells(2,2) and "dog" in cells(2,3), so with the above code, the immediate window reads "3", indicative of the column method was found.

Image of the above:

enter image description here

Upvotes: 1

Related Questions