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.
Thanks!
Jerry
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.
Thanks!
Jerry
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?
Thanks!
Jerry
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
Loop
rowTill = x
End Function
[/VBA]
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.
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.
Then in any VBA where the row needs to be referenced use
OK thanks. Let me try it!
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
Display More
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.
Don’t have an account yet? Register yourself now and be a part of our community!