CODEWITHSUNDEEP
excelerror-handlingvba
JimQ
JimQ

Reputation: 43

Run Time Error 13 Type Mis-Match VBA Excel 2010

I got this code off here earlier today and just tried to adapt it. I'm receiving Run Time Error 13 on the line Set Rng = and I'm not quite sure why(?) Any help appreciated.

Sub PlayMacro()

  Dim Prompt As String
  Dim RetValue As String
  Dim Rng As Range
  Dim RowCrnt As Long

  Prompt = ""

      With Sheets("Claims")

    Do While True

  RetValue = InputBox(Prompt & "Give me a value to look for")
  'RetValue will be empty if you click cancel

  If RetValue = "" Then
    Exit Do
  End If

  ' I do not wish to active the cell containing the required value.
  ' I want to know where it is.
  Set Rng = .Range("Table1").Find(What:=RetValue, After:=.Range("Table1"), _
            LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

  If Rng Is Nothing Then
    ' The entered value could not be found
    Prompt = "I could not find """ & RetValue & """"
  Else
    ' The entered value was found
    RowCrnt = Rng.Row
    Prompt = "I found """ & RetValue & """ on row " & RowCrnt
  End If
  Prompt = Prompt & vbLf
Loop

End With

End Sub

Upvotes: 0

Views: 190

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

try to delete After:=.Range("Table1"), in the line Set Rng = .Range("Table1").Find(... :

Set Rng = .Range("Table1").Find(What:=RetValue,  _ 
        LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

Upvotes: 2

Related Questions