CODEWITHSUNDEEP
excelvba
Tankard666
Tankard666

Reputation: 29

Look between which values of a list a given value is

I have the following list: 6, 12, 24, 36 and the integer values 1-36

For example if i have the value 7, i want to give back 6 from the first list.

More examples: 

from 1 to 6 -> 0,
from 7 to 12 -> 6,
from 13 to 24 -> 12,
from 25 to 36 -> 24

What function can i use for this ?

My idea was to loop trough my first list, but i don't know how to do this or if this can be done easier.

Upvotes: 0

Views: 45

Answers (3)

MYZ
MYZ

Reputation: 359

I assume your list is in the range "A1:A36" of a worksheet called "MySheet". There are several ways to loop through a column, one would be:

Option Explicit

Sub loopThroughList()
Dim c As Range
Dim rRange As Range
Dim myValue As Long
Dim myOutput As Long

Set rRange = Worksheets("MySheet").Range("A1:A36")


For Each c In rRange
    myValue = c.Value

    Select Case myValue
    Case 1 To 6
        myOutput = 0
    Case 7 To 12
        myOutput = 6
    Case 13 To 24
        myOutput = 12
    Case 25 To 36
        myOutput = 24
    End Select
Debug.Print myOutput

Next c

End Sub

Of you should generally be more careful when reading out values and assiging them to variables. But this should do the trick for now.

Upvotes: 0

L. Scott Johnson
L. Scott Johnson

Reputation: 4382

Excel formula (with value to be matched in A1):

=INDEX({0,6,12,24,36},MATCH(A1-1,{0,6,12,24,36},1))

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

You can use the WorksheetFunction.Match method in combination with Match_type:=1

So if you define a list

Dim LookupList As Variant
LookupList = Array(0, 6, 12, 24, 36)

you can lookup your value with

Dim InputValue As Long
InputValue = 1
LookupList(WorksheetFunction.Match(InputValue - 1, LookupList, 1) - 1)

The following demo will produce the output below:

Option Explicit

Public Sub Demo()
    Dim LookupList As Variant
    LookupList = Array(0, 6, 12, 24, 36)

    Dim InputValue  As Long
    For InputValue  = 1 To 40
        'InputValue is the value you look for
        Debug.Print InputValue , LookupList(WorksheetFunction.Match(InputValue  - 1, LookupList, 1) - 1)
    Next InputValue 
End Sub

Output:

 i             looked up value
 1             0 
 2             0 
 3             0 
 4             0 
 5             0 
 6             0 
 7             6 
 8             6 
 9             6 
 10            6 
 11            6 
 12            6 
 13            12 
 14            12 
 15            12 
 16            12 
 17            12 
 18            12 
 19            12 
 20            12 
 21            12 
 22            12 
 23            12 
 24            12 
 25            24 
 26            24 
 27            24 
 28            24 
 29            24 
 30            24 
 31            24 
 32            24 
 33            24 
 34            24 
 35            24 
 36            24 
 37            36 
 38            36 
 39            36 
 40            36

Upvotes: 0

Related Questions