Consulting

Results 1 to 11 of 11

Thread: Find/Replace in Excel

  1. #1

    Find/Replace in Excel

    Hi all,


    i'm trying to find a sample way to do find and rename process on excel file with a specific modifications


    i need o look for a cell then rename the cell under it


    for ex,


    look for (English Section details) then rename the cell under it to (English Term) as shown below on screenshot



    0.png


    i hope if anyone can help or give me any hint


    thanks in advance


    cheers


    Ethen



    Cross-Posting: http://www.excelforum.com/excel-prog...ml#post4591554

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,047
    Location
    Ummm... Are you talking about Named Ranges (Cells) or about Cell Values?

    IOW Is "Term" the Name of a Range, or is it just what is entered in the Cell?
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  3. #3
    "Term" is the text content of the cell, and i just want to look for for any cell contains text "English Section details" then check if the cell under it contains text "Terms" ------> if yes then edit the text to "English Terms".thanks in advance for your efforts

    Cheers
    Last edited by Ethen5155; 02-24-2017 at 05:51 PM.

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,047
    Location
    dim Cel as Range
    for each Cel in YourRange 'Edit YourRange to reflect reality
    If Cel = "English Section details" And Cel.Offset(1) = "Terms" Then _
    Cel.Offset(1) = "English Terms"
    Next
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  5. #5
    Dear SamT,

    sorry for bothering you again and again, could you post the missing line for range in case i want to make the range to the whole sheet1 to excel file that contains more than 100.000 cell between column (A) and (B)

  6. #6
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,047
    Location
    100000 cells? I'm going to rewrite it

    Sub Change_TERMS()
    
    Const ESd As String * 23 = "english section details" 
    Const T As String * 5 = "terms"
    Const ET as String * 13= "English Terms"
    
    Dim Cel As Range 
    
    'UnComment next 4 lines after testing
    'With Application 
    .Screenupdating = False
    .Calculation = xlCalculationManual
    'End With
    
    For Each Cel In Sheets("Sheet1").UsedRange
        If LCase(Trim(Cel)) = ESD Then
          If LCase(Trim(Cel.Offset(1))) = T Then _ 
        Cel.Offset(1) = ET
       End If
    Next 
    
    With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    End With
    End Sub
    If that is too slow, and you think there's less than say 1000 "english section details"+"terms" pairs, I'll try using Find to see if it's faster
    Last edited by SamT; 02-24-2017 at 06:37 PM.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  7. #7

    Unhappy

    Hi SamT,

    well i tried your code and i got this msg

    2.png



    i attached sample file too


    http://www.megafileupload.com/1qa7l/Sample.zip

    http://www.filedropper.com/sample_17

    Thanks in advance

  8. #8
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,047
    Location
    6.68MB? Zipped?
    For testing purpose, all we need is maybe 100 Rows.

    You can upload that here by using the Go Advanced Editor and its Paper Clip menu icon
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  9. #9
    Attached now 👍
    Attached Files Attached Files

  10. #10
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,871
    Location
    Rows 52081 and 52083 in the Big Sample.xlsx have a #NAME error in column B so that was the Type Mismatch error

    Maybe this - only took 3-4 seconds on the large file

    Option Explicit
    Sub Change_Text()
        
        Dim rCell As Range
        
        Application.ScreenUpdating = False
         
        For Each rCell In Worksheets("Sheet1").UsedRange.Cells
            If VarType(rCell.Value) <> vbString Then GoTo GetNextCell
            If LCase(Trim(rCell.Value)) <> "english section details" Then GoTo GetNextCell
            If LCase(Trim(rCell.Offset(1, 0).Value)) <> "term" Then GoTo GetNextCell
            
            rCell.Offset(1, 0) = "English Terms"
            'rCell.Offset(1, 0).Interior.Color = vbRed     <<<< just for testing
    GetNextCell:
        Next
         
        Application.ScreenUpdating = True
    End Sub
    Last edited by Paul_Hossler; 02-24-2017 at 09:19 PM.
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #11
    Dear Paul,

    many thanks for your care and attention, you are right about those cells when i fix them the code works like magic now

    your help is highly appreciated really and you are my savior


    Dear SamT,

    i didn't forget your help too, thanks for your efforts and your time too

    Best Regards

Tags for this Thread

Posting Permissions

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