Macro to replace in two worksheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

yshridhar - 15 May 2008 04:15 GMT

Hi everybody
The following macro i recorded to replace a value in sheet1 and sheet2.

Sub replas()
Sheets(Array("Sheet1", "Sheet2")).Select
Sheets("Sheet2").Activate
Cells.Replace What:="10", Replacement:="a", LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
Range("B1").Select
End Sub

But when i try to run it, it is replacing only in Sheet2. How to modify it?
Any suggestions. Thanks to all
With regards
Sreedhar

Reply to this Message

Jim Cone - 15 May 2008 05:14 GMT

Here is one way...
'--
Sub replas_R1()
Sheets("Sheet1").Cells.Replace What:="10", Replacement:="a", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Sheets("Sheet2").Cells.Replace What:="10", Replacement:="a", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Range("B1").Select
End Sub

Signature

Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"yshridhar" <yshridhar@discussions.microsoft.com>
wrote in message
Hi everybody
The following macro i recorded to replace a value in sheet1 and sheet2.

Sub replas()
Sheets(Array("Sheet1", "Sheet2")).Select
Sheets("Sheet2").Activate
Cells.Replace What:="10", Replacement:="a", LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
Range("B1").Select
End Sub

But when i try to run it, it is replacing only in Sheet2. How to modify it?
Any suggestions. Thanks to all
With regards
Sreedhar

Reply to this Message

yshridhar - 15 May 2008 05:29 GMT

Thanks Jim for your suggestion. Is there any way to do it by selecting all
the sheets at once.
regards
Sreedhar

> Here is one way...
> '--
[quoted text clipped - 9 lines]
> Range("B1").Select
> End Sub

Reply to this Message

Jim Cone - 15 May 2008 14:34 GMT

Not that I know of.
There is very little in VBA that can be accomplished on multiple sheets
with only one iteration. My experience has been that it consumes all
of your random access memory while trying.
Jim Cone

"yshridhar"
<yshridhar@discussions.microsoft.com>
wrote in message
Thanks Jim for your suggestion. Is there any way to do it by selecting all
the sheets at once.
regards
Sreedhar

"Jim Cone" wrote:

> Here is one way...
> '--
[quoted text clipped - 9 lines]
> Range("B1").Select
> End Sub

> "yshridhar" <yshridhar@discussions.microsoft.com>
> wrote in message
[quoted text clipped - 14 lines]
> With regards
> Sreedhar

Reply to this Message

Gord Dibben - 16 May 2008 01:01 GMT

Sub replas()
Sheets(Array("Sheet1", "Sheet2")).Select
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Cells.Replace What:="10", Replacement:="a", LookAt:=xlWhole, _
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B1").Select
Next ws
Sheets("Sheet1").Select
End Sub

Gord Dibben MS Excel MVP

>Thanks Jim for your suggestion. Is there any way to do it by selecting all
>the sheets at once.
>regards
>Sreedhar

Reply to this Message

yshridhar - 16 May 2008 05:03 GMT

Thanks Gord.
Regards
Sreedhar

> Sub replas()
> Sheets(Array("Sheet1", "Sheet2")).Select
[quoted text clipped - 15 lines]
> >regards
> >Sreedhar

Reply to this Message