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
[TABLE="class: grid, width: 400"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]ProductCode[/TD]
[TD]Price /Unit[/TD]
[TD]Price /Box[/TD]
[TD]Current Band[/TD]
[/TR]
[TR]
[TD]Prod1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50 [/TD]
[TD] Jupiter[/TD]
[/TR]
[TR]
[TD]Prod2[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]125 [/TD]
[TD] Saturn[/TD]
[/TR]
[TR]
[TD]Prod3[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]265 [/TD]
[TD]Between Saturn and Jupiter[/TD]
[/TR]
[TR]
[TD]Prod4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25 [/TD]
[TD] < Jupiter[/TD]
[/TR]
</tbody>[/TABLE]


Banding Table
[TABLE="class: grid, width: 457"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ProductCode[/TD]
[TD]Venus /Unit[/TD]
[TD]Mars /Unit[/TD]
[TD]Saturn /Unit[/TD]
[TD]Jupiter /Unit[/TD]
[/TR]
[TR]
[TD]Prod1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Prod2[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Prod3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Prod4[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!