Most efficent way to find first occurance of a value in a range

[eikogs]

I am trying to improve a bad habit of using For loops to loop trough a column to find the a value so that can use the row number in other parts of my code. I would like some input if the soluon I have found is the most efficient way to accomplish this.

This is my new solution. Is this the most efficient way to accomplish what I am tryng to do?

    Dim SearchVal As Variant
    Dim CheckRange As Range
    Dim RowNum As Long
    
    Set CheckRange = Columns("A:A")
    SearchVal = "test1"
    
    On Error Resume Next
    RowNum = Application.WorksheetFunction.Match(SearchVal, CheckRange, 0)
    On Error GoTo 0
    
    MsgBox RowNum

Please let me know if you have a better way. Would it matter if I used a shorter range, like "A1:A1000"?

 

[JoeMo]

The match function is very fast so you probably won't see a big speed change if you narrow the search range. You can avoid a runtime error if there is no match like this:

Dim RowNum as Variant
RowNum = Application.Match(SearchVal, CheckRange, 0)
If not IsError(RowNum) Then 
'do something

If a match is not found an error (but not a runtime error) will be generated so RowNum will be some error number rather than a long, which is why you want to Dim RowNum as variant.

 

[Rick Rothstein]

I don't know how the speed compares to Application.Match, but I think the Find function is quite fast...

[table="width: 500"]
[tr]
	[td]RowNum = CheckRange.Find(SearchVal, Cells(Rows.Count, "A"), xlValues, xlWhole, , xlNext, False, , False).Row[/td]
[/tr]
[/table]

 

[JoeMo]

I don't know how the speed compares to Application.Match, but I think the Find function is quite fast...

[TABLE="width: 500"]
<tbody>[TR]
[TD]RowNum = CheckRange.Find(SearchVal, Cells(Rows.Count, "A"), xlValues, xlWhole, , xlNext, False, , False).Row[/TD]
[/TR]
</tbody>[/TABLE]

I don't know how the speed compares either. The most apparent difference is that the Find Method will generate a runtime error if there is no match.

 

[Rick Rothstein]

The most apparent difference is that the Find Method will generate a runtime error if there is no match.

Which the OP's On Error Resume Next statement will catch. With RowNum declared as Long, the code line I suggested will return 0 if there is no match (which can easily be tested for in later code if necessary).

 

[Leith Ross]

You can avoid the error very easily when using find. The Find method of a Range object returns either a Range or the special object variable Nothing. The result can be tested in an IF statement. The additional code's affect on execution speed would be infinitesimal.

Dim Cell    As Range
Dim RowNum  As Long


    Set Cell = CheckRange.Find(SearchVal, Cells(Rows.Count, "A"), xlValues, xlWhole, , xlNext, False, , False)


    If Cell Is Nothing Then
        MsgBox SearVal & " was Not Found.", vbExclamation
        Exit Sub
    End IF


    RowNum = Cell.Row

 

[Marcelo Branco]

I don't know how the speed compares either. The most apparent difference is that the Find Method will generate a runtime error if there is no match.

About Find x Application.Match
https://fastexcel.wordpress.com/2011/10/26/match-vs-find-vs-variant-array-vba-performance-shootout/

where i see
Conclusions
Don’t use Range.Find unless you want to search a large number of columns for the same thing (you would have to do a Match for each column).
The Variant array approach is surprisingly effective, particularly when you expect a large number of hits.
Match wins easily for a small number of hits.

M.

 

[Rick Rothstein]

About Find x Application.Match
https://fastexcel.wordpress.com/2011/10/26/match-vs-find-vs-variant-array-vba-performance-shootout/

where i see
Conclusions
Don’t use Range.Find unless you want to search a large number of columns for the same thing (you would have to do a Match for each column).
The Variant array approach is surprisingly effective, particularly when you expect a large number of hits.
Match wins easily for a small number of hits.

M.

Thanks for posting... good to know.

 

[JoeMo]

About Find x Application.Match
https://fastexcel.wordpress.com/2011/10/26/match-vs-find-vs-variant-array-vba-performance-shootout/

where i see
Conclusions
Don’t use Range.Find unless you want to search a large number of columns for the same thing (you would have to do a Match for each column).
The Variant array approach is surprisingly effective, particularly when you expect a large number of hits.
Match wins easily for a small number of hits.

M.

Thanks for the reference Marcelo!

 

[offthelip]

@Marcelo. thanks for that. I am pleased to see that variant arrays (which I am always extolling the virtues of) only lose out when the time is less than 20 milliseconds