I need an If Then statement that will take entered value from A1:A10 and compares it through "low" and "high" 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 to take value from A1:A2 and assign code to B1:B10.

I was thinking to do it this way:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [A1] 0 And [A1] <= 5 Then
[B1] = 1
ElseIf [A1] <= 10 And [A1] = 6 Then
[B1] = 2
ElseIf [A1] <= 15 And [A1] = 11 Then
[B1] = 3
ElseIf [A1] <= 20 And [A1] = 16 Then
[B1] = 4
ElseIf [A1] <= 25 And [A1] = 21 Then
[B1] = 6
ElseIf [A1] <= 30 And [A1] = 26 Then
[B1] = 7
ElseIf [A1] <= 35 And [A1] = 31 Then
[B1] = 8
ElseIf [A1] <= 40 And [A1] = 36 Then
[B1] = 9
ElseIf [A1] <= 45 And [A1] = 41 Then
[B1] = 10
ElseIf [A1] <= 50 And [A1] = 46 Then
[B1] = 11
ElseIf Target <= 55 And [A1] = 51 Then
[B1] = 12
End If
End Sub

But this way I end up with a long code for each cell. Is there any other way?
Thank you for any help.

Hi Vika,

One way:

Sub test()
With Application
[B1] = .RoundUp([A1] / 5, 0) - ([A1] = 0)
End With
End Sub

Regards,
KL


"Vika" wrote in message
...

I need an If Then statement that will take entered value from A1:A10 and
compares it through "low" and "high" 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 to take value from A1:A2 and assign code to
B1:B10.

I was thinking to do it this way:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [A1] 0 And [A1] <= 5 Then
[B1] = 1
ElseIf [A1] <= 10 And [A1] = 6 Then
[B1] = 2
ElseIf [A1] <= 15 And [A1] = 11 Then
[B1] = 3
ElseIf [A1] <= 20 And [A1] = 16 Then
[B1] = 4
ElseIf [A1] <= 25 And [A1] = 21 Then
[B1] = 6
ElseIf [A1] <= 30 And [A1] = 26 Then
[B1] = 7
ElseIf [A1] <= 35 And [A1] = 31 Then
[B1] = 8
ElseIf [A1] <= 40 And [A1] = 36 Then
[B1] = 9
ElseIf [A1] <= 45 And [A1] = 41 Then
[B1] = 10
ElseIf [A1] <= 50 And [A1] = 46 Then
[B1] = 11
ElseIf Target <= 55 And [A1] = 51 Then
[B1] = 12
End If
End Sub

But this way I end up with a long code for each cell. Is there any
other way?
Thank you for any help.


--
Vika

One way via formulas ..

Assuming this reference table is in Sheet1, A1:C12

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

In say, Sheet2, if values will be entered in A1:A10
you could put in B1:

=IF(A1="","",IF(OR(A1<0,A155),"out-of-range",INDEX(Sheet1!A:A,MATCH(A1,Shee
t1!B:B,1))))

then just copy B1 down to B10

Col B will return the desired code values from Sheet1 for the inputs in col
A

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Vika" wrote in message
...

I need an If Then statement that will take entered value from A1:A10 and
compares it through "low" and "high" 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 to take value from A1:A2 and assign code to
B1:B10.

I was thinking to do it this way:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [A1] 0 And [A1] <= 5 Then
[B1] = 1
ElseIf [A1] <= 10 And [A1] = 6 Then
[B1] = 2
ElseIf [A1] <= 15 And [A1] = 11 Then
[B1] = 3
ElseIf [A1] <= 20 And [A1] = 16 Then
[B1] = 4
ElseIf [A1] <= 25 And [A1] = 21 Then
[B1] = 6
ElseIf [A1] <= 30 And [A1] = 26 Then
[B1] = 7
ElseIf [A1] <= 35 And [A1] = 31 Then
[B1] = 8
ElseIf [A1] <= 40 And [A1] = 36 Then
[B1] = 9
ElseIf [A1] <= 45 And [A1] = 41 Then
[B1] = 10
ElseIf [A1] <= 50 And [A1] = 46 Then
[B1] = 11
ElseIf Target <= 55 And [A1] = 51 Then
[B1] = 12
End If
End Sub

But this way I end up with a long code for each cell. Is there any
other way?
Thank you for any help.


--
Vika

A method without 'if' is:

Using column X1 to X11 and Y1 to Y11 as helper columns, if you enter
the LOW range in column X, and the relavant CODE in column Y you could,
for data entered in A1 to A10, enter in B1

=VLOOKUP(A1,X$1:Y$11,2,TRUE)

and formula-drag that to B10 (the end of your data.

This will set code 4 for a value of 17 in your sample data, and will
enable you to amend or add to the lookup range quite easily should you
need to expand the range out to (say) 300. (remember to keep column X
sorted).

Hope this helps.



Max Wrote:
One way via formulas ..

Assuming this reference table is in Sheet1, A1:C12

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

In say, Sheet2, if values will be entered in A1:A10
you could put in B1:

=IF(A1="","",IF(OR(A1<0,A155),"out-of-range",INDEX(Sheet1!A:A,MATCH(A1,Shee
t1!B:B,1))))

then just copy B1 down to B10

Col B will return the desired code values from Sheet1 for the inputs in
col
A

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Vika" wrote in message
...

I need an If Then statement that will take entered value from A1:A10
and
compares it through "low" and "high" 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 to take value from A1:A2 and assign code
to
B1:B10.

I was thinking to do it this way:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [A1] 0 And [A1] <= 5 Then
[B1] = 1
ElseIf [A1] <= 10 And [A1] = 6 Then
[B1] = 2
ElseIf [A1] <= 15 And [A1] = 11 Then
[B1] = 3
ElseIf [A1] <= 20 And [A1] = 16 Then
[B1] = 4
ElseIf [A1] <= 25 And [A1] = 21 Then
[B1] = 6
ElseIf [A1] <= 30 And [A1] = 26 Then
[B1] = 7
ElseIf [A1] <= 35 And [A1] = 31 Then
[B1] = 8
ElseIf [A1] <= 40 And [A1] = 36 Then
[B1] = 9
ElseIf [A1] <= 45 And [A1] = 41 Then
[B1] = 10
ElseIf [A1] <= 50 And [A1] = 46 Then
[B1] = 11
ElseIf Target <= 55 And [A1] = 51 Then
[B1] = 12
End If
End Sub

But this way I end up with a long code for each cell. Is there any
other way?
Thank you for any help.


--
Vika


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=395631

"Bryan Hessey"
A method without 'if' is: ...
=VLOOKUP(A1,X$1:Y$11,2,TRUE)

Just a clarification that the IFs in the earlier formula suggested are
merely add-on error trapping for clearer outputs, which I normally provide.
The core formula's just a simple INDEX(...,MATCH(...,1)) with the
flexibility to read the source reference table as it is. Using VLOOKUP
requires a re-arrangement of the source table elsewhere as you have
described.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

On Sun, 14 Aug 2005 07:26:49 +0100, Vika
wrote:

But this way I end up with a long code for each cell. Is there any
other way?
Thank you for any help.


Try:

Range("B1").Value = Fix((Range("A1").Value - 1) / 5) + 1


--ron

Thank you to all of you for your help. I tried each provided option and they all work great!!!

Many, many thanks!!!! Super!!!....

Vika

You're welcome, Vika !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Vika" wrote in message
...

Thank you to all of you for your help. I tried each provided option and
they all work great!!!

Many, many thanks!!!! Super!!!....

Vika

As you're posting / reading from Excelbanter.com, you should be aware that
Excelbanter's interface seems to remove all traces of "greater than" or
"less than" operator symbols, including those within posted formulas.

For example, for the formula suggested in my response,
the .. IF( OR( A1 "less than" zero, A1 "greater than" 55 ) , .. error trap
part appears erroneous as: .. IF(OR(A10,A155), .. in Excelbanter

In case you might want to see it as it was posted,
try: http://tinyurl.com/a4zpp
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--