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

Hey thanks a lot Dave, that message box really helped. I had an error o
a specific cell, but the cell had a normal comment in it. Here's what i
had:

1 V. Term:
J. Hoek

1 Transfer Out:
B. Colfer

When I deleted this comment the macro ran just fine. I have NO clu
what was wrong with this comment....

Any ideas on why what happened?

Otherwise thanks again Dave, I really appreciate it.

....That's also a pretty useful way of debugging..

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

Tom's code assumes that you only have one type of transfer per comment.

If you have more than one, it can get mixed up.

malik641 wrote:

Hey thanks a lot Dave, that message box really helped. I had an error on
a specific cell, but the cell had a normal comment in it. Here's what it
had:

1 V. Term:
J. Hoek

1 Transfer Out:
B. Colfer

When I deleted this comment the macro ran just fine. I have NO clue
what was wrong with this comment....

Any ideas on why what happened?

Otherwise thanks again Dave, I really appreciate it.

...That's also a pretty useful way of debugging...

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

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

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

Dave,
THANKS! That works great!!! Perfect!!

I just have ONE MORE question...

Instead of having the range be specific (i.e. C4:C44). How do I make it
so that you select a certain amount of cells, and then place the answer
a certain amount of cells lower than my last selected cell in the
column (selected cells will only be one column wide but many rows
down). Here's an example:

Cells C4:C44 are selected

Answer will be 32 spaces below C44 (C76).


This will be the LAST thing I promise (well, I hope anyway)


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

Try this:

Option Explicit
Sub AnalysisOfLeaves2A()
'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
Dim DestCell As Range

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

tot = 0
For Each cmt In ActiveSheet.Comments
If Intersect(cmt.Parent, Selection.Columns(1)) 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

With Selection.Columns(1)
Set DestCell = .Cells(.Cells.Count).Offset(32)
End With

DestCell.Value = tot

End Sub



malik641 wrote:

Dave,
THANKS! That works great!!! Perfect!!

I just have ONE MORE question...

Instead of having the range be specific (i.e. C4:C44). How do I make it
so that you select a certain amount of cells, and then place the answer
a certain amount of cells lower than my last selected cell in the
column (selected cells will only be one column wide but many rows
down). Here's an example:

Cells C4:C44 are selected

Answer will be 32 spaces below C44 (C76).

This will be the LAST thing I promise (well, I hope anyway)

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

Nevermind, I got it.
Thanks again Dave for your help.
This is what I end up with.

Sub AnalysisOfLeaves3()
'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
Dim rng As Range

Set rng = Selection

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

tot = 0
For Each cmt In ActiveSheet.Comments
If Intersect(cmt.Parent, rng) 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

ActiveCell.Offset(48, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = tot

End Su

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

Anybody up for it

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

I posted a reply and I thought your post said that you have something that
works.

malik641 wrote:

Anybody up for it?

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

Yes, but notice that we both posted our replies at the SAME time and
then I editted mine later on in the day to what I have now and it's a
little different and when I wrote that I posted the second comment
"Anybody up for it?" because the thread was on the third page and I
wanted it to be fresh so everyone can see it and help on the additional
code I want.
Dave Peterson Wrote:
I posted a reply and I thought your post said that you have something
that
works.

malik641 wrote:

Anybody up for it?

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


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

I see my response as 18 minutes before yours--but that's not important.

I guess I don't know what additional stuff you want.

malik641 wrote:

Yes, but notice that we both posted our replies at the SAME time and
then I editted mine later on in the day to what I have now and it's a
little different and when I wrote that I posted the second comment
"Anybody up for it?" because the thread was on the third page and I
wanted it to be fresh so everyone can see it and help on the additional
code I want.
Dave Peterson Wrote:
I posted a reply and I thought your post said that you have something
that
works.

malik641 wrote:

Anybody up for it?

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

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

That's weird, it says the same time on mine....hmm

Oh well. I'll just post it again. Here's what I wrote:

Remember when I said "This is last thing..."? well I have something
that I would like to ADD to this macro

This one is a little more challenging though

I had to adjust the macro a little bit for what I fully needed. Here's
what I ended up with.


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

Dim totTerm As Long-------------'Total number of terminations
Dim totNew As Long--------------'Total number of New Hires
Dim numN As Long----------------'Used for total number of New Hires
Dim cmt As Comment-------------'Variable for comments
Dim sStrT As String----------------'Used for text in comments for
Terminations
Dim sStrN As String----------------'Used for text in comments for New
Hires
Dim ilocT As Long-------------------'Number before keyword text in
comments for Terminations
Dim ilocN As Long-------------------'Number before keyword text in
comments for New Hires
Dim myKeyWordsT As Variant-----'Used to look up terminations
Dim myKeyWordsN As Variant-----'Used to look up new hires
Dim iCtrT As Long-------------------'Used to equal keywords for
Terminations
Dim iCtrN As Long-------------------'Used to equal keywords for New
Hires
Dim rng As Range-------------------'Selected cells

Set rng = Selection

'Sets key words for text lookup in comments
myKeyWordsT = Array("i. term", "v. term")
myKeyWordsN = Array("new hire")
totTerm = 0
totNew = 0
For Each cmt In ActiveSheet.Comments

'If statement for Terminations

If Intersect(cmt.Parent, rng) Is Nothing Then
'do nothing
Else
For iCtrT = LBound(myKeyWordsT) To UBound(myKeyWordsT)
sStrT = cmt.Text
Do
ilocT = InStr(1, sStrT, myKeyWordsT(iCtrT), vbTextCompare)
If ilocT 0 Then
If IsNumeric(Mid(sStrT, ilocT - 2, 2)) Then
numT = CLng(Mid(sStrT, ilocT - 2, 2))
totTerm = numT + totTerm
Else
MsgBox "Error on: " & cmt.Parent.Address(0, 0)
End If
Else
Exit Do
End If
sStrT = Mid(sStrT, ilocT + Len(myKeyWordsT(iCtrT)))
Loop
Next iCtrT
End If

Next cmt


For Each cmt In ActiveSheet.Comments

'If statement for New Hires

If Intersect(cmt.Parent, rng) Is Nothing Then
'do nothing
Else
For iCtrN = LBound(myKeyWordsN) To UBound(myKeyWordsN)
sStrN = cmt.Text
Do
ilocN = InStr(1, sStrN, myKeyWordsN(iCtrN), vbTextCompare)
If ilocN 0 Then
If IsNumeric(Mid(sStrN, ilocN - 2, 2)) Then
numN = CLng(Mid(sStrN, ilocN - 2, 2))
totNew = numN + totNew
Else
MsgBox "Error on: " & cmt.Parent.Address(0, 0)
End If
Else
Exit Do
End If
sStrN = Mid(sStrN, ilocN + Len(myKeyWordsN(iCtrN)))
Loop
Next iCtrN
End If


Next cmt

'Places New Hires in new hires cell
ActiveCell.Offset(43, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = totNew

'Places Terminations in termination cell
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = totTerm


End Sub

This works perfectly for what I originally needed to do. Now I need to
add a feature to this. And like I said this one is a BIGGER challenge
(If it isn't a big challenge, then sorry for the hype...I'm still a
novice :) ).

The reason I have this macro to begin with is to know how many hired
people there were and how many terminations there were (I forgot that
transfers didn't count, and I eliminated it as you can see in the
code). And with each cell that was selected holds a different
department of my company. Each having their own department number.

The numbers are placed adjacent to the selected cells (more
specifically B7:B34, B37:B42, and B46:B47...the reason for the gaps is
because of other "Total" values in the table) And as you know the
selected cells are the ones with comments that have the keyword values
and such.

Remember, this is an *ADDITION* to what I have...Everything else works
GREAT!

I need to know which department had ONLY the "V. Term" and "I. Term"
keywords. I also need to know to what amount (i.e. "5 V. Terms", like
in the code I have now) and THEN I need to place that specific number
in that one comment in a table BELOW the selected cells (still in the
same column, though) in coordinance with the same department number
based on cells B58:B78. Notice that there are less cells in the new
table than the one's adjacent to the selected cells. This is because
there are a few choice cells in B58:B78 (namely B71:B73, B75, and B78)
which instead of holding the numbers inside the cells as text, they
have comments with multiple department numbers (i.e. Comment:"152 + 134
+ 155").

Here is an example (this is the best I can come up with):
B7= 111------------------------------------C7=Comment:"1 V. Term"
B8= 112------------------------------------C8=Comment:"2 I. Terms"
B9= 113------------------------------------C9=Comment:"1 Transfer out"
B10=114-----------------------------------C10=Comment:"1 New Hire"
B11=115-----------------------------------C11=Comment:"1 Transfer In"
........................................I should End up with the
following (IN Column C, Column B has set
values).............................
B69=111-----------------------------------C69=1
B70=Comment:"112 + 113 + 114"-----C70=2
B71=115-----------------------------------C71=0


Hope this isn't TOO difficult


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

Personally, I think it's time to rethink your design--maybe way past time!

Put that data into separate columns in their own cells. It'll make life much
easier.



malik641 wrote:

That's weird, it says the same time on mine....hmm

Oh well. I'll just post it again. Here's what I wrote:

Remember when I said "This is last thing..."? well I have something
that I would like to ADD to this macro

This one is a little more challenging though

I had to adjust the macro a little bit for what I fully needed. Here's
what I ended up with.

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

Dim totTerm As Long-------------'Total number of terminations
Dim totNew As Long--------------'Total number of New Hires
Dim numN As Long----------------'Used for total number of New Hires
Dim cmt As Comment-------------'Variable for comments
Dim sStrT As String----------------'Used for text in comments for
Terminations
Dim sStrN As String----------------'Used for text in comments for New
Hires
Dim ilocT As Long-------------------'Number before keyword text in
comments for Terminations
Dim ilocN As Long-------------------'Number before keyword text in
comments for New Hires
Dim myKeyWordsT As Variant-----'Used to look up terminations
Dim myKeyWordsN As Variant-----'Used to look up new hires
Dim iCtrT As Long-------------------'Used to equal keywords for
Terminations
Dim iCtrN As Long-------------------'Used to equal keywords for New
Hires
Dim rng As Range-------------------'Selected cells

Set rng = Selection

'Sets key words for text lookup in comments
myKeyWordsT = Array("i. term", "v. term")
myKeyWordsN = Array("new hire")
totTerm = 0
totNew = 0
For Each cmt In ActiveSheet.Comments

'If statement for Terminations

If Intersect(cmt.Parent, rng) Is Nothing Then
'do nothing
Else
For iCtrT = LBound(myKeyWordsT) To UBound(myKeyWordsT)
sStrT = cmt.Text
Do
ilocT = InStr(1, sStrT, myKeyWordsT(iCtrT), vbTextCompare)
If ilocT 0 Then
If IsNumeric(Mid(sStrT, ilocT - 2, 2)) Then
numT = CLng(Mid(sStrT, ilocT - 2, 2))
totTerm = numT + totTerm
Else
MsgBox "Error on: " & cmt.Parent.Address(0, 0)
End If
Else
Exit Do
End If
sStrT = Mid(sStrT, ilocT + Len(myKeyWordsT(iCtrT)))
Loop
Next iCtrT
End If

Next cmt

For Each cmt In ActiveSheet.Comments

'If statement for New Hires

If Intersect(cmt.Parent, rng) Is Nothing Then
'do nothing
Else
For iCtrN = LBound(myKeyWordsN) To UBound(myKeyWordsN)
sStrN = cmt.Text
Do
ilocN = InStr(1, sStrN, myKeyWordsN(iCtrN), vbTextCompare)
If ilocN 0 Then
If IsNumeric(Mid(sStrN, ilocN - 2, 2)) Then
numN = CLng(Mid(sStrN, ilocN - 2, 2))
totNew = numN + totNew
Else
MsgBox "Error on: " & cmt.Parent.Address(0, 0)
End If
Else
Exit Do
End If
sStrN = Mid(sStrN, ilocN + Len(myKeyWordsN(iCtrN)))
Loop
Next iCtrN
End If

Next cmt

'Places New Hires in new hires cell
ActiveCell.Offset(43, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = totNew

'Places Terminations in termination cell
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = totTerm

End Sub

This works perfectly for what I originally needed to do. Now I need to
add a feature to this. And like I said this one is a BIGGER challenge
(If it isn't a big challenge, then sorry for the hype...I'm still a
novice :) ).

The reason I have this macro to begin with is to know how many hired
people there were and how many terminations there were (I forgot that
transfers didn't count, and I eliminated it as you can see in the
code). And with each cell that was selected holds a different
department of my company. Each having their own department number.

The numbers are placed adjacent to the selected cells (more
specifically B7:B34, B37:B42, and B46:B47...the reason for the gaps is
because of other "Total" values in the table) And as you know the
selected cells are the ones with comments that have the keyword values
and such.

Remember, this is an *ADDITION* to what I have...Everything else works
GREAT!

I need to know which department had ONLY the "V. Term" and "I. Term"
keywords. I also need to know to what amount (i.e. "5 V. Terms", like
in the code I have now) and THEN I need to place that specific number
in that one comment in a table BELOW the selected cells (still in the
same column, though) in coordinance with the same department number
based on cells B58:B78. Notice that there are less cells in the new
table than the one's adjacent to the selected cells. This is because
there are a few choice cells in B58:B78 (namely B71:B73, B75, and B78)
which instead of holding the numbers inside the cells as text, they
have comments with multiple department numbers (i.e. Comment:"152 + 134
+ 155").

Here is an example (this is the best I can come up with):
B7= 111------------------------------------C7=Comment:"1 V. Term"
B8= 112------------------------------------C8=Comment:"2 I. Terms"
B9= 113------------------------------------C9=Comment:"1 Transfer out"
B10=114-----------------------------------C10=Comment:"1 New Hire"
B11=115-----------------------------------C11=Comment:"1 Transfer In"
.......................................I should End up with the
following (IN Column C, Column B has set
values).............................
B69=111-----------------------------------C69=1
B70=Comment:"112 + 113 + 114"-----C70=2
B71=115-----------------------------------C71=0

Hope this isn't TOO difficult

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

You're right. I'll see what I can do on Monday with it. Unless I ge
bored over the weakend

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

eh...too much to fix with the whole "re-design" thing
i bought a book on VBA for Excel, so maybe this will help

I've got to put this one on hold for now. I'll post what I end up with
when I finish it....unless someone else can do it! ;)


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