Searching for a number

Question

We have 3 systems in our group - one system is having trouble recognizing the Excel VBA Code that searches for a number - any ideas? Thanks

Code is below: the part we are having trouble with is the Selection.Find part: Strangest thing I have ever seen - thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim curid As String
Application.Goto Reference:="R2C2"
'curid is the id number to search for
curid = ActiveCell.Value2

  'Target Address is F1 - contains a drop down list of selections
If Target.Address = "$F$1" Then
    On Error GoTo bm_Safe_Exit
    Application.EnableEvents = False
    Select Case Target.Value2
        Case "Approved"
        'statusid is a column containing all record id numbers ranging from 1 to 115 right now
                Application.Goto Reference:="statusid"
        'this is the problem - it will not execute on one system we have
                Selection.Find(What:=curid, After:=ActiveCell, LookIn:=xlValues, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False).Activate

                ActiveCell.Offset(0, 1).Range("A1").Select
                ActiveCell.FormulaR1C1 = "Approved"
                Sheets("Update Form").Select

Highly suggest you read [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Apr 07 '20 at 17:49
"having trouble" is not a useful description of the actual problem you're having. What *exactly* are you seeing? — Tim Williams, Apr 07 '20 at 17:52
As @BigBen mentioned, you should avoid using `.Select` and `.Selection`. It makes performance and debugging worse. Additionally, the `.Find` method of the Range object will return `Nothing` if no match is found. If that happens, the `.Activate` part will throw an error. It would also be helpful to know what "Will not execute" means. Is there an error here? If so, what is the error? — ArcherBird, Apr 07 '20 at 17:53
Selection.Find(What:=curid, After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate ' this line of code is not executing that is the issue I am having thanks — GRicks, Apr 07 '20 at 17:56
@ArcherBird - I have several case statements here - this is just one of them. That part is working fine - AND - it should ALWAYS find the correct ID number - should NEVER error out — GRicks, Apr 07 '20 at 17:59
@TimWilliams for some reason - it is not finding the curid value in the Selection.Find part of the code. — GRicks, Apr 07 '20 at 18:03
@GRicks is it possible the range is Hidden on the one system where it doesn't work? The `.Find` method won't work on hidden cells when looking in `xlValues` — ArcherBird, Apr 07 '20 at 18:04
And you should ALWAYS check the return result for `nothing` when you perform a `Find`, for just this situation. Echoing and building on the earlier comments from @BigBen, take a look at [this answer](https://stackoverflow.com/a/28422075/4717755) on how to use `Find` without using `Selection`. — PeterT, Apr 07 '20 at 18:04

score 0 · Answer 1 · answered Apr 07 '20 at 18:14

Should look more like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim curid As String, f As Range
    curid = Me.Range("B2").Value 'curid is the id number to search for

    'Target Address is F1 - contains a drop down list of selections
    If Target.Address = "$F$1" Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        Select Case Target.Value
            Case "Approved"
            'statusid is a column containing all record id numbers ranging from 1 to 115 right now
                Set f = Range("statusid").Find(What:=curid, LookIn:=xlValues, _
                               LookAt:=xlWhole, MatchCase:=False)
                If Not f Is Nothing Then
                    f.Offset(0, 1).Value = "Approved"
                Else
                    'was not found
                End If

Just for logic puropose, I'd move the first two lines right after the `If Target.Address = "$F$1" Then` statement. — HTH, Apr 07 '20 at 18:17

Searching for a number

1 Answers1