PDA

View Full Version : Find nearest greater value



malarvel
08-10-2016, 11:43 PM
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:


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.
If grade pay value = 1900 ( i.e F2:F15) then find the value in Level2 of column(B1) of sheet2.
If grade pay value = 2000 ( i.e F2:F15) then find the value in Level3 of column(C1) of sheet2.
If grade pay value = 2400 ( i.e F2:F15) then find the value in Level4 of column(D1) of sheet2.
If grade pay value = 2800 ( i.e F2:F15) then find the value in Level5 of column(E1) of sheet2.
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
08-11-2016, 01:58 AM
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.