Find nearest greater value

[malarvel]

I have a data in sheet1 range “A1:G15”. Range A1 (EMPID), B1(NAME), C1(DESIGNATION), D1(DEPARTMENT), E1(OLD BASIC PAY), F1(GRADE PAY), G1(NEW BASICPAY). In sheet2 I have pay matrix data range from A1: F42. The column heading have (Level1, Level2, Level3……..Level6 like this).

How to find nearest greater value using VBA in Excel?

Procedure:

1. I would like to find nearest greater value based on the range G2:G15 value, if grade pay value = 1800 ( i.e F2:F15) then find the value in Level1 of column(A1) of sheet2.
2. If grade pay value = 1900 ( i.e F2:F15) then find the value in Level2 of column(B1) of sheet2.
3. If grade pay value = 2000 ( i.e F2:F15) then find the value in Level3 of column(C1) of sheet2.
4. If grade pay value = 2400 ( i.e F2:F15) then find the value in Level4 of column(D1) of sheet2.
5. If grade pay value = 2800 ( i.e F2:F15) then find the value in Level5 of column(E1) of sheet2.
6. If grade pay value = 4200 ( i.e F2:F15) then find the value in Level6 of column(F1) of sheet2. How to find nearest greater value using

[xavier73]

Here is a solution:

Sub FindPAY()
    lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lastrow
     
        Select Case Sheets("Sheet1").Cells(i, 6)
            Case 1800
                Row = 1
            Case 1900
                Row = 2
            Case 2000
                Row = 3
            Case 2400
                Row = 4
            Case 2800
                Row = 5
            Case 4200
                Row = 6
        End Select
        
        
        For j = 2 To 41
            If Sheets("Sheet1").Cells(i, 7).Value > Sheets("Sheet2").Cells(j, Row) Then
                rowfound = j
                Exit For
            End If
        Next j
        Sheets("Sheet1").Cells(i, 8) = Sheets("Sheet2").Cells(rowfound - 1, Row)
    Next i
End Sub

There is no table for GRADE PAY 4000 in Sheet2. You have to add that and also add a case select for that.