+ Reply to Thread
Results 1 to 10 of 10

Using Replace() to search for a two-word phrase

Hybrid View

  1. #1
    Registered User
    Join Date
    08-11-2011
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007/ 2010
    Posts
    72

    Using Replace() to search for a two-word phrase

    When I run the following against a single word, it runs fine, but I need to run it against a two-word phrase. I'm getting a bug when I try.

    Sub B_ReplaceHeaders()
        
        'Replace Last Name with LAST_NAME
        Dim LastName As String
        Dim LAST_NAME As String
        
        'Replace First Name with FIRST_NAME
        Dim FirstName As String
        Dim FIRST_NAME As String
        
        
        LastName = Range("A1").Value
        LAST_NAME = Replace("Last Name", "Last Name", "LAST_NAME")
        
        FirstName = Range("B1").Value
        FIRST_NAME = Replace("First Name", "First Name", "FIRST_NAME")
        
        Range("A1").Offset(, 0).Value = LastName
        Range("B1").Offset(, 0).Value = FirstName
    
    End Sub
    Any ideas on how I might account for the space in the search string?

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Using Replace() to search for a two-word phrase

    can you provide the workbook to better understand and test?
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    08-11-2011
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007/ 2010
    Posts
    72

    Re: Using Replace() to search for a two-word phrase

    Sure - See attached.

    It's the B_ReplaceHeaders macro...
    Attached Files Attached Files
    Last edited by jpcsolutions; 06-09-2015 at 01:35 PM. Reason: Added macro title

  4. #4
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Using Replace() to search for a two-word phrase

    It is not clear to me what you are trying to accomplish. Are you just trying to change cell A1 so that instead of "Last Name" is says "LAST_NAME"?

    If so, then this will do that

    Range("A1").Replace What:="Last Name", Replacement:="LAST_NAME"
    Last edited by stnkynts; 06-09-2015 at 01:44 PM.

  5. #5
    Registered User
    Join Date
    08-11-2011
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007/ 2010
    Posts
    72

    Re: Using Replace() to search for a two-word phrase

    Yes - That's exactly what I'm trying to do. I have other 'Replace()' functions running in the same Workbook, and they run fine when running a single term - like this...

        Term = Range("C1").Value
        CertificateDate = Replace(Term, "Term", "Certficate Date")
    It works fine...

  6. #6
    Registered User
    Join Date
    08-11-2011
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007/ 2010
    Posts
    72

    Re: Using Replace() to search for a two-word phrase

    Did you try what you suggested? It's not working for me?

    Can you show me the full block? Or was it only that line you replaced?

  7. #7
    Registered User
    Join Date
    08-11-2011
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007/ 2010
    Posts
    72

    Re: Using Replace() to search for a two-word phrase

    When I add the line you suggested, this is what my block looks like...

    Sub B_ReplaceHeaders()
    
        'Replace Last Name with LAST_NAME
        Dim LastName As String
        Dim LAST_NAME As String
    
        'Replace First Name with FIRST_NAME
        Dim FirstName As String
        Dim FIRST_NAME As String
    
    
        LastName = Range("A1").Value
        LAST_NAME = Replace("Last Name", "Last Name", "LAST_NAME")
    
        FirstName = Range("B1").Value
        FIRST_NAME = Replace("First Name", "First Name", "FIRST_NAME")
    
        Range("A1").Offset(, 0).Value = LastName
        Range("B1").Offset(, 0).Value = FirstName
    
    End Sub
    Is there something I'm missing?

  8. #8
    Registered User
    Join Date
    08-11-2011
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007/ 2010
    Posts
    72

    Re: Using Replace() to search for a two-word phrase

    Nevermind. I've figured it out. I removed everything else and just used what you suggested. It was simply to cluttered with code. Thanks for your help!

  9. #9
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Using Replace() to search for a two-word phrase

    Is there something I'm missing?
    Yes. Your last lines are referencing the wrong variable. It should instead be:
    Range("A1").Offset(, 0).Value = LAST_NAME 'Note: the offset is pointless in this situation
    Range("B1").Offset(, 0).Value = FIRST_NAME

    With that being said, you should only need these 2 lines (tested and worked).

    Sub B_ReplaceHeaders()
    
    Range("A1").Replace What:="Last Name", Replacement:="LAST_NAME"
    Range("B1").Replace What:="First Name", Replacement:="FIRST_NAME"
    
    End Sub

  10. #10
    Registered User
    Join Date
    08-11-2011
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007/ 2010
    Posts
    72

    Re: Using Replace() to search for a two-word phrase

    Thanks for the reply. I've added to your rep. Much appreciated!

+ 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. Search by word and replace with date
    By devpp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-11-2015, 05:29 AM
  2. [SOLVED] Search using a Word/Phrase
    By hiteshasrani43 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 03-05-2015, 01:02 AM
  3. Replies: 2
    Last Post: 10-09-2014, 05:14 AM
  4. Search and Replace... from Excel to Word
    By Xx7 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-27-2011, 03:05 PM
  5. [SOLVED] Newbie trying to search and replace word in word doc from excel
    By kaiser in forum Excel General
    Replies: 5
    Last Post: 09-13-2005, 07:06 AM

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