Function MatchCommentext()
 

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

Function MatchCommentext()
 

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

Function MatchCommentext()
 

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

Function MatchCommentext()
 

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

Function MatchCommentext()
 

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.

Function MatchCommentext()
 

Inserting a comment doesn't fire a Calculate event. To ensure that the
result is correct you need to force a calculation (F9).

In article ,
"MikeF" wrote:

... Am I missing something?

Function MatchCommentext()
 

Hi, thanx.
It's the first thing I tried ... nearly punched the f9 key off the keyboard
[!!].
Even went to Tools/Options and hit recalc there.

In your file, when you change the location of the comment and recalc, the
function returns the changed/correct column number???!??
It's not doing that in the one I downloaded.

Sincerely, I appreciate your help.
Thanx in advance for working through this with me.

- Mike

"JE McGimpsey" wrote:

Inserting a comment doesn't fire a Calculate event. To ensure that the
result is correct you need to force a calculation (F9).

In article ,
"MikeF" wrote:

... Am I missing something?

Function MatchCommentext()
 

Try a full recalc - CTRL-ALT-F9

In article ,
"MikeF" wrote:

It's the first thing I tried ... nearly punched the f9 key off the keyboard
[!!].
Even went to Tools/Options and hit recalc there.

Function MatchCommentext()
 

Yup, that did it.
I did not know about "full recalc".
Works properly now.

May I trouble you for one more code adjustment?
Instead of the column number, is there a way to have the function return the
cell address??

Sincerely appreciated.
Thank you again.

- Mike



"JE McGimpsey" wrote:

Try a full recalc - CTRL-ALT-F9

In article ,
"MikeF" wrote:

It's the first thing I tried ... nearly punched the f9 key off the keyboard
[!!].
Even went to Tools/Options and hit recalc there.

Function MatchCommentext()
 

Yup, that did it.
I was unaware of "full recalc".

Then I deleted both comments. Everything went to #N/A as expected.
However, when I put one or both back, in any relevant cell, the function
cells remained at #N/A. Full recalc did not impact them in this scenario.

At the risk of belaboring this, should it not be dynamic?
Ie where there's a comment, there's a corrseponding column number.
Where there's not, the function would be #N/A.

Thanx again.
- Mike





"JE McGimpsey" wrote:

Try a full recalc - CTRL-ALT-F9

In article ,
"MikeF" wrote:

It's the first thing I tried ... nearly punched the f9 key off the keyboard
[!!].
Even went to Tools/Options and hit recalc there.

Function MatchCommentext()
 

Try changing this:

MatchCommentText = i
to
MatchCommentText = rng(i).address(0,0)

And when I added "application.volatile", it recalculated when I hit the F9 key:

Option Explicit

Public Function MatchCommentText( _
criterion As String, rng As Range) As Variant
Application.Volatile
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 = rng(i).Address(0, 0)
bFound = True
Exit For
End If
End If
Next i
On Error GoTo 0
If Not bFound Then MatchCommentText = CVErr(xlErrNA)
End Function

And modifying comments, changing formats isn't enough to cause excel to
recalculate the cell with the formula.

By adding "application.volatile", the UDF will recalculate whenever excel
recalcs--so if you use this lots of places, you may find your workbook slowing
down.

But remember to calculate before you trust the results of the formula--you may
be one calc behind at any given time.

MikeF wrote:

Yup, that did it.
I did not know about "full recalc".
Works properly now.

May I trouble you for one more code adjustment?
Instead of the column number, is there a way to have the function return the
cell address??

Sincerely appreciated.
Thank you again.

- Mike

"JE McGimpsey" wrote:

Try a full recalc - CTRL-ALT-F9

In article ,
"MikeF" wrote:

It's the first thing I tried ... nearly punched the f9 key off the keyboard
[!!].
Even went to Tools/Options and hit recalc there.


--

Dave Peterson

Function MatchCommentext()
 

Dave,
Thanx for the assistance.
That worked well, but the same issue is occuring ... when I delete the
comment, and/or change both the criteria text and comment text, the function
returns #n/a no matter how many recalcs.
Since there will be a couple of hundred rows, with comments and critreria
changing all the time, the dynamics of this solution don't seem to be working.

Is there a solution that simply looks for criteria text in a cell, and if
that text matches a comment in the lookup range, the cell address of the
match can be placed in the same row, in a specific column?

Again, thanx to everyone for the help here.
- Mike

"Dave Peterson" wrote:

Try changing this:

MatchCommentText = i
to
MatchCommentText = rng(i).address(0,0)

And when I added "application.volatile", it recalculated when I hit the F9 key:

Option Explicit

Public Function MatchCommentText( _
criterion As String, rng As Range) As Variant
Application.Volatile
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 = rng(i).Address(0, 0)
bFound = True
Exit For
End If
End If
Next i
On Error GoTo 0
If Not bFound Then MatchCommentText = CVErr(xlErrNA)
End Function

And modifying comments, changing formats isn't enough to cause excel to
recalculate the cell with the formula.

By adding "application.volatile", the UDF will recalculate whenever excel
recalcs--so if you use this lots of places, you may find your workbook slowing
down.

But remember to calculate before you trust the results of the formula--you may
be one calc behind at any given time.

MikeF wrote:

Yup, that did it.
I did not know about "full recalc".
Works properly now.

May I trouble you for one more code adjustment?
Instead of the column number, is there a way to have the function return the
cell address??

Sincerely appreciated.
Thank you again.

- Mike

"JE McGimpsey" wrote:

Try a full recalc - CTRL-ALT-F9

In article ,
"MikeF" wrote:

It's the first thing I tried ... nearly punched the f9 key off the keyboard
[!!].
Even went to Tools/Options and hit recalc there.


--

Dave Peterson

Function MatchCommentext()
 

My bet is that your string to match and the comment text are not identical.

It could be an upper/lower case problem--or it could be an extra line feed at
the end of the comment's text (my problem when I tested!).

Select your cell with the text to match and copy the value from the formula
bar. Edit the comment and paste that in--but don't hit enter! Just click on
another cell when you're done.

Then force a recalc.

If you don't care about upper/lower, then change this:
If sTest = criterion Then
to
If lcase(sTest) = lcase(criterion) Then




MikeF wrote:

Dave,
Thanx for the assistance.
That worked well, but the same issue is occuring ... when I delete the
comment, and/or change both the criteria text and comment text, the function
returns #n/a no matter how many recalcs.
Since there will be a couple of hundred rows, with comments and critreria
changing all the time, the dynamics of this solution don't seem to be working.

Is there a solution that simply looks for criteria text in a cell, and if
that text matches a comment in the lookup range, the cell address of the
match can be placed in the same row, in a specific column?

Again, thanx to everyone for the help here.
- Mike

"Dave Peterson" wrote:

Try changing this:

MatchCommentText = i
to
MatchCommentText = rng(i).address(0,0)

And when I added "application.volatile", it recalculated when I hit the F9 key:

Option Explicit

Public Function MatchCommentText( _
criterion As String, rng As Range) As Variant
Application.Volatile
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 = rng(i).Address(0, 0)
bFound = True
Exit For
End If
End If
Next i
On Error GoTo 0
If Not bFound Then MatchCommentText = CVErr(xlErrNA)
End Function

And modifying comments, changing formats isn't enough to cause excel to
recalculate the cell with the formula.

By adding "application.volatile", the UDF will recalculate whenever excel
recalcs--so if you use this lots of places, you may find your workbook slowing
down.

But remember to calculate before you trust the results of the formula--you may
be one calc behind at any given time.

MikeF wrote:

Yup, that did it.
I did not know about "full recalc".
Works properly now.

May I trouble you for one more code adjustment?
Instead of the column number, is there a way to have the function return the
cell address??

Sincerely appreciated.
Thank you again.

- Mike

"JE McGimpsey" wrote:

Try a full recalc - CTRL-ALT-F9

In article ,
"MikeF" wrote:

It's the first thing I tried ... nearly punched the f9 key off the keyboard
[!!].
Even went to Tools/Options and hit recalc there.


--

Dave Peterson


--

Dave Peterson

Function MatchCommentext()
 

That's what I thought as well.
But I checked it *thoroughly*, pasted the text from the criteria cell into
the comment, and deleted the username insertion.
- Mike

"Dave Peterson" wrote:

My bet is that your string to match and the comment text are not identical.

It could be an upper/lower case problem--or it could be an extra line feed at
the end of the comment's text (my problem when I tested!).

Select your cell with the text to match and copy the value from the formula
bar. Edit the comment and paste that in--but don't hit enter! Just click on
another cell when you're done.

Then force a recalc.

If you don't care about upper/lower, then change this:
If sTest = criterion Then
to
If lcase(sTest) = lcase(criterion) Then




MikeF wrote:

Dave,
Thanx for the assistance.
That worked well, but the same issue is occuring ... when I delete the
comment, and/or change both the criteria text and comment text, the function
returns #n/a no matter how many recalcs.
Since there will be a couple of hundred rows, with comments and critreria
changing all the time, the dynamics of this solution don't seem to be working.

Is there a solution that simply looks for criteria text in a cell, and if
that text matches a comment in the lookup range, the cell address of the
match can be placed in the same row, in a specific column?

Again, thanx to everyone for the help here.
- Mike

"Dave Peterson" wrote:

Try changing this:

MatchCommentText = i
to
MatchCommentText = rng(i).address(0,0)

