VBA Function for comparing a value from a cell with values from another table

[IosifFlorin]

Hello! I am trying to write a new function because only with formulas is too complicated and inefficient

• i have 2 tables (names below)
• in the TableProd, header Current Band i want to find out where is positioned in the Banding Table (table 2) the Price for a certain Product.
• i am using the tables to make it easier for referencing

Important: the comparison of the prices have to start with the most aggressive band in table 2 (which is the one most to the right, not left).

In CurrentBand i have the some examples of answer

I want to extend the function, this is just and example but the names of the headers are with these specifics, but would be really helpful to get the blueprint which i could expand afterwards

TableProd

ProductCode	Price /Unit	Price /Box	Current Band
Prod1	10	50	Jupiter
Prod2	25	125	Saturn
Prod3	53	265	Between Saturn and Jupiter
Prod4	5	25	< Jupiter

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Banding Table

ProductCode	Venus /Unit	Mars /Unit	Saturn /Unit	Jupiter /Unit
Prod1	10	10	10	10
Prod2	50	25	25	20
Prod3	100	75	60	50
Prod4	50	30	20	10

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[sergioMabres]

Hi Iosif,
Thre is many ways to do thei, here is an idea
Create Function keyband with this code

Function keyband(k As String, p As Double, r As Range) As String
    Dim rcols, rrows, f As Integer
    rcols = r.Columns.Count
    rrows = r.Rows.Count
    ' Looks for the Key in columns 1 of r
    f = 0
    For i = 2 To rrows
       If (k = r.Cells(i, 1).Value) Then
          f = i
          Exit For
       End If
    Next i
    ' If it finds the key looks for the column now
    If (f > 1) Then
       For i = rcols To 2 Step -1
          ' equal value the first to the right
          If (p = r.Cells(f, i).Value) Then
             keyband = "= " + r.Cells(1, i).Value
             Exit For
          ElseIf (p < r.Cells(f, i).Value And i = rcols) Then
          ' less then the first one
             keyband = "< " + r.Cells(1, i).Value
             Exit For
          ElseIf (p > r.Cells(f, i).Value And i = 2) Then
          ' grater thethelast one
             keyband = "> " + r.Cells(1, i).Value
             Exit For
          ElseIf (p > r.Cells(f, i).Value And p < r.Cells(f, i - 1).Value And i > 2) Then
          ' between 2 values
             keyband = "Between " + r.Cells(1, i - 1).Value + " and " + r.Cells(1, i).Value
             Exit For
          Else
          ' Error no case was found, problably the table/range r is in a wrong order
             keyband = "Error"
          End If
       Next i
    Else
    ' Did not find the key
       keyband = "n/a"
    End If
End Function

Use the funcion in E4

=keyband(A4,B4,$A$11:$E$15)

Then get these results

Cheers
Sergio

 

[IosifFlorin]

Thanks a lot Sergio, works marvelously!
i changed a bit to work how i wanted, but is exactly what i needed help with

 

[sergioMabres]

Glad to help!