Sub to write another result dimension into comments over formula c

[Guest]

Here's my set-up (book is set to manual calc)

In sheet: Y, in A1:B5, I have data

Area Brch
Zone1 Br10
Zone2 Br11
Zone1 Br12
Zone2 Br13

and in sheet: X, in A1:B3

Area Brch
Zone1 2
Zone2 2

Unique zones are listed in A2 down
In B2, copied down is the formula: =COUNTIF(Y!A:A,A2)

I'm looking for a sub which can auto-write the names of the 2 branches into
the comments over the formula cells B2:B3 (which return the counts of the #
of branches for the zones) when I press F9 to recalc the book. This is to
provide another result dimension to the table in X, so as to speak.

So in the comment for B2 will be:
Br10
Br12

and in the comment for B3 will be:
Br11
Br13

Appreciate insights on how the above could be achieved. Thanks.

---

 

[Guest]

Thoughts, any one ?
Perhaps it was not possible ..

---

 

[Dave Peterson]

How about dumping the =countif() and using a UDF that does both of the work.

Option Explicit
Function myCountifComment(myRng As Range, myInCell As Range) As Long

'pass two columns to this function.

Dim myCount As Long
Dim myCell As Range
Dim myStr As String
Dim myAdjRng As Range

myCount = Application.CountIf(myRng.Columns(1), myInCell.Value)

On Error Resume Next
Application.Caller.Offset(0, -1).Comment.Delete
On Error GoTo 0

If myCount = 0 Then
'do nothing
Else
'this shouldn't be necessary
'if the range is out of the usedrange, then the countif should be 0
Set myAdjRng = Nothing
On Error Resume Next
Set myAdjRng = Intersect(myRng, myRng.Parent.UsedRange)
On Error Resume Next

For Each myCell In myAdjRng.Cells
If LCase(myCell.Value) = LCase(myInCell.Value) Then
myStr = myStr & vbLf & myCell.Offset(0, 1).Value
End If
Next myCell

If myStr = "" Then
'do nothing
Else
myStr = Mid(myStr, 2)
Application.Caller.Offset(0, -1).AddComment Text:=myStr
End If
End If

myCountifComment = myCount

End Function

=======
I used this formula in the worksheet:
=mycountifcomment(Y!A:B,A2)

Then if column A or B changed, the function would recalculate--changing the
comment or the value or both.

And there's not too much validation in this thing--so watch out.

 

[Guest]

How about dumping the =countif()
and using a UDF that does both of the work.

Superb, Dave! Many thanks. Runs great!

I tweaked the OFFSET in this line a little <g>
to get the comment to appear over the formula cells:

Application.Caller.Offset(0, 0).AddComment Text:=myStr


---

 

[Dave Peterson]

Oops.

You could have used:
Application.Caller.AddComment Text:=myStr

But more importantly, fix this line, too.
from:
Application.Caller.Offset(0, -1).Comment.Delete
to:
Application.Caller.Comment.Delete

That's the line that deletes the existing comment, so the .addcomment won't blow
up.

 

[Guest]

Thanks for the tweak corrections, Dave !