And when I added "application.volatile", it recalculated when I hit the F9 key:

Option Explicit

Public Function MatchCommentText( _
criterion As String, rng As Range) As Variant
Application.Volatile
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 = rng(i).Address(0, 0)
bFound = True
Exit For
End If
End If
Next i
On Error GoTo 0
If Not bFound Then MatchCommentText = CVErr(xlErrNA)
End Function

And modifying comments, changing formats isn't enough to cause excel to
recalculate the cell with the formula.

By adding "application.volatile", the UDF will recalculate whenever excel
recalcs--so if you use this lots of places, you may find your workbook slowing
down.

But remember to calculate before you trust the results of the formula--you may
be one calc behind at any given time.

MikeF wrote:

Yup, that did it.
I did not know about "full recalc".
Works properly now.

May I trouble you for one more code adjustment?
Instead of the column number, is there a way to have the function return the
cell address??

Sincerely appreciated.
Thank you again.

- Mike

"JE McGimpsey" wrote:

Try a full recalc - CTRL-ALT-F9

In article ,
"MikeF" wrote:

It's the first thing I tried ... nearly punched the f9 key off the keyboard
[!!].
Even went to Tools/Options and hit recalc there.


--

Dave Peterson


--

Dave Peterson

Function MatchCommentext()
 

I'd check again.

MikeF wrote:

That's what I thought as well.
But I checked it *thoroughly*, pasted the text from the criteria cell into
the comment, and deleted the username insertion.
- Mike

"Dave Peterson" wrote:

My bet is that your string to match and the comment text are not identical.

It could be an upper/lower case problem--or it could be an extra line feed at
the end of the comment's text (my problem when I tested!).

Select your cell with the text to match and copy the value from the formula
bar. Edit the comment and paste that in--but don't hit enter! Just click on
another cell when you're done.

Then force a recalc.

If you don't care about upper/lower, then change this:
If sTest = criterion Then
to
If lcase(sTest) = lcase(criterion) Then




MikeF wrote:

Dave,
Thanx for the assistance.
That worked well, but the same issue is occuring ... when I delete the
comment, and/or change both the criteria text and comment text, the function
returns #n/a no matter how many recalcs.
Since there will be a couple of hundred rows, with comments and critreria
changing all the time, the dynamics of this solution don't seem to be working.

Is there a solution that simply looks for criteria text in a cell, and if
that text matches a comment in the lookup range, the cell address of the
match can be placed in the same row, in a specific column?

Again, thanx to everyone for the help here.
- Mike

"Dave Peterson" wrote:

Try changing this:

MatchCommentText = i
to
MatchCommentText = rng(i).address(0,0)

And when I added "application.volatile", it recalculated when I hit the F9 key:

Option Explicit

Public Function MatchCommentText( _
criterion As String, rng As Range) As Variant
Application.Volatile
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 = rng(i).Address(0, 0)
bFound = True
Exit For
End If
End If
Next i
On Error GoTo 0
If Not bFound Then MatchCommentText = CVErr(xlErrNA)
End Function

And modifying comments, changing formats isn't enough to cause excel to
recalculate the cell with the formula.

By adding "application.volatile", the UDF will recalculate whenever excel
recalcs--so if you use this lots of places, you may find your workbook slowing
down.

But remember to calculate before you trust the results of the formula--you may
be one calc behind at any given time.

MikeF wrote:

Yup, that did it.
I did not know about "full recalc".
Works properly now.

May I trouble you for one more code adjustment?
Instead of the column number, is there a way to have the function return the
cell address??

Sincerely appreciated.
Thank you again.

- Mike

"JE McGimpsey" wrote:

Try a full recalc - CTRL-ALT-F9

In article ,
"MikeF" wrote:

It's the first thing I tried ... nearly punched the f9 key off the keyboard
[!!].
Even went to Tools/Options and hit recalc there.


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Function MatchCommentext()
 

My mistake, you are correct.
There was a *space* left in the comment.
Agh!
I will see if this will work in the larger sheet now.

- Mike

"Dave Peterson" wrote:

My bet is that your string to match and the comment text are not identical.

It could be an upper/lower case problem--or it could be an extra line feed at
the end of the comment's text (my problem when I tested!).

Select your cell with the text to match and copy the value from the formula
bar. Edit the comment and paste that in--but don't hit enter! Just click on
another cell when you're done.

Then force a recalc.

If you don't care about upper/lower, then change this:
If sTest = criterion Then
to
If lcase(sTest) = lcase(criterion) Then




MikeF wrote:

Dave,
Thanx for the assistance.
That worked well, but the same issue is occuring ... when I delete the
comment, and/or change both the criteria text and comment text, the function
returns #n/a no matter how many recalcs.
Since there will be a couple of hundred rows, with comments and critreria
changing all the time, the dynamics of this solution don't seem to be working.

