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

  • Hi,

    Suppose the current Cell is C1000, how do I find the closest cell (C600) that has a value of 20, without looping?
    The direction would always be up, in the same column.
    The return value would be the row number.



  • 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.

  • That works but I need a VBA version, without the excel formulas.
    Searching the entire column maybe too slow since I have 500k+ rows!
    Is there any way to search from the current cell up to the closest matched cell using VBA macro?


  • Here my actually code, with a loop.
    I just need the same thing without the loop since it really drags on speed.

    [VBA]Function rowTill(col As Integer, startBar As Long, target As Variant) As Integer
    x = 0
    j = startBar

    Do Until dataSheet.Cells(j - x, col).Value = target
    x = x + 1
    rowTill = x

    End Function


  • 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.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thanks for the suggestion.
    How do I adapt the array formula you posted to VBA?
    Cell based formula wouldn't work for me since I have to call that function from another Sub.

    Many thanks!

  • 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

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • 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

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

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Code
    Sub aaa()
    Dim r as range,y
    Set r = range("c1:c" & activesheet.usedrange.specialcells(11).row)
     y = Filter(Application.Transpose(Evaluate("if(" & r.address & " =  20,row(" & r.address & "))")),false,0)
    if ubound(y)>= 0 then msgbox join(y,vblf)
  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!