code takes to long
 

Why does this code take about 4-5 minutes to excute? I does 4 replacements
for each cell.

Range("C16:BJ17").Select
Selection.Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

code takes to long
 

Try running it by not selecting the range first?

With Range("C16:BJ17")
.Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
End With

--

Regards,
Nigel




"Scooter" wrote in message
...
Why does this code take about 4-5 minutes to excute? I does 4
replacements
for each cell.

Range("C16:BJ17").Select
Selection.Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

code takes to long
 

Hi

Try to skip the select statement:

Range("C16:BJ17").Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

Regards,
Per

"Scooter" skrev i meddelelsen
...
Why does this code take about 4-5 minutes to excute? I does 4
replacements
for each cell.

Range("C16:BJ17").Select
Selection.Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

code takes to long
 

Hi,

I would guess you have a lot of formula and each change is forcing a
recalculation so disable calculations and screenupdating

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

'your code

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Mike

"Scooter" wrote:

Why does this code take about 4-5 minutes to excute? I does 4 replacements
for each cell.

Range("C16:BJ17").Select
Selection.Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

code takes to long
 

It runs nearly instantly for me on some made up data, and properly does the
substitution. I'm running Office 2003

Eric

"Scooter" wrote:

Why does this code take about 4-5 minutes to excute? I does 4 replacements
for each cell.

Range("C16:BJ17").Select
Selection.Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

code takes to long
 

That did not seem to have any affect. With 2003 it was fast and this
started with 2007.

"Per Jessen" wrote:

Hi

Try to skip the select statement:

Range("C16:BJ17").Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

Regards,
Per

"Scooter" skrev i meddelelsen
...
Why does this code take about 4-5 minutes to excute? I does 4
replacements
for each cell.

Range("C16:BJ17").Select
Selection.Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

code takes to long
 

That did not seem to have any affect. With 2003 it was fast and this
started with 2007.

"Mike H" wrote:

Hi,

I would guess you have a lot of formula and each change is forcing a
recalculation so disable calculations and screenupdating

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

'your code

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Mike

"Scooter" wrote:

Why does this code take about 4-5 minutes to excute? I does 4 replacements
for each cell.

Range("C16:BJ17").Select
Selection.Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

code takes to long
 

That did not seem to have any affect. With 2003 it was fast and this
started with 2007.

"Nigel" wrote:

Try running it by not selecting the range first?

With Range("C16:BJ17")
.Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
End With

--

Regards,
Nigel




"Scooter" wrote in message
...
Why does this code take about 4-5 minutes to excute? I does 4
replacements
for each cell.

Range("C16:BJ17").Select
Selection.Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

code takes to long
 

With 2003 it was fast and this started with 2007.

"Eric G" wrote:

It runs nearly instantly for me on some made up data, and properly does the
substitution. I'm running Office 2003

Eric

"Scooter" wrote:

Why does this code take about 4-5 minutes to excute? I does 4 replacements
for each cell.

Range("C16:BJ17").Select
Selection.Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False