Find/Replace in Excel

[Ethen5155]

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



18470


i hope if anyone can help or give me any hint


thanks in advance


cheers


Ethen



Cross-Posting: http://www.excelforum.com/excel-programming-vba-macros/1175365-find-replace-in-excel.html#post4591554

[SamT]

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?

[Ethen5155]

"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

[SamT]

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

[Ethen5155]

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)

[SamT]

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

[Ethen5155]

Hi SamT,

well i tried your code and i got this msg

18473



i attached sample file too


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

http://www.filedropper.com/sample_17

Thanks in advance

[SamT]

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

[Ethen5155]

Attached now 👍

[Paul_Hossler]

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

[Ethen5155]

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