Need help getting application.inpiutbox to run correctly

[BrianExcel]

Hello, I am running the following macro, which is almost running correctly now...

1. User enters a string in the first application.inputbox (working correctly)
   1. OR the user can click cancel and the sub will exit (working correctly)
2. User selects range via second application.inputbox (NOT workinng)

In an ideal world, the remaining code would then run (which would search the range for that string entered in step 1, and do the remaining steps.

I had to add the capability for the user to cancel the sub at any time, thus the "If application.inputbox = "" then" lines...

But I keep getting a "Run-time error 13: Type Mismatch" when I try and run the code. Here is the code itself...

Sub SISearch(control As IRibbonControl)Dim r As Range


sTerm = Application.InputBox(prompt:="Enter text to search for", Title:="SEARCH TEXT", Type:=2)
    If sTerm = "" Then
        Exit Sub
    Else
Set rAll = Application.InputBox(prompt:="Select range to search", Title:="RANGE SELECTION", Type:=8)
    If rAll = "" Then [COLOR=#ff0000]<---[/COLOR] [COLOR=#ff0000]THIS IS WHERE MY CODE KEEPS ERRORING OUT[/COLOR]
        Exit Sub
    Else
        For Each r In rAll
            If InStr(UCase(r), UCase(sTerm)) Then
                r.Offset(0, 1) = "Text located"
            End If
        Next r
    End If
    End If
End Sub

Before I added the cancel options, the code was working fine, UNLESS the user selected cancel, in which case it was going to debug mode, which is why I needed to add the ability for my users to cancel.

Can anyone point me in the right direction on why this isn't working? If I can just get this last step done, I should be good to go...

 

[BrianExcel]

Also FYI, if I modify the code to the below (which is the exact same only removing the "Cancel" button option, it runs fine...

Sub SISearch(control As IRibbonControl)Dim r As Range


sTerm = Application.InputBox(prompt:="Enter text to search for", Title:="SEARCH TEXT", Type:=2)
    If sTerm = "" Then
        Exit Sub
    Else
Set rAll = Application.InputBox(prompt:="Select range to search", Title:="RANGE SELECTION", Type:=8)
        
        For Each r In rAll
            If InStr(UCase(r), UCase(sTerm)) Then
                r.Offset(0, 1) = "Text located"
            End If
        Next r
    End If


End Sub

 

[shg]

Sub SISearch()
    Dim s           As String
    Dim r           As Range
    Dim cell        As Range

    s = Application.InputBox(Prompt:="Enter text to search for", Title:="SEARCH TEXT", Type:=2)
    If Len(s) Then
        On Error GoTo NeverMind
        Set r = Application.InputBox(Prompt:="Select range to search", Title:="RANGE SELECTION", Type:=8)
        For Each cell In Intersect(r, r.Worksheet.UsedRange)
            If InStr(1, cell.Value2, s, vbTextCompare) Then
                cell.Offset(0, 1) = "Text located"
            End If
        Next cell
    End If
NeverMind:
End Sub

 

[BrianExcel]

Thanks shg -

I am running Excel 2010. When I run your code, I get a "Wrong Number of Arguments or invalid property assignment" error.

Any thoughts on modification?

 

[VoG]

That works for me in Excel 2010. Which line of code causes the error?

 

[BrianExcel]

It actually doesn't give me a line of code. I have the VBA editing window open, and when I run the code, it throws up that msgbox indicating the error and then automatically returns me to Excel - not the VBA window but the actual application.

Weird.

 

[BrianExcel]

GOT IT!!!

It was the fact that I was running this from the Ribbon, and the lack of (control as iRibbonControl) in the Sub name was throwing the error. Adding that made it work perfectly. THANKS!!!

 

[shg]

You're welcome.