Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi all

    I'm trying to return the address of a comment cell on the activesheet. I tried the cells find method but I can not get it to work in code. Any help would be great.


    Thanks, Darryl


    The new forum looks great!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What exactly do you want to accomplish?

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HUHandEH;
    Hi Hans!

    I'm trying to return the location of a cell that holds a comment with my string in the comment.

    Thanks,
    Darryl

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does this do what you want?

    Code:
    Sub FindComment()
      Dim strText As String
      Dim rng As Range
      Dim oCell As Range
      strText = InputBox("Enter text to look for")
      If strText = "" Then
    	MsgBox "You didn't enter anything", vbCritical
    	Exit Sub
      End If
      Set rng = Cells.SpecialCells(xlCellTypeComments)
      If rng Is Nothing Then
    	MsgBox "There are no cells with comments!", vbExclamation
      Else
    	For Each oCell In rng.Cells
      	If InStr(oCell.Comment.Text, strText) > 0 Then
        	MsgBox "Text found in comment of cell " & oCell.Address, vbInformation
      	End If
    	Next oCell
      End If
    End Sub

  5. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi Hans,

    Thanks you're best as always!

    Darryl.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    This will give the address of the cell with the 1st comment:

    ActiveSheet.Comments(1).Parent.Address

    I am not sure what you are after, perhaps this will give you enough to adapt to what you want/need. Or if you can elaborate on what you want to do we could be more specific

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •