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

1. ## 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. 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.

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. ## 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" <Vika.F.1tp0ub_1123920303.6887@excelforum-nospam.com> wrote in
message news:Vika.F.1tp0ub_1123920303.6887@excelforum-nospam.com...
>
> 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
>

4. ## 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" <Vika.F.1tp0ub_1123920303.6887@excelforum-nospam.com> wrote in
message news:Vika.F.1tp0ub_1123920303.6887@excelforum-nospam.com...
>
> 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
>

5. 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. ## 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

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

#### 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