+ Reply to Thread
Results 1 to 9 of 9

Finding specific data in cell comments and finding the line# in comments it appears on

  1. #1
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    119

    Finding specific data in cell comments and finding the line# in comments it appears on

    Hello,

    I did a search and did not find anything on this subject. I am using the cell comments to record changes to data in that cell with a date and time stamp. If today I enter '25' in cell A1, vba code will add the the cell's comment 2014-11-10 4:48:00 PM {25}

    If tomorrow I enter '82' in cell A1, vba code appends to the existing comment the new entry. The full comment will look as follows:

    2014-11-10 4:48:00 PM {25}
    2014-11-11 2:15:13 PM {82}

    Data may change many times for cell A1 and thus its comment field may have many lines in it each representing data entered at a specific date and time. If the comment field has 10 lines of data, is there a way to search the comment and find data on the last two lines, or say identify the 5th line if necessary and retrieve its data? In particular I would be after the data in the curly brackets.

    Thanks for any help.

    TV

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Finding specific data in cell comments and finding the line# in comments it appears on

    http://excel.tips.net/T002945_Searching_Comments.html


    Please Login or Register  to view this content.
    Last edited by mehmetcik; 11-10-2014 at 07:20 PM.

  3. #3
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    119

    Re: Finding specific data in cell comments and finding the line# in comments it appears on

    Hi mehmetcik,

    Thanks for your post. I thought it would be obvious I'm looking for a VBA approach as I posted in the VBA forum. I'm not looking for a simple search and I need to identify position of text preferably by identifying the row to look in once knowing how many rows of text the comment field has.

    TV

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Finding specific data in cell comments and finding the line# in comments it appears on

    Sorry I was working on some code for you.

    The above code worked for me.

    It searches for "test" in a comment and extracts the number within the curly brackets.

    The code below finds all occurrences of "test" in coments and extracts the number within the curly brackets into column A.
    Last edited by mehmetcik; 11-10-2014 at 08:17 PM.

  5. #5
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    119

    Re: Finding specific data in cell comments and finding the line# in comments it appears on

    Quote Originally Posted by mehmetcik View Post
    Sorry I was working on some code for you.

    The above code worked for me.

    It searches for "test" in a comment and extracts the number within the curly brackets.

    The code below finds all occurrences of "test" in coments and extracts the number within the curly brackets into column A.
    Your code does not work to solve my problem!

    You are still implementing a simple search and then extracting the first value found in curly brackets. There is no control in your code to pull the data in the curly brackets from a specific line in the comments. If you read my description you would see that there are multiple lines with curly brackets with data in them and a date/time stamp next to each one.

    You may be trying to be helpful but understanding the question and providing a solution for that question is the way to go.

    2014-11-10 4:48:00 PM {25}
    2014-11-11 2:15:13 PM {82}
    2014-11-11 3:29:14 PM {13}
    2014-11-11 4:03:17 PM {5}
    2014-11-11 5:35:13 PM {68}
    2014-11-11 6:26:18 PM {43}
    2014-11-11 7:02:19 PM {10007}
    2014-11-11 8:15:13 PM {256}
    2014-11-11 9:10:16 PM {45}

    If the above is the comment field of cell A1, how can I extract '68' from the fifth line by telling the code to look at data in the curly brackets found on the fifth line? The only information I have ahead of time is the line number I need to take the data from.

    I'm already formulating my own solution that counts the number of line breaks or Chr(10) in the comment field, locates the 5th occurrence of Chr(10), and returns the data in the curly brackets on that row.

    There are a lot of big brains here and I'm wondering if there is a quicker more elegant solution than that, for example is there a way to determine the number of lines in the comment field without counting Chr(10)? Is there a command that can just count the length of lines in the comment much like you count the number of used rows in a spreadsheet?

    Regards,

    TV

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Finding specific data in cell comments and finding the line# in comments it appears on

    Ok Have it your way.

    Sub Macro2()
    startpoint = InStr(1, Replace(Range("A1").Comment.Text, "{", "|", 1, 4), "{") + 1
    digits = InStr(startpoint, Range("A1").Comment.Text, "}") - startpoint
    MyNo = Mid(Range("A1").Comment.Text, startpoint, digits)
    End Sub

  7. #7
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    119

    Re: Finding specific data in cell comments and finding the line# in comments it appears on

    Did you test that code before posting?

  8. #8
    Forum Contributor
    Join Date
    05-29-2013
    Location
    MD
    MS-Off Ver
    Excel 365
    Posts
    148

    Re: Finding specific data in cell comments and finding the line# in comments it appears on

    Hi tv69,

    The line breaks method is what I would have used for this too. You could store the comment in a temporary array using the Split() function to split it out by Chr(10), that at least would give you easy access to the different lines.

    Thanks,
    Duncan

  9. #9
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    119

    Re: Finding specific data in cell comments and finding the line# in comments it appears on

    Duncan,

    Thank you for the tip. That will work out just great.

    Cheers,

    TV

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. copy cell text to comments in excel 2010 and vise versa (i.e. from comments to cell)
    By senthile in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-25-2014, 12:16 PM
  2. Append comments to a cell which already has comments
    By seanpears99 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-20-2013, 07:34 PM
  3. Finding Comments
    By thedryden in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2007, 11:57 AM
  4. Replies: 3
    Last Post: 03-02-2007, 07:13 AM
  5. [SOLVED] Cell Comments-Is possible to search for data in comments?
    By dmm129 in forum Excel General
    Replies: 3
    Last Post: 10-28-2005, 03:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1