Searching a row one cell after another
 

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

Searching a row one cell after another
 

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?

--
Rick (MVP - Excel)


"Neka" wrote in message
...
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

Searching a row one cell after another
 

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,lo okat:=xlwhole)
end if

"Neka" wrote:

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

Searching a row one cell after another
 

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

Thanks

On Jan 26, 11:02*am, "Rick Rothstein"
wrote:
Is the second search (theonedown the column) for the same text used to
find the firstcell, or is it for entirely different text? If different
text, where does that text come from?

--
Rick (MVP -Excel)

"Neka" wrote in message

...



Hello,
Please I'm writing a macro that is suppose to search a worksheet and find
a
specificcellin arowthat matches what the user enter in the search box.
Once the data matches acellon arowthen I want to performanother
search
on the column that corresponds to therow.
So first a horzontal search on therowto find matchingcelland then a
vertical search on the column to findanothermatchingcell- Hide quoted text -

- Show quoted text -

Searching a row one cell after another
 

What will c1 and c2 be declared as? Range?

Thanks

On Jan 26, 11:09*am, Joel wrote:
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,lo okat:=xlwhole)
end if



"Neka" wrote:
Hello,
Please I'm writing a macro that is suppose to search a worksheet and find a
specificcellin arowthat matches what the user enter in the search box.
Once the data matches acellon arowthen I want to performanothersearch
on the column that corresponds to therow.
So first a horzontal search on therowto find matchingcelland then a
vertical search on the column to findanothermatchingcell- Hide quoted text -

- Show quoted text -

Searching a row one cell after another
 

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)


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

Thanks

On Jan 26, 11:02 am, "Rick Rothstein"
wrote:
Is the second search (theonedown the column) for the same text used to
find the firstcell, or is it for entirely different text? If different
text, where does that text come from?

--
Rick (MVP -Excel)

"Neka" wrote in message

...



Hello,
Please I'm writing a macro that is suppose to search a worksheet and
find
a
specificcellin arowthat matches what the user enter in the search box.
Once the data matches acellon arowthen I want to performanother
search
on the column that corresponds to therow.
So first a horzontal search on therowto find matchingcelland then a
vertical search on the column to findanothermatchingcell- Hide quoted
text -

- Show quoted text -