Excel Input Box VBA Error Checking Problem

Question

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

You should format your code as code so it's easier for us to read. See here how. — Jean-François Corbett
– Jean-François Corbett, Commented Apr 7, 2011 at 9:37

Neil · Accepted Answer · 2011-04-07 19:48:02Z

3

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.

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.)
You then try to compare the variable to False instead of checking that it isn't Nothing.
You then need to activate the found Range rather than trying to find it again.

edited Apr 7, 2011 at 19:48

answered Apr 6, 2011 at 19:33

Neil

55.5k8 gold badges65 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brett Over a year ago

Hey I edited the post to the code I have written but I am still getting errors.

Dick Kusleika · Accepted Answer · 2011-04-09 16:21:16Z

1

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

answered Apr 9, 2011 at 16:21

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Collectives™ on Stack Overflow

Excel Input Box VBA Error Checking Problem

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related