View Full Version : [SOLVED] Find/Replace in Excel

02-24-2017, 03:01 PM
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


i hope if anyone can help or give me any hint

thanks in advance



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

02-24-2017, 05:08 PM
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?

02-24-2017, 05:17 PM
"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


02-24-2017, 05:47 PM
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"

02-24-2017, 06:02 PM
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)

02-24-2017, 06:25 PM
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

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

02-24-2017, 06:42 PM
Hi SamT,

well i tried your code and i got this msg


i attached sample file too



Thanks in advance

02-24-2017, 07:45 PM
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

02-24-2017, 08:12 PM
Attached now 👍

02-24-2017, 09:08 PM
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

Application.ScreenUpdating = True
End Sub

02-25-2017, 02:28 AM
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