Matching a value to a column, (rounding) and returning offset column value

**jayinthe813** · 03-19-2013, 11:50 AM

I am dealing with 2 columns like below. What I want to do, via VBA, is to provide a number, say $3050, match it against column A (rounding up if necessary) and returning the item from column B.

For instance

mystring = "3150"

Value (COL A)	Perecentage (COL B)
$3,000	95%
$3,100	94.8%
$3,200	94.6%
$4000	94.4%

3150, should return 94.6%. It should always round up and numbers should always be between 3000-15000, I know my qualifier is:

If mystring > 3000 and mystring < 15000 Then

'do stuff here

end if

just not sure about how I should try to match the values up, thanks!

**Richard Buttrey** · 03-19-2013, 12:28 PM

Hi,

Do you really need a macro. With your chosen number in say D1 then

Formula:



=INDEX(B:B,MATCH(D1,A:A,1)+1,1)

will give you your answer. If you really do need a macro then just tuck this formula away somewhere and read the cell value directly into your VBA variable.

**hafizimran** · 03-19-2013, 12:37 PM

Function MatchValues(myString As String)
    Dim i As Long
    'Supposing that your data is in A and B columns starting from 2nd row
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        If Range("A" & i).Value >= Val(myString) Then
            MatchValues = Format(Range("B" & i).Value, "Percent")
            Exit Function
        End If
    Next
End Function

**jayinthe813** · 03-19-2013, 01:50 PM

Thanks to both of you! I could do either one but im automating with access and vba so the vba alternative makes more sense I think

**Richard Buttrey** · 03-19-2013, 02:42 PM

Hi,

Do either of these help. One is a function, so enter say
=Percentage(3150)
in a cell.

The other is an equivalent procedure which will result in the dResult value showing the percentage.

Function Percentage(lmyvalue As Long) As Variant
    Dim lRow As Long
    If lmyvalue > 3500 And lmyvalue < 15000 Then
        lRow = WorksheetFunction.Match(lmyvalue, Range("A:A"), 1)
        Percentage = Application.WorksheetFunction.Index(Range("B:B"), lRow, 1)
        Else
        Percentage = "Outside limits"
        
    End If
End Function

Sub GetPercentage()
    Dim lRow As Long, lmyvalue As Long, dResult As Variant

    lmyvalue = 3150    ' or use an input box to get a value
    If lmyvalue > 3500 And lmyvalue < 15000 Then

        lRow = WorksheetFunction.Match(lmyvalue, Range("A:A"), 1) + 1
        dResult = Format(Application.WorksheetFunction.Index(Range("B:B"), lRow, 1), "0.000")
    Else
        dResult = "Outside limits"
    End If
End Sub