use find twice to find cell on a specific row
 

We have a worksheet that contains a column of unique Identification
numbers and several different types of tests. The following example
shows the layout:

A B C D E F
G
1 LabID Test1 Test2 Test3 Test4 Test5 Test6
2 5123 Cd As Ag Cu Hg Zn
3 5124 Cr B Ba As Cd Ni
4 5125 Ni Pb Cu Cr Ag Be
5 5126 Pb Ag B Ba Be Cu

How would I find the cell that contains Ag for LabID 525?


Something like Find LabID = 5125 then look ONLY in that row to see if
one of the cells in that specific row contains the text Ag.

I do not want to find Ag in any row but the row that has the labid
5125. In this case - Ag is on the same row as the labid.

The problem is: if Ag is not on that the same row as the specified
LabId I do not need to find Ag on a following row. If Ag were not on
the row of LabID 5125 I would need to have
foundcell = nothing as the result.

2nd possibility:
Look at LabID 5124 - the row does not contain Ag. I need to be able to
say:
find 5124 then look in this row to see if one of the cells in this row
contains the text Ag. The result I need is foundcell is nothing.


I have tried using find twice in a row. First find the LabId then Find
the test. The problem is that if the particular test I am looking for
is not on the same row as the LabID in the first Find statement, the
test does appear several rows later. The test must be required on the
specific LabID.


Any help would be appreciated



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

use find twice to find cell on a specific row
 

You can do it with code like the following:

Dim cell As Range
Set cell = Columns(2).Find(What:="5125", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
Set cell = cell.EntireRow.Find(What:="ag", _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
cell.Select

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"captbluefin" wrote in message
...
We have a worksheet that contains a column of unique Identification
numbers and several different types of tests. The following example
shows the layout:

A B C D E F
G
1 LabID Test1 Test2 Test3 Test4 Test5 Test6
2 5123 Cd As Ag Cu Hg Zn
3 5124 Cr B Ba As Cd Ni
4 5125 Ni Pb Cu Cr Ag Be
5 5126 Pb Ag B Ba Be Cu

How would I find the cell that contains Ag for LabID 525?


Something like Find LabID = 5125 then look ONLY in that row to see if
one of the cells in that specific row contains the text Ag.

I do not want to find Ag in any row but the row that has the labid
5125. In this case - Ag is on the same row as the labid.

The problem is: if Ag is not on that the same row as the specified
LabId I do not need to find Ag on a following row. If Ag were not on
the row of LabID 5125 I would need to have
foundcell = nothing as the result.

2nd possibility:
Look at LabID 5124 - the row does not contain Ag. I need to be able to
say:
find 5124 then look in this row to see if one of the cells in this row
contains the text Ag. The result I need is foundcell is nothing.


I have tried using find twice in a row. First find the LabId then Find
the test. The problem is that if the particular test I am looking for
is not on the same row as the LabID in the first Find statement, the
test does appear several rows later. The test must be required on the
specific LabID.


Any help would be appreciated



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

use find twice to find cell on a specific row
 

Hi 'captn

Sometning like this may get you going. Where the table is set up in B2:H5,
change to suit of course. (Headers in row 1) Cell K1 is where you enter
the LabID number and K2 is where you enter the element symbol you are
searching for.

Sub SilverSeeker()
Dim bList As Range
Dim rngFind As Range
Dim LabId As Variant
Dim Elem As String
Dim rRow As Range
LabId = Range("K1").Value

If LabId = "" Then Exit Sub
LabId = Range("K1").Value
Elem = Range("K2").Value
Set bList = Range("B2:B5")
On Error Resume Next
bList.Select
Set rngFind = Selection.Find(what:=LabId)

If Not rngFind Is Nothing Then
Selection.Find(what:=LabId).Activate
Set rRow = ActiveCell.Offset(0, 1).Resize(1, 6)
rRow.Find(what:=Elem).Activate
If ActiveCell.Value = Elem Then
MsgBox Elem & " found in " & _
ActiveCell.Address, , "Found It"
Range("K1").Select
Exit Sub
ElseIf ActiveCell.Value < Elem Then
MsgBox " No " & Elem & " in row " & _
ActiveCell.Row, , "Didn't Find it"
Range("K1").Select
End If
End If
End Sub

HTH
Regards,
Howard

"captbluefin" wrote in message
...
We have a worksheet that contains a column of unique Identification
numbers and several different types of tests. The following example
shows the layout:

A B C D E F
G
1 LabID Test1 Test2 Test3 Test4 Test5 Test6
2 5123 Cd As Ag Cu Hg Zn
3 5124 Cr B Ba As Cd Ni
4 5125 Ni Pb Cu Cr Ag Be
5 5126 Pb Ag B Ba Be Cu

How would I find the cell that contains Ag for LabID 525?


Something like Find LabID = 5125 then look ONLY in that row to see if
one of the cells in that specific row contains the text Ag.

I do not want to find Ag in any row but the row that has the labid
5125. In this case - Ag is on the same row as the labid.

The problem is: if Ag is not on that the same row as the specified
LabId I do not need to find Ag on a following row. If Ag were not on
the row of LabID 5125 I would need to have
foundcell = nothing as the result.

2nd possibility:
Look at LabID 5124 - the row does not contain Ag. I need to be able to
say:
find 5124 then look in this row to see if one of the cells in this row
contains the text Ag. The result I need is foundcell is nothing.


I have tried using find twice in a row. First find the LabId then Find
the test. The problem is that if the particular test I am looking for
is not on the same row as the LabID in the first Find statement, the
test does appear several rows later. The test must be required on the
specific LabID.


Any help would be appreciated



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/