Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Find all values

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    iceland
    Posts
    138
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Oregon
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •