+ Reply to Thread
Results 1 to 4 of 4

VBA Find & replace text in comment

  1. #1
    Registered User
    Join Date
    08-12-2014
    Location
    Utah
    MS-Off Ver
    2010
    Posts
    2

    VBA Find & replace text in comment

    Hi there,

    I have found plenty of information on how to change the comments for the entire workbook/worksheet, but I need to modify that down to just a range.

    I am also wondering if this can be modified to find "HDPE" in the comment, but lookup the replace value from another cell. I am no VBA wizard, so thank you in advance for your help! I would like to replace two values in the comments. They would be my variable. The replacement values would come from cells D124 and E124. This process would then need to repeat until the row is empty. Hopefully, that is clear. Let me know what info you need from me in order to help.


    Here is the code currently....

    Sub ReplaceComments()

    Dim cmt As Comment
    Dim wks As Worksheet
    Dim sFind As String
    Dim sReplace As String
    Dim sCmt As String
    Dim rng As Range

    sFind = "HDPE"
    sReplace = "SDR-35"

    Set rng = Range("k108:R121")
    For Each cmt In rng.Comments
    sCmt = cmt.Text
    If InStr(sCmt, sFind) <> 0 Then
    sCmt = Application.WorksheetFunction. _
    Substitute(sCmt, sFind, sReplace)
    cmt.Text Text:=sCmt
    End If
    Next

    End Sub

  2. #2
    Valued Forum Contributor nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,194

    Re: VBA Find & replace text in comment

    Hi JKHildi & welcome to the forum,

    Try below code on a copy of your workbook ...
    Please Login or Register  to view this content.
    PS: Please take a moment & edit your post, highlight the code then press the # button from the menu to insert code tags for you
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  3. #3
    Registered User
    Join Date
    08-12-2014
    Location
    Utah
    MS-Off Ver
    2010
    Posts
    2

    Re: VBA Find & replace text in comment

    That worked for the find and replace. Thank you nankw83!

  4. #4
    Valued Forum Contributor nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,194

    Re: VBA Find & replace text in comment

    Glad to help & thanks for the Rep+

    Please take a moment & mark the thread as [SOLVED] from the Thread Tools above your first post

+ 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. Replace text in cell with text from comment
    By kalagas in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-05-2020, 05:23 PM
  2. [SOLVED] Find and replace value from list, add original value as comment
    By banaanas in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-06-2018, 02:41 AM
  3. Replies: 1
    Last Post: 02-27-2018, 11:22 AM
  4. [SOLVED] Search through columns and find specific text and replace that text with header column
    By adamzee in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2014, 11:59 PM
  5. find in excel replace in word: find/replace text in text boxes and headers
    By dean.rogers in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2012, 12:40 PM
  6. Find comment replace cell value
    By magarians@yahoo.ca in forum Excel General
    Replies: 3
    Last Post: 07-05-2006, 04:45 PM
  7. [SOLVED] Find and Replace in the Comment Field
    By Richcarvel in forum Excel General
    Replies: 2
    Last Post: 05-01-2006, 05:15 PM

Tags for this Thread

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