CODEWITHSUNDEEP
excelvba
Brett
Brett

Reputation: 1

Excel Input Box VBA Error Checking Problem

Below is the code that I changed. I cannot figure out VBA for the life of me. If this was c++ it would have taken me 30 seconds to write. I am still getting the errors.

Sub CodeFinder()

    Dim userInput As String
    Dim errorCheck As String

    userInput = InputBox("Please enter the code to search", "Code Search Engine")

    errorCheck = Cells.Find(What:=userInput, _
                       After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
                       SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                       MatchCase:=False)

    If errorCheck = False Then
        MsgBox ("Error")
    Else
        Cells.Find(What:=userInput, _
                   After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
                   SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                   False).Activate
    End If 

End Sub

Upvotes: 0

Views: 2014

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33145

Sub CodeFinder()

    Dim userInput As String
    Dim rFound As Range

    userInput = InputBox("Please enter the code to search", "Code Search Engine")

    If Len(userInput) > 0 Then
        Set rFound = ActiveSheet.Cells.Find(What:=userInput, _
                           After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
                           SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                           MatchCase:=False)

        If Not rFound Is Nothing Then
            rFound.Select
        Else
            MsgBox "No cells found"
        End If
    End If

End Sub

Upvotes: 1

Neil
Neil

Reputation: 55392

If Cells.Find fails it returns Nothing. So you need to assign it to a variable, and check its value before trying to .Activate it.

In fact you should also check the return value of InputBox in case Cancel was clicked.

EDIT: Still contains a number of errors.

  1. Cells.Find returns a Range, but you are trying to assign it to a String variable. (Also don't forget that Range and String variables have different assignment statements.)
  2. You then try to compare the variable to False instead of checking that it isn't Nothing.
  3. You then need to activate the found Range rather than trying to find it again.

Upvotes: 3

Related Questions