Is there a solution that simply looks for criteria text in a cell, and if
that text matches a comment in the lookup range, the cell address of the
match can be placed in the same row, in a specific column?

Again, thanx to everyone for the help here.
- Mike

"Dave Peterson" wrote:

Try changing this:

MatchCommentText = i
to
MatchCommentText = rng(i).address(0,0)

And when I added "application.volatile", it recalculated when I hit the F9 key:

Option Explicit

Public Function MatchCommentText( _
criterion As String, rng As Range) As Variant
Application.Volatile
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 = rng(i).Address(0, 0)
bFound = True
Exit For
End If
End If
Next i
On Error GoTo 0
If Not bFound Then MatchCommentText = CVErr(xlErrNA)
End Function

And modifying comments, changing formats isn't enough to cause excel to
recalculate the cell with the formula.

By adding "application.volatile", the UDF will recalculate whenever excel
recalcs--so if you use this lots of places, you may find your workbook slowing
down.

But remember to calculate before you trust the results of the formula--you may
be one calc behind at any given time.

MikeF wrote:

Yup, that did it.
I did not know about "full recalc".
Works properly now.

May I trouble you for one more code adjustment?
Instead of the column number, is there a way to have the function return the
cell address??

Sincerely appreciated.
Thank you again.

- Mike

"JE McGimpsey" wrote:

Try a full recalc - CTRL-ALT-F9

In article ,
"MikeF" wrote:

It's the first thing I tried ... nearly punched the f9 key off the keyboard
[!!].
Even went to Tools/Options and hit recalc there.


--

Dave Peterson


--

Dave Peterson

Function MatchCommentext()
 

JE,

The solution works well.
Thank you.

- Mike

"JE McGimpsey" wrote:

Try a full recalc - CTRL-ALT-F9

In article ,
"MikeF" wrote:

It's the first thing I tried ... nearly punched the f9 key off the keyboard
[!!].
Even went to Tools/Options and hit recalc there.

Function MatchCommentext()
 

Thank you sincerely.
This solution works well.

- Mike

"Dave Peterson" wrote:

I'd check again.

MikeF wrote:

That's what I thought as well.
But I checked it *thoroughly*, pasted the text from the criteria cell into
the comment, and deleted the username insertion.
- Mike

"Dave Peterson" wrote:

My bet is that your string to match and the comment text are not identical.

It could be an upper/lower case problem--or it could be an extra line feed at
the end of the comment's text (my problem when I tested!).

Select your cell with the text to match and copy the value from the formula
bar. Edit the comment and paste that in--but don't hit enter! Just click on
another cell when you're done.

Then force a recalc.

If you don't care about upper/lower, then change this:
If sTest = criterion Then
to
If lcase(sTest) = lcase(criterion) Then




MikeF wrote:

Dave,
Thanx for the assistance.
That worked well, but the same issue is occuring ... when I delete the
comment, and/or change both the criteria text and comment text, the function
returns #n/a no matter how many recalcs.
Since there will be a couple of hundred rows, with comments and critreria
changing all the time, the dynamics of this solution don't seem to be working.

Is there a solution that simply looks for criteria text in a cell, and if
that text matches a comment in the lookup range, the cell address of the
match can be placed in the same row, in a specific column?

Again, thanx to everyone for the help here.
- Mike

"Dave Peterson" wrote:

Try changing this:

MatchCommentText = i
to
MatchCommentText = rng(i).address(0,0)

And when I added "application.volatile", it recalculated when I hit the F9 key:

Option Explicit

Public Function MatchCommentText( _
criterion As String, rng As Range) As Variant
Application.Volatile
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 = rng(i).Address(0, 0)
bFound = True
Exit For
End If
End If
Next i
On Error GoTo 0
If Not bFound Then MatchCommentText = CVErr(xlErrNA)
End Function

And modifying comments, changing formats isn't enough to cause excel to
recalculate the cell with the formula.

By adding "application.volatile", the UDF will recalculate whenever excel
recalcs--so if you use this lots of places, you may find your workbook slowing
down.

But remember to calculate before you trust the results of the formula--you may
be one calc behind at any given time.

MikeF wrote:

Yup, that did it.
I did not know about "full recalc".
Works properly now.

May I trouble you for one more code adjustment?
Instead of the column number, is there a way to have the function return the
cell address??

Sincerely appreciated.
Thank you again.

- Mike

"JE McGimpsey" wrote:

Try a full recalc - CTRL-ALT-F9

In article ,
"MikeF" wrote:

It's the first thing I tried ... nearly punched the f9 key off the keyboard
[!!].
Even went to Tools/Options and hit recalc there.


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson