Solved: InStr Error when using .Comment.Text

[SamT]

:banghead: :banghead: :banghead:

I hope you guys can help. I've been fighting this for over 8 hours now. I just spent the last few searching the archives for every combination of "InStr," "error," "Comment," and "Mismatch" I can think of.:dunno

I am trying to search in a cells .Comment.Text for a string.
I have tried Instr(Range.Comment.Text, "word",1) and InStr(Var, Var, 1) and changing the Var Types to String and Variable with no luck. I've also tried fixed length String Vars and non-fixed length.

No matter what I try I get a Type Mismatch Error in the Instr function. I am pretty sure it's something to do with the fact that the first string comes from a Range Comment, but????

Why wouldn't Dimming a Var As String, then setting it = Comment.Text force it to a String Type compatible with InStr?

[SamT]

Opps, Forgot :nervous:

I'm using excel 97 on Win98SE

SamT

[Norie]

I think you might have the arguments the wrong way round.

I would also suggest you avoid using something like Range as a name for a variable, which it seems you are doing.

[SamT]

Norie,

The Help file says the order is correct, but I tried reversing them, like you suggested. Still getting the Type Mismatch error.
:banghead:
:banghead:
:banghead:

[mdmackillop]

As Norie noted, the arguments were the wrong way round.
Instr is not finding object due to capital letter difference. Option Compare Text avoids that problem.
I've added a loop to check A1:A3
Option Explicit
Option Compare Text

Sub ResetInteriors()


Dim CS As String 'Stands for Comment String
Dim OW As String 'Stands For Object Word
Dim CVW As String 'Stands for Cell Value Word, the word in the Cell.
Dim i As Long

CS = ""
OW = "object"
CVW = ""

'If using Range "A1", CVW = "Test 1"; If "A2", "Test 2"; "A3", "Test 3"
For i = 1 To 3
With ActiveSheet.Range("A" & i)
CVW = .Value
CS = .Comment.Text

'Instr as used below results in Type Mismatch error

'The Position of OW in CS is always > 1.
If InStr(1, CS, OW) > 1 Then 'OW Found.
.Interior.ColorIndex = 7
End If

If InStr(1, CS, CVW) = 0 Then 'CVW not found.
.Interior.Pattern = xlPatternGray8
End If

End With
Next
End Sub

[SamT]

Mack, you the man.

Quoting the help file for Instr:

InStr([start, ]string1, string2[, compare])
start: Optional. Numeric expression that sets the starting position for each search. If omitted, search begins at the first character position. ... The start argument is required if compare is specified.
string1: Required. String expression being searched.
string2: Required. String expression sought.
compare: Optional. Specifies the type of string comparison. The compare argument can be omitted, or it can be 0, 1or 2. Specify 0 (default) to perform a binary comparison. Specify 1 to perform a textual, noncase-sensitive comparison.

I completely missed the red text.:banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: RTFM RTFM RTFM, then RTFM backwards.

Since I trust that what you wrote would work I put the Start arg back in and . . .drumroll, please... It worked like a champ. I left the Compare arg in, rather than use Option Compare Text for the whole module.InStr(1, CS, OW, 1)

In Norie's post, I thought he meant that the two string args were reversed. Sorry Norie.
Thanks, both of you.

SamT

Now where is that "Mark Solved" button. . .