CODEWITHSUNDEEP
vbaexcel
Sherry Kong
Sherry Kong

Reputation: 129

Type mismatch error instr

I want to find the IC number in the policyowner(NRIC). But there is error in the code.

    Dim col As Long
    Dim IC, NRIC
    Dim name

    name = ActiveSheet.Cells(rw, 1).Value

    NRIC = ThisWorkbook.Sheets("main").Columns(9)

    IC = InStr(1, name, "(" & NRIC & ")") <<<<< this is the problem (type mismatch error)

    MsgBox IC

The display:

main sheet

name column

IC column

policydetails Sheet

policyowner(NRIC) column

Upvotes: 0

Views: 1336

Answers (1)

L42
L42

Reputation: 19727

InStr Function 3rd argument expects a string (substring).
You actually passed an array which is the entire Column(9).
For it to work, you need to loop through all the values in Column(9) like this:

Dim c As Range
With ThisWorkbook.Sheets("main")
    For Each c In .Columns(9).CurrentRegion
        If InStr(1, name, c.Value2) <> 0 Then
            MsgBox c.Value2 ' I don't know what you want to do if you find the cell
            Exit Sub
        End If
    Next
End With

An alternative would be to use the Find Method of the Range Object.

Dim c As Range
With ThisWorkbook.Sheets("main")
    Set c = .Columns(9).CurrentRegion.Find(What:=name, LookAt:=xlPart)
    If Not c Is Nothing Then MsgBox c.Value2
End With

Upvotes: 1

Related Questions