+ Reply to Thread
Results 1 to 6 of 6

Macro that runs entered value through "low" and "high" range

  1. #1
    Registered User
    Join Date
    08-13-2005
    Posts
    3

    Question Macro that runs entered value through "low" and "high" range

    Is there a way to write a macro that can take the entered value from the cells A2-A10 and run it through the "low" and "high" range and automatically enter the correct codes to cells B2-B10 in the data sheet.

    code low high
    1 0 5
    2 6 10
    3 11 15
    4 16 20
    5 21 25
    6 16 30
    7 31 35
    8 36 40
    9 41 45
    10 46 50
    11 51 55

    For Example:
    A B
    7 2
    3 1
    53 11
    42 9
    32 7
    53 11
    14 3
    28 6
    5 1

    Thank you very much for any help

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    The easy way is to use the Low value and Code columns only, say A1 to A10 has 0,6,11,16,21,26,31,36,41,46 and column B contains the relevant code, and assuming that your data started in A20, the vlookup would be

    =VLOOKUP(A20,$A$1:$B$10,2,TRUE)

    which can be formula-dragged down the B column from B20 onwards.

    Hope this helps.


    Quote Originally Posted by Vika.F
    Is there a way to write a macro that can take the entered value from the cells A2-A10 and run it through the "low" and "high" range and automatically enter the correct codes to cells B2-B10 in the data sheet.

    code low high
    1 0 5
    2 6 10
    3 11 15
    4 16 20
    5 21 25
    6 16 30
    7 31 35
    8 36 40
    9 41 45
    10 46 50
    11 51 55

    For Example:
    A B
    7 2
    3 1
    53 11
    42 9
    32 7
    53 11
    14 3
    28 6
    5 1

    Thank you very much for any help

  3. #3
    Bob Phillips
    Guest

    Re: Macro that runs entered value through "low" and "high" range

    Vika,

    No macro necessary, just a function.

    Assuming the low-high table is in K1:L11, then just put this formula in B1

    =MATCH(A1,$K$1:$K$11,1)

    and copy down

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Vika.F" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Is there a way to write a macro that can take the entered value from the
    > cells A2-A10 and run it through the "low" and "high" range and
    > automatically enter the correct codes to cells B2-B10 in the data
    > sheet.
    >
    > code low high
    > 1 0 5
    > 2 6 10
    > 3 11 15
    > 4 16 20
    > 5 21 25
    > 6 16 30
    > 7 31 35
    > 8 36 40
    > 9 41 45
    > 10 46 50
    > 11 51 55
    >
    > For Example:
    > A B
    > 7 2
    > 3 1
    > 53 11
    > 42 9
    > 32 7
    > 53 11
    > 14 3
    > 28 6
    > 5 1
    >
    > Thank you very much for any help
    >
    >
    > --
    > Vika.F
    > ------------------------------------------------------------------------
    > Vika.F's Profile:

    http://www.excelforum.com/member.php...o&userid=26255
    > View this thread: http://www.excelforum.com/showthread...hreadid=395530
    >




  4. #4
    Nigel
    Guest

    Re: Macro that runs entered value through "low" and "high" range

    Can you re-phrase the question. What are the correct code and how do you
    determine these from the high low values ?

    --
    Cheers
    Nigel



    "Vika.F" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Is there a way to write a macro that can take the entered value from the
    > cells A2-A10 and run it through the "low" and "high" range and
    > automatically enter the correct codes to cells B2-B10 in the data
    > sheet.
    >
    > code low high
    > 1 0 5
    > 2 6 10
    > 3 11 15
    > 4 16 20
    > 5 21 25
    > 6 16 30
    > 7 31 35
    > 8 36 40
    > 9 41 45
    > 10 46 50
    > 11 51 55
    >
    > For Example:
    > A B
    > 7 2
    > 3 1
    > 53 11
    > 42 9
    > 32 7
    > 53 11
    > 14 3
    > 28 6
    > 5 1
    >
    > Thank you very much for any help
    >
    >
    > --
    > Vika.F
    > ------------------------------------------------------------------------
    > Vika.F's Profile:

    http://www.excelforum.com/member.php...o&userid=26255
    > View this thread: http://www.excelforum.com/showthread...hreadid=395530
    >




  5. #5
    Registered User
    Join Date
    08-13-2005
    Posts
    3
    I need an If Then statement that will take entered value from A1:A10 and compares it through range:
    code low high
    1 0 5
    2 6 10
    3 11 15
    4 16 20
    5 21 25
    6 16 30
    7 31 35
    8 36 40
    9 41 45
    10 46 50
    11 51 55
    And then assigns correct code to cell B1:B10. For example if the entered value in cell A1 is 5, it will automatically enter code 1 to cell B1.
    The If Then statement has take value from A1:A2 and assign code to B1:B10.

    Thank you

  6. #6
    Registered User
    Join Date
    08-13-2005
    Posts
    3

    Thank you

    Bob,

    I tried your function and it works great! I spent so much time writing a long VB code for each cell But your function makes it so simple. Thank you so much!!!

    Thank you to everyone who replied

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1