Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Advanced find and replace

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Im looking for the VBA code to search a spreadsheet and find a specific word and replace it with another, but the replacement word has to be underlined.
    Any help would be greatly appreciated
    dunk

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Could give the following a shot:

    Sub ReplaceAndUnderline()
    Dim rFound As Range
    Dim szFirst As String
    Dim iCount As Integer
    Dim oldval As String
    Dim newval As String
    oldval = "test"
    newval = "testing"
    Set rFound = Cells.Find(what:=oldval, lookat:=xlWhole)
    iCount = 0
    Do While Not rFound Is Nothing
    If szFirst = "" Then
    szFirst = rFound.Address
    ElseIf rFound.Address = szFirst Then
    Exit Do
    End If
    rFound.Value = Application.Substitute(rFound.Value, _
    oldval, newval)
    rFound.Font.Underline = True
    iCount = iCount + 1
    Set rFound = Cells.FindNext(rFound)
    Loop
    End Sub

    Just change the oldval and newval strings.


    Cheers,

    Nate

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This works great, thank you very much.

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Glad to hear it. Good hunting.

    Cheers, Nate

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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