Help with If statment to compare values

[Vika]

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.

 

[KL]

Hi Vika,

One way:

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

Regards,
KL

 

[Max]

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,A1>55),"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
--

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.

 

[Bryan Hessey]

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.

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,A1>55),"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
--

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.

 

[Max]

"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.

 

[Ron Rosenfeld]

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