What I'm trying to do is select a range of cells which some have
comments and others do not. Within those comments are key words I am
looking for (i.e. "Transfer Out" or "Transfers Out" or "V. Term.").
And in front of those words is a number. Here's an example of two
comments:

______________________
1 New Hi
I. Baker

1 V. Term:
R. Robinson-Lynch
______________________

______________________
2 Transfers Out:
O. Erinjogunola
K. Sartori
______________________


Let's say the Range for cells to contain comments is C4:C44
I want to use the numbers before the key words and add them together to
know how many leaves there were in that range of cells.

Basically what I want C50 to say is this:
3

and I also need this to be Relative Reference.
Can this be done??


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

Dim tot as long, num as Long
Dim cmt as Comment
Dim sStr as String, iloc as Long
Dim iloc1 as Long, iloc2 as Long
Dim iloc3 as Long

Tot = 0
for each cmt in Activesheet.Comments
if not intersect(cmt.parent,Range("C4:C44")) is nothing then
sStr = cmt.Text
iloc1 = inst(1,sStr,"Transfer Out",vbTextcompare)
iloc2 = instr(1,sStr,"Transfers Out",vbTextcompare)
iloc3 = Instr(1,sStr,"V. Term")
iloc = iloc1 + iloc2 + iloc3
if iloc 0 then
num = clng(left(sStr,iloc-1))
tot = num + tot
end if
Next
Range("C50") = tot

--
Regards,
Tom Ogilvy


"malik641" wrote in
message ...

What I'm trying to do is select a range of cells which some have
comments and others do not. Within those comments are key words I am
looking for (i.e. "Transfer Out" or "Transfers Out" or "V. Term.").
And in front of those words is a number. Here's an example of two
comments:

______________________
1 New Hi
I. Baker

1 V. Term:
R. Robinson-Lynch
______________________

______________________
2 Transfers Out:
O. Erinjogunola
K. Sartori
______________________


Let's say the Range for cells to contain comments is C4:C44
I want to use the numbers before the key words and add them together to
know how many leaves there were in that range of cells.

Basically what I want C50 to say is this:
3

and I also need this to be Relative Reference.
Can this be done??


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

Thanks Tom for the reply.
I tried what you gave me and I had to add a "End If" statement but that
was all. But when I try to run the Macro it gives me a 'Runtime Error 13
Type Mismatch' and I have NO CLUE what that means. I'm a true novice at
VB code (it's been 5 years since I've had to use it....so I kinda
forgot everything about it)

Just incase I did something wrong here's what I have in the VB Editor:

Sub AnalysisOfLeaves()
'Calculates amount of leavers from text in comments

Dim tot As Long, num As Long
Dim cmt As Comment
Dim sStr As String, iloc As Long
Dim iloc1 As Long, iloc2 As Long
Dim iloc3 As Long

tot = 0

For Each cmt In ActiveSheet.Comments
If Not Intersect(cmt.Parent, Range("C4:C44")) Is Nothing Then
sStr = cmt.Text
iloc1 = InStr(1, sStr, "Transfer Out", vbTextCompare)
iloc2 = InStr(1, sStr, "Transfers Out", vbTextCompare)
iloc3 = InStr(1, sStr, "V. Term", vbTextCompare)
iloc = iloc1 + iloc2 + iloc3
If iloc 0 Then
num = CLng(Left(sStr, iloc - 1))
tot = num + tot
End If
End If
Next

Range("C50") = tot

End Sub


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

Do you get the error at:

num = CLng(Left(sStr, iloc - 1))

type mismatch would mean that Left(sStr,iloc-1) is not producing a result
that would evaluate to a number.

for example:

clng("A")

produces an Error 13, Type Mismatch

My suggested code was based on the sample data you provided (the number was
the first item in the comment and was to the left of the search term) and an
assumption that more than one of the test fields would not appear in the
same comment. If that isn't the case, that line of code may need more
work.

--
Regards,
Tom Ogilvy



"malik641" wrote in
message ...

Thanks Tom for the reply.
I tried what you gave me and I had to add a "End If" statement but that
was all. But when I try to run the Macro it gives me a 'Runtime Error 13
Type Mismatch' and I have NO CLUE what that means. I'm a true novice at
VB code (it's been 5 years since I've had to use it....so I kinda
forgot everything about it)

Just incase I did something wrong here's what I have in the VB Editor:

Sub AnalysisOfLeaves()
'Calculates amount of leavers from text in comments

Dim tot As Long, num As Long
Dim cmt As Comment
Dim sStr As String, iloc As Long
Dim iloc1 As Long, iloc2 As Long
Dim iloc3 As Long

tot = 0

For Each cmt In ActiveSheet.Comments
If Not Intersect(cmt.Parent, Range("C4:C44")) Is Nothing Then
sStr = cmt.Text
iloc1 = InStr(1, sStr, "Transfer Out", vbTextCompare)
iloc2 = InStr(1, sStr, "Transfers Out", vbTextCompare)
iloc3 = InStr(1, sStr, "V. Term", vbTextCompare)
iloc = iloc1 + iloc2 + iloc3
If iloc 0 Then
num = CLng(Left(sStr, iloc - 1))
tot = num + tot
End If
End If
Next

Range("C50") = tot

End Sub


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

I don't know if that is where I get the error because when I run th
macro and it doesn't highlight the error or place the text cursor a
the error. So I'm not sure.

Do I have to select a certain cell before I run the macro? Or do
highlight the section I want analyzed? (I Know I'm a novice at macro
but I'm pretty sure what I have to do to run the macro correctly is t
select cell C50 and then run the macro.)

The cells that I'm analyzing all have comments that are structured th
same as the examples that I listed.

Tom Ogilvy Wrote:
Do you get the error at:

num = CLng(Left(sStr, iloc - 1))

type mismatch would mean that Left(sStr,iloc-1) is not producing
result
that would evaluate to a number.

for example:

clng("A")

produces an Error 13, Type Mismatch

My suggested code was based on the sample data you provided (the numbe
was
the first item in the comment and was to the left of the search term
and an
assumption that more than one of the test fields would not appear i
the
same comment. If that isn't the case, that line of code may nee
more
work.

--
Regards,
Tom Ogilvy



"malik641"
wrote in
message ...

Thanks Tom for the reply.
I tried what you gave me and I had to add a "End If" statement bu
that
was all. But when I try to run the Macro it gives me a 'Runtime Erro
13
Type Mismatch' and I have NO CLUE what that means. I'm a true novic
at
VB code (it's been 5 years since I've had to use it....so I kinda
forgot everything about it)

Just incase I did something wrong here's what I have in the V
Editor:

Sub AnalysisOfLeaves()
'Calculates amount of leavers from text in comments

Dim tot As Long, num As Long
Dim cmt As Comment
Dim sStr As String, iloc As Long
Dim iloc1 As Long, iloc2 As Long
Dim iloc3 As Long

tot = 0

For Each cmt In ActiveSheet.Comments
If Not Intersect(cmt.Parent, Range("C4:C44")) Is Nothing Then
sStr = cmt.Text
iloc1 = InStr(1, sStr, "Transfer Out", vbTextCompare)
iloc2 = InStr(1, sStr, "Transfers Out", vbTextCompare)
iloc3 = InStr(1, sStr, "V. Term", vbTextCompare)
iloc = iloc1 + iloc2 + iloc3
If iloc 0 Then
num = CLng(Left(sStr, iloc - 1))
tot = num + tot
End If
End If
Next

Range("C50") = tot

End Sub


--
malik641

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


--
malik64
-----------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...fo&userid=2419
View this thread: http://www.excelforum.com/showthread.php?threadid=38619

It doesn't matter what you have selected.

The total is put in cell C50 by this line:
Range("C50") = tot

The code only looks in comments in this range: C4:C44

You can change either if you want.

===
But I think Tom wrote the code believing that your numbers were always the first
characters in the comment.

(Do you ever go to 2 digits in your transfers/terminations?)


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

Dim tot As Long, num As Long
Dim cmt As Comment
Dim sStr As String, iloc As Long
Dim iloc1 As Long, iloc2 As Long
Dim iloc3 As Long

tot = 0

For Each cmt In ActiveSheet.Comments
If Not Intersect(cmt.Parent, Range("C4:C44")) Is Nothing Then
sStr = cmt.Text
iloc1 = InStr(1, sStr, "Transfer Out", vbTextCompare)
iloc2 = InStr(1, sStr, "Transfers Out", vbTextCompare)
iloc3 = InStr(1, sStr, "V. Term", vbTextCompare)
iloc = iloc1 + iloc2 + iloc3
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
End If
End If
Next cmt

Range("C50") = tot

End Sub



malik641 wrote:

I don't know if that is where I get the error because when I run the
macro and it doesn't highlight the error or place the text cursor at
the error. So I'm not sure.

Do I have to select a certain cell before I run the macro? Or do I
highlight the section I want analyzed? (I Know I'm a novice at macros
but I'm pretty sure what I have to do to run the macro correctly is to
select cell C50 and then run the macro.)

The cells that I'm analyzing all have comments that are structured the
same as the examples that I listed.

Tom Ogilvy Wrote:
Do you get the error at:

num = CLng(Left(sStr, iloc - 1))

type mismatch would mean that Left(sStr,iloc-1) is not producing a
result
that would evaluate to a number.

for example:

clng("A")

produces an Error 13, Type Mismatch

My suggested code was based on the sample data you provided (the number
was
the first item in the comment and was to the left of the search term)
and an
assumption that more than one of the test fields would not appear in
the
same comment. If that isn't the case, that line of code may need
more
work.

--
Regards,
Tom Ogilvy



"malik641"
wrote in
message ...

Thanks Tom for the reply.
I tried what you gave me and I had to add a "End If" statement but
that
was all. But when I try to run the Macro it gives me a 'Runtime Error
13
Type Mismatch' and I have NO CLUE what that means. I'm a true novice
at
VB code (it's been 5 years since I've had to use it....so I kinda
forgot everything about it)

Just incase I did something wrong here's what I have in the VB
Editor:

Sub AnalysisOfLeaves()
'Calculates amount of leavers from text in comments

Dim tot As Long, num As Long
Dim cmt As Comment
Dim sStr As String, iloc As Long
Dim iloc1 As Long, iloc2 As Long
Dim iloc3 As Long

tot = 0

For Each cmt In ActiveSheet.Comments
If Not Intersect(cmt.Parent, Range("C4:C44")) Is Nothing Then
sStr = cmt.Text
iloc1 = InStr(1, sStr, "Transfer Out", vbTextCompare)
iloc2 = InStr(1, sStr, "Transfers Out", vbTextCompare)
iloc3 = InStr(1, sStr, "V. Term", vbTextCompare)
iloc = iloc1 + iloc2 + iloc3
If iloc 0 Then
num = CLng(Left(sStr, iloc - 1))
tot = num + tot
End If
End If
Next

Range("C50") = tot

End Sub


--
malik641

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


--
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