+ Reply to Thread
Results 1 to 5 of 5

Looping through range to replace data

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-28-2013
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    226

    Looping through range to replace data

    Hello Everybody,

    I have data in column A, as 005 d33++123121245, and i need to replace data "005 d33++". i need only numbers.
    when i tried the below code I am getting reslut "TRUE" not what i wanted, can anybody plesae help me to correct my code.

    Sub Macro2()
    
    
    For i = 1 To 100
        Cells(i, 1).Value = Cells(i, 1).Replace _
            (What:="005 d33++", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False)
    Next i
    
    End Sub
    Last edited by venkatpvc; 08-23-2013 at 06:05 PM. Reason: adding more explination

  2. #2
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Looping through range to replace data

    hi venkatpvc,

    Range("a1", Cells(Rows.Count, 1).End(xlUp)).Replace "005 d33++", "", xlPart

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Looping through range to replace data

    So simpler:


        Range("A1:A100").Replace What:="005 d33++", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False

    Or even:

        Range("A:A").Replace What:="005 d33++", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Forum Contributor
    Join Date
    03-28-2013
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    226

    Re: Looping through range to replace data

    thank you both, The codes work for me perfectly.....

    venkat

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Looping through range to replace data

    I've marked this thread as SOLVED for you.
    Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.

+ 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. [SOLVED] Retrieving XML data by looping through a range in a column
    By capnhud in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-28-2013, 05:24 AM
  2. Looping through values to Replace
    By vncntj in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-22-2012, 11:13 AM
  3. [SOLVED] copy and replace data using looping macro
    By cmb80 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-16-2012, 05:17 AM
  4. Looping through different columns in a range to populate the data in another workshee
    By deepika Jain in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-15-2011, 05:19 PM
  5. Add Text to Textbox value by looping through a data range.
    By Francis Brown in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-05-2005, 08: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