Try this:

Option Explicit
Sub AnalysisOfLeaves2()
'Calculates amount of leavers from text in comments

Dim tot As Long
Dim num As Long
Dim cmt As Comment
Dim sStr As String
Dim iloc As Long
Dim myKeyWords As Variant
Dim iCtr As Long

myKeyWords = Array("transfer out", "transfers out", "v. term")

tot = 0
For Each cmt In ActiveSheet.Comments
If Intersect(cmt.Parent, Range("C4:C44")) Is Nothing Then
'do nothing
Else
For iCtr = LBound(myKeyWords) To UBound(myKeyWords)
sStr = cmt.Text
Do
iloc = InStr(1, sStr, myKeyWords(iCtr), vbTextCompare)
If iloc 0 Then
If IsNumeric(Mid(sStr, iloc - 2, 2)) Then
num = CLng(Mid(sStr, iloc - 2, 2))
tot = num + tot
Else
MsgBox "error on: " & cmt.Parent.Address(0, 0)
End If
Else
Exit Do
End If
sStr = Mid(sStr, iloc + Len(myKeyWords(iCtr)))
Loop
Next iCtr
End If
Next cmt
Range("C50") = tot

End Sub



malik641 wrote:

Okay, I figured out why it said Type Mismatch.

If a comment had "1 V. Term" and "1 Transfer Out" or ANY combination of
TWO or more different text strings, it would give me that error. But if
I had 2 of the same text strings in the same comment it wouldn't read
the second string to add it to the count.

Example:

________
1 V. Term:
Joe S

1 V. Term:
John S
________

This would bring back 1 as the value
when it should bring 2

________
1 V. Term:
Joe S

1 Transfer out:
John S
________

This would give me the error
when it should read 2

how do I correct the macro to make it give back the value I want?

--
malik641
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=386198

--

Dave Peterson