Searching a row one cell after another

[Neka]

Hello,
Please I'm writing a macro that is suppose to search a worksheet and find a
specific cell in a row that matches what the user enter in the search box.
Once the data matches a cell on a row then I want to perform another search
on the column that corresponds to the row.
So first a horzontal search on the row to find matching cell and then a
vertical search on the column to find another matching cell

 

[Rick Rothstein]

Is the second search (the one down the column) for the same text used to
find the first cell, or is it for entirely different text? If different
text, where does that text come from?

 

[Joel]

header = "Name"
Data = "123"
set c1 = rows(1).find(what:=Header,lookin:=xlvalues,lookat:=xlwhole)
if not c is nothing then
set c2 = c.entirecolumn.find(what:=Data,lookin:=xlvalues,lookat:=xlwhole)
end if

 

[chinnek]

The second search (down the column) is for another text and source is
from another named box (range).

Thanks

 

[chinnek]

What will c1 and c2 be declared as? Range?

Thanks

 

[Rick Rothstein]

Okay, your "search boxes" are named ranges. Replace my "SearchBox1" and
"SearchBox2" example named range names with your actual named range names
and give the following macro a try. Since you didn't say what you wanted to
do when you found the cell, I simply MessageBox'ed its address, but the
range named SecondFind references the cell you want (assuming both text
strings were found). Also, since you didn't mention if you wanted and exact
match or not, I assumed you wanted exact matches. You can change this by
changing the LookAt arguments (either one of them individually or both
together) to xlPart.

Sub FindFind()
Dim FirstFind As Range
Dim SecondFind As Range
Dim LastUsedRow As Long
Dim LastUsedColumn As Long
Const NamedRange1 As String = "SearchBox1"
Const NamedRange2 As String = "SearchBox2"
On Error GoTo NotFound
LastUsedRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlRows).Row
LastUsedColumn = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
Set FirstFind = ActiveSheet.Cells.Find(What:=Range(NamedRange1), _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
After:=Cells(LastUsedRow, LastUsedColumn), _
LookAt:=xlWhole)
If FirstFind.Address = Range("SearchBox1").Address Then
Set FirstFind = ActiveSheet.Cells.FindNext( _
Range(Range(NamedRange1).Address))
End If
Set SecondFind = ActiveSheet.Columns(FirstFind.Column).Find( _
What:=Range(NamedRange2), After:=FirstFind, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
LookAt:=xlWhole)
MsgBox "Found it at " & SecondFind.Address(0, 0)
Exit Sub
NotFound:
MsgBox "The text could not be found!"
End Sub

--
Rick (MVP - Excel)


The second search (down the column) is for another text and source is
from another named box (range).

Thanks