Looking to build a function exactly like MATCH(), except that it would
evaluate the text of comments in a range.

If any comment in that range contained specific text in one cell, say
"MyText", then the function would return the column number of the cell in the
lookup range that contained the criteria comment text.

Example:
=MatchCommentext(criteria,lookuprange)

This function would go in cell a1.
In b1 would be the criteria ... MyText.
The lookup range could be b2.e2.
Cell c2 in the lookup range contains a comment with the same text as the
criteria cell [b1].
Therefore the function in this example would return a value of 2.

** Would a simpler variation be that it returned a value of TRUE or FALSE?
That would still work.

Thank you in advance for looking at this.


- Mike

One way:

Public Function MatchCommentText( _
criterion As String, rng As Range) As Variant
Dim rArea As Range
Dim i As Long
Dim sTest As String
Dim bFound As Boolean
On Error Resume Next
For i = 1 To rng.Count
sTest = rng(i).Comment.Text
If Not Err Then
If sTest = criterion Then
MatchCommentText = i
bFound = True
Exit For
End If
End If
Next i
On Error GoTo 0
If Not bFound Then MatchCommentText = CVErr(xlErrNA)
End Function

Note that if XL automatically inserts the username, that username, along
with ":" & a newline character are part of the text. One could modify
the function to ignore those characters, but it would likely also ignore
text in which those two characters appear.

You could also use a match if the value in the criterion exists anywhere
in a comment in the range by substituting

If sTest Like "*" & criterion & "*" Then

for

If sTest = criterion Then


In article ,
"MikeF" wrote:

Looking to build a function exactly like MATCH(), except that it would
evaluate the text of comments in a range.

If any comment in that range contained specific text in one cell, say
"MyText", then the function would return the column number of the cell in the
lookup range that contained the criteria comment text.

Example:
=MatchCommentext(criteria,lookuprange)

This function would go in cell a1.
In b1 would be the criteria ... MyText.
The lookup range could be b2.e2.
Cell c2 in the lookup range contains a comment with the same text as the
criteria cell [b1].
Therefore the function in this example would return a value of 2.

** Would a simpler variation be that it returned a value of TRUE or FALSE?
That would still work.

Thank you in advance for looking at this.


- Mike

Hello, and thank you for the reply.
I pasted the function in a regular module [presume that's where it goes..!].

On first attempt, it's returning an #N/A.
Here's the specifics ...

cell e153: =MATCHCommenTtext($E$149,AH153:CP153)
cell e149: MyText
cells ah153.cp153 are the lookup range.
cell ak153 contains a comment with MyText as the only text in it [no
username/etc].

PS ... I was incorrect in stating that returning TRUE or FALSE would work.
There needs to be a way to identify the cell address so a MATCH can be
performed on it.
Thanx again.

- Mike



"JE McGimpsey" wrote:

One way:

Public Function MatchCommentText( _
criterion As String, rng As Range) As Variant
Dim rArea As Range
Dim i As Long
Dim sTest As String
Dim bFound As Boolean
On Error Resume Next
For i = 1 To rng.Count
sTest = rng(i).Comment.Text
If Not Err Then
If sTest = criterion Then
MatchCommentText = i
bFound = True
Exit For
End If
End If
Next i
On Error GoTo 0
If Not bFound Then MatchCommentText = CVErr(xlErrNA)
End Function

Note that if XL automatically inserts the username, that username, along
with ":" & a newline character are part of the text. One could modify
the function to ignore those characters, but it would likely also ignore
text in which those two characters appear.

You could also use a match if the value in the criterion exists anywhere
in a comment in the range by substituting

If sTest Like "*" & criterion & "*" Then

for

If sTest = criterion Then


In article ,
"MikeF" wrote:

Looking to build a function exactly like MATCH(), except that it would
evaluate the text of comments in a range.

If any comment in that range contained specific text in one cell, say
"MyText", then the function would return the column number of the cell in the
lookup range that contained the criteria comment text.

Example:
=MatchCommentext(criteria,lookuprange)

This function would go in cell a1.
In b1 would be the criteria ... MyText.
The lookup range could be b2.e2.
Cell c2 in the lookup range contains a comment with the same text as the
criteria cell [b1].
Therefore the function in this example would return a value of 2.

** Would a simpler variation be that it returned a value of TRUE or FALSE?
That would still work.

Thank you in advance for looking at this.


- Mike

Works fine for me with your parameters. See

ftp://ftp.mcgimpsey.com/excel/mikef_demo.xls

In article ,
"MikeF" wrote:

Hello, and thank you for the reply.
I pasted the function in a regular module [presume that's where it goes..!].

On first attempt, it's returning an #N/A.
Here's the specifics ...

cell e153: =MATCHCommenTtext($E$149,AH153:CP153)
cell e149: MyText
cells ah153.cp153 are the lookup range.
cell ak153 contains a comment with MyText as the only text in it [no
username/etc].

PS ... I was incorrect in stating that returning TRUE or FALSE would work.
There needs to be a way to identify the cell address so a MATCH can be
performed on it.
Thanx again.

If I can change a litte bit in midstream here..
I may be overcomplicating the requirement.
All that's needed is for the function to return the address of the cell that
contains the comment criteria.

So in this instance ...
cell e153: =MATCHCommenTtext($E$149,AH153:CP153)
cell e149: MyText
cells ah153.cp153 are the lookup range.
cell ak153 contains a comment with MyText as the only text in it [no
username/etc].
.... In the above example, the function in e153 would return ak153.
And so on for as other rows as required.

So cell e154: =MATCHCommenTtext($E$149,AH154:CP154)
cell e149: MyText
cells ah154.cp154 are the lookup range.
cell cb154 contains a comment with MyText as the only text in it.
.... In the above example, the function in e154 would return cb154.
And so on for as other rows as required.

Thanx again. Hope this change isn't too weird. :)

- Mike





"JE McGimpsey" wrote:

One way:

Public Function MatchCommentText( _
criterion As String, rng As Range) As Variant
Dim rArea As Range
Dim i As Long
Dim sTest As String
Dim bFound As Boolean
On Error Resume Next
For i = 1 To rng.Count
sTest = rng(i).Comment.Text
If Not Err Then
If sTest = criterion Then
MatchCommentText = i
bFound = True
Exit For
End If
End If
Next i
On Error GoTo 0
If Not bFound Then MatchCommentText = CVErr(xlErrNA)
End Function

Note that if XL automatically inserts the username, that username, along
with ":" & a newline character are part of the text. One could modify
the function to ignore those characters, but it would likely also ignore
text in which those two characters appear.

You could also use a match if the value in the criterion exists anywhere
in a comment in the range by substituting

If sTest Like "*" & criterion & "*" Then

for

If sTest = criterion Then


In article ,
"MikeF" wrote:

Looking to build a function exactly like MATCH(), except that it would
evaluate the text of comments in a range.

If any comment in that range contained specific text in one cell, say
"MyText", then the function would return the column number of the cell in the
lookup range that contained the criteria comment text.

Example:
=MatchCommentext(criteria,lookuprange)

This function would go in cell a1.
In b1 would be the criteria ... MyText.
The lookup range could be b2.e2.
Cell c2 in the lookup range contains a comment with the same text as the
criteria cell [b1].
Therefore the function in this example would return a value of 2.

** Would a simpler variation be that it returned a value of TRUE or FALSE?
That would still work.

Thank you in advance for looking at this.


- Mike