Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 th cells A2-A10 and run it through the "low" and "high" range an automatically enter the correct codes to cells B2-B10 in the dat 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 hel -- Vika. ----------------------------------------------------------------------- Vika.F's Profile: http://www.excelforum.com/member.php...fo&userid=2625 View this thread: http://www.excelforum.com/showthread.php?threadid=39553 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that runs entered value through "low" and "high" range
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. Vika.F Wrote: 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 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=395530 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that runs entered value through "low" and "high" range
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 -- 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that runs entered value through "low" and "high" range
Bob, I tried your function and it works great! I spent so much time writin a long VB code for each cell But your function makes it s simple. Thank you so much!!! :) Thank you to everyone who replie -- Vika. ----------------------------------------------------------------------- Vika.F's Profile: http://www.excelforum.com/member.php...fo&userid=2625 View this thread: http://www.excelforum.com/showthread.php?threadid=39553 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting by "High", "Medium" and "Low" | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |