Hello,

I am trying to find a value within a group. I have a spreadsheet that I
have formatted and sorted so that it looks like:

Group Prod Name Amt.
a 123-33 chair 37.5
a 123-44 chair 38.01
a 123-45 chair 39.41
b 133-75 Table 33.25
b 133-68 Table 35.14
b 133-55 Table 30.75
b 133-53 Table 61.3
c 168-15 couch 60.28
c 169-74 couch 60.15
d 115-01 loveseat 75.01
d 115-02 loveseat 72.75
d 115-03 loveseat 73.05


Now as I am going through code I need to retrieve the Prod number. The only
thing I have to search with is the amount and group. For example:

I need to search first for group c and then search for 60.15 within the rows
starting with group c to return 169-74.

The file gets quite large and I was hoping a find method of some sort that
would let me find two values. I could do a loop to evaluate the group column
and then the amount column. Another way would be to find the first
occurrence of the group C and then the last row of group c and create a
range. Then I could loop through that range for the amount. Both methods
seem to be a long way to accomplish this.

Any ideas on a way to find the values in two columns?

Thanks,

Tony

This code should be close to what you want. It finds all intances of "c" in
column A and returns that range of cells. It then takes that range and
offsets it 3 columns to the left and searches that range for 60.15... (note
the code needs to used XL2000 or better but it can be modified to work in
lesser versions. Also note that it does not requir the sheet to be sorted as
you have it now. finally it will find all intances of 60.15 that match, not
just one)

Public Sub DoStuff()
Dim rng As Range

Set rng = FindStuff("c", Columns("A"))
If Not rng Is Nothing Then Set rng = FindStuff(60.15, rng.Offset(0, 3))
If Not rng Is Nothing Then rng.Select
End Sub

Public Function FindStuff(ByVal varWhat As Variant, ByVal rngToSearch As
Range) As Variant
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set FindStuff = Nothing
Set rngFound = rngToSearch.Find(What:=varWhat, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Set rngFoundAll = rngFound
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
Set FindStuff = rngFoundAll
End If
End Function
--
HTH...

Jim Thomlinson


"Webtechie" wrote:

Hello,

I am trying to find a value within a group. I have a spreadsheet that I
have formatted and sorted so that it looks like:

Group Prod Name Amt.
a 123-33 chair 37.5
a 123-44 chair 38.01
a 123-45 chair 39.41
b 133-75 Table 33.25
b 133-68 Table 35.14
b 133-55 Table 30.75
b 133-53 Table 61.3
c 168-15 couch 60.28
c 169-74 couch 60.15
d 115-01 loveseat 75.01
d 115-02 loveseat 72.75
d 115-03 loveseat 73.05


Now as I am going through code I need to retrieve the Prod number. The only
thing I have to search with is the amount and group. For example:

I need to search first for group c and then search for 60.15 within the rows
starting with group c to return 169-74.

The file gets quite large and I was hoping a find method of some sort that
would let me find two values. I could do a loop to evaluate the group column
and then the amount column. Another way would be to find the first
occurrence of the group C and then the last row of group c and create a
range. Then I could loop through that range for the amount. Both methods
seem to be a long way to accomplish this.

Any ideas on a way to find the values in two columns?

Thanks,

Tony

It offsets 3 columns to the right, not the left as I indicated...
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

This code should be close to what you want. It finds all intances of "c" in
column A and returns that range of cells. It then takes that range and
offsets it 3 columns to the left and searches that range for 60.15... (note
the code needs to used XL2000 or better but it can be modified to work in
lesser versions. Also note that it does not requir the sheet to be sorted as
you have it now. finally it will find all intances of 60.15 that match, not
just one)

Public Sub DoStuff()
Dim rng As Range

Set rng = FindStuff("c", Columns("A"))
If Not rng Is Nothing Then Set rng = FindStuff(60.15, rng.Offset(0, 3))
If Not rng Is Nothing Then rng.Select
End Sub

Public Function FindStuff(ByVal varWhat As Variant, ByVal rngToSearch As
Range) As Variant
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set FindStuff = Nothing
Set rngFound = rngToSearch.Find(What:=varWhat, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Set rngFoundAll = rngFound
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
Set FindStuff = rngFoundAll
End If
End Function
--
HTH...

Jim Thomlinson


"Webtechie" wrote:

Hello,

I am trying to find a value within a group. I have a spreadsheet that I
have formatted and sorted so that it looks like:

Group Prod Name Amt.
a 123-33 chair 37.5
a 123-44 chair 38.01
a 123-45 chair 39.41
b 133-75 Table 33.25
b 133-68 Table 35.14
b 133-55 Table 30.75
b 133-53 Table 61.3
c 168-15 couch 60.28
c 169-74 couch 60.15
d 115-01 loveseat 75.01
d 115-02 loveseat 72.75
d 115-03 loveseat 73.05


Now as I am going through code I need to retrieve the Prod number. The only
thing I have to search with is the amount and group. For example:

I need to search first for group c and then search for 60.15 within the rows
starting with group c to return 169-74.

The file gets quite large and I was hoping a find method of some sort that
would let me find two values. I could do a loop to evaluate the group column
and then the amount column. Another way would be to find the first
occurrence of the group C and then the last row of group c and create a
range. Then I could loop through that range for the amount. Both methods
seem to be a long way to accomplish this.

Any ideas on a way to find the values in two columns?

Thanks,

Tony