The fastest/most effective method to check if value is in the range [VBA]

[RatExcel]

Hello Guys,

What is the fastest / most effective way to check if given value is in the given range?

Let's say I have a sheet with 10 000 rows of data in it. Let's supose I want to do something to cell in column B if corresponding cell in column A is equal to given value.

The method that I use most often is the below:

Sub Test()

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(i, 1) = "ABC" Then
        Cells(i, 2) = "DEF"
    End If
Next i

End Sub

But if value "ABC" is not in column A the whole action is pointless and it takes time and resource if the worksheet has a lot of rows.

The other solution I can think of is the below:

Sub Test()

On Error Resume Next
If WorksheetFunction.Match("ABC", Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)), 0) Then
    If Err.Number > 0 Then
        On Error GoTo 0
        Err.Clear
        GoTo NextStep
    End If
    On Error GoTo 0
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If Cells(i, 1) = "ABC" Then
            Cells(i, 2) = "DEF"
        End If
    Next i
End If

NextStep:

End Sub

But the code gets longer and less readible and I personally think this is not a good solution.

So is there any simpler, faster, more elegant way to determine if the given value is in the range?

I'm certain that there is and it is very obvious but I can't think of it. Would you help?

 

[NeonRedSharpie]

Sub test()

Dim rng As Range
Set rng = Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))


On Error GoTo ErrorHandler


Debug.Print rng.Find("ABC", lookat:=xlWhole)


For Each iCell In rng
    If iCell.Value = "ABC" Then iCell.Offset(0, 1).Value = "DEF"
Next iCell


Exit Sub




ErrorHandler:
    Select Case Err.Number
        Case 9, 91
        MsgBox "Not Found"
    End Select


End Sub

This is usually how I would go about it. Same process, but why not use an error handling function instead of throwing it in the middle of the code?

 

[Rick Rothstein]

This should be pretty fast, especially with 10,000 rows of data...

Sub SetColumnBfromColumnA()
  Application.ScreenUpdating = False
  ActiveSheet.AutoFilterMode = False
  Rows(1).Insert
  Columns("A").AutoFilter Field:=1, Criteria1:="ABC"
  On Error GoTo NothingVisible
  Intersect(ActiveSheet.UsedRange, Columns("B").SpecialCells(xlCellTypeVisible)).Value = "DEF"
  ActiveSheet.AutoFilterMode = False
NothingVisible:
  Rows(1).Delete
  Application.ScreenUpdating = True
End Sub

 

[RatExcel]

Thanks for your tips, NeonRedSharpie and Rick.

@Rick, for me the most important is just to be able to easily check if given value is in given range.

I came up with below function:

Function IsInArray(vWhat As Variant, vWhere As Variant) As Boolean
    IsInArray = Not IsError(Application.Match(vWhat, vWhere, 0))
End Function

It works if I use Columns(1) or Range("A:A") as vWhere but it returns False for Range("A:B"). Do you know why?

 

[Rick Rothstein]

Thanks for your tips, NeonRedSharpie and Rick.

@Rick, for me the most important is just to be able to easily check if given value is in given range.

Based on the code you posted, it appears that your ultimate goal is to replace Column B values when the corresponding cell in Column A equals (for you example) "ABC". The code I posted should be fast enough not to need that test at all... if the text exists in Column A, the Column B replacements take place... if not, nothing happens. Why waste the time having Excel check separately?

 

[RatExcel]

That's not my ultimate goal, that was just an example. I have to retrieve values from other columns as often as I have to replace them. Your method is great for replacing, thanks for that, but I also would like to have an ability to just check if value is in given range. I came up with something like this:

Function IsInArray(vWhat As Variant, vWhere As Variant) As Boolean
    If Not vWhere.Find(vWhat) Is Nothing Then IsInArray = True Else IsInArray = False
End Function

I hope this will work. The only required argument for Find Method is What. I'm not sure what are the default values for other optional arguments. I cannot find it on Excel help. Correct me if I'm wrong:

After: start looking from the begining of range
LookIn: xlValues
LookAt: xlWhole
SearchOrder: xlByRows
SearchDirection: xlNext
MatchCase: False
MatchByte: False
SearchFormat: False

Am I right?