VBA Non-Loop Find Cell from Current Cell based on Value

Maybe something like the attached file. In G1 enter the value for which you want the Row returned, there are 2 formulas, one finds an exact match of the value in G1, the other the closest match.

A formula is always going to be faster than VBA, no matter how many rows of data (in fact the more rows there are the difference in speed becomes even greater!).

I missed the bit about needing a non exact match to return the row for the nearest value above the searched value.

Try this

=IFERROR(IFERROR(ROW(INDEX(C:C,MATCH(G1,C:C,0))),ROW(INDEX(C:C,MATCH(MIN(IF(C:C>G1,C:C)),C:C,0)))),"Search value greater than max value in data")

Note this is an Array Formula and so must be confirmed by pressing ctrl+shift+enter not just enter.

This should return the correct row for both an exact match and a non-exact match

Your VBA code could be made considerably faster by using an array based code rather than object based. To do the code without a loop would mean using something like the array formula I have proposed, but adapting it for use in VBA.

Even without a loop VBA would be slower than an Excel built-in function.

This is the way I would go.

• Leave the formula on the worksheet
• Name the cell where the value to be searched for is entered "SrchVal"
• Name the cell with the formula "_Row"

Then in any VBA where the row needs to be referenced use

Sub Example()
    Dim lRow As Long
    
    [SrchVal] = 20 '// You can make the 20 a variable that can be changed by other code
    lRow = [_Row]
    
    '// Your code that needs the Row returned by the formula here
    '// using lRow variable for the required row
    
End Sub

On reflection, there is no point adapting the array formula for use in the VBA, because an array formula is a looping formula anyway!

If you still want to get the Row for a given value using VBA rather then a formula then this array based code would be faster than your code.

Try this on the sample file

Sub GetRow()
    Dim lRow As Long
    
    lRow = rowTill(3, 1, 22)
    Debug.Print lRow
    
End Sub


Function rowTill(lCol As Long, lStartBar As Long, SrchVal) As Integer
    Dim lRow As Long, r As Range, d As Double
    
    With ActiveSheet '// Change this to a specific sheet if required
        lRow = .Cells(.Rows.Count, lCol).End(xlUp).Row
        x = .Range(.Cells(lStartBar, lCol), .Cells(lRow, lCol))
        On Error GoTo NoMatch
        rowTill = Application.Match(SrchVal, x, 0)
        On Error GoTo 0
        Exit Function
NoMatch:
        On Error GoTo 0
        For i = 1 To UBound(x, 1)
            If x(i, 1) > SrchVal Then
                If d = 0 Then
                    d = x(i, 1) - SrchVal: rowTill = lStartBar + i - 1
                ElseIf d > x(i, 1) - SrchVal Then
                    d = x(i, 1) - SrchVal
                    rowTill = lStartBar + i - 1
                End If
            End If
        Next
    End With
    
End Function

Note that code only performs a loop if an exact match is not found.

garah's code is a neat way to do away to do away with looping, but needed adapting for use with a variable search value and also to find the closest match above if no exact match.

I have adapted the code and placed it in a Worksheet_Change event code.

I am attaching 2 files, both have over 500,000 rows of data (same data in each) one (V4) uses a formula and the other (V3) uses VBA .

Change the Search Value for both and you will see that the formula version is still slightly faster than VBA.

Both files have data starting in Row 3, assuming that, from the code you did have, there is something above the list of values. If the start row is other than 3 you will need to change cell G2 on the V3 version . If the data column is other than C, in V3 change cell G1 to the actual data column, with the formulas in Cells !2 and J2 of the V4 file you will need to change all the Cs to whatever the actual data column is.

Note that with V3 cells G1:G3 and cells J3:K3 are named ranges and with V4 cells G1, I2 and J2 are named ranges. You can move them to anywhere on your actual sheet (making sure you add the names of the Named Ranges exactly as they are now, if you copy those cells to your actual sheet!).

Due to garah's filter/evaluate code the speed difference is very small, so you can go with whichever method suits you.

In both files I added a box that shows the actual value of the cell in the data column and returned row, that way you can easily see if there was an exact match or not (thought that might be useful for you )

Turns out files with 500,000 rows were too big for attaching, so I reduced data to 250,000 rows.