Find all values

[stone]

HI everyone.

I have a few tables, each table has it own name( gy, fr, gh, ect.) these tables all look alike, except the numerical values in them are not alike. On the horizontal headrow i have "Height" and in the vertical headcolumn i have "wheigt"

for example

Name: gy
____________________height_________
---------------150cm--160cm---180cm
________2Kg______4______3_______8
wheight_3kg______7______5_______5
________4kg______1______3_______2


Name: fr
--------------------height--------
______________150cm__160cm___180cm
________2Kg_____7______3______8
wheight_3kg_____6______4______3
________4kg_____0______6______1


if I enter for example 7 in a certain cell then I want to get the name of the table(s) and the corresponding wheight and height, for all values equal and grater than 7.

This outcome can be anywhere on the same sheet.


Multiple thanks in advance.

Best regards, stone
This message was edited by stone on 2002-04-26 05:57
This message was edited by stone on 2002-04-26 05:58

 

[cornbread]

Ok I have a way to find your value and get the other stuff greater than.
It's kind of a work around. Make your table so you have the height going from
least to greatest left to right. Weight least to greatest top to bottom. This sub will
find the value you enter in cell "c7" in the Weight Column then copy all the cells in that
table across and down from that value. It will then paste those values in the cell you specify
in "c8". Change the values in the sub to reflect the possible area in the table if the value was
the smallest possible height and weight. I could probably get the table name too but I have to return
to work now.


Option Compare Text
Sub find()
Dim output As String
Range("c7").Select
Cells.find(What:=ActiveCell.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
ActiveCell.Offset(0, 1).Select
Range(ActiveCell, ActiveCell.Offset(9, 5)).Copy
Range("c8").Select
output = ActiveCell.Value
Range(output).Select
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub
This message was edited by cornbread on 2002-04-26 08:15