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



Attachment 18470


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

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?

"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

Code:

---

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

---

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)

100000 cells? I'm going to rewrite it

Code:

---

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

Hi SamT,

well i tried your code and i got this msg

Attachment 18473



i attached sample file too


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

http://www.filedropper.com/sample_17

Thanks in advance

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

Attached now 👍

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

Code:

---


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

---

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