Variable in a Search and Replace macro
 

I have a formula in many worksheets that needs to be modified.

I have a macro that goes to each worksheet but I would like to use a
variable in the text to be replaced so I dont have to update the macro for
each sheet.

I would like to something like;

FOR X= 1 TO 80

Selection.Replace What:="min1", Replacement:="min27", etc etc....


NEXT X

Where the replacement string becomes

Replacement:="minX"

and increments for each x

Is this possible?

thanks
sandy

Variable in a Search and Replace macro
 

Do you mean something like

Sub FindIt(myVar)
FOR X= 1 TO 80
Selection.Replace What:="min1", Replacement:=myVar, etc etc....
NEXT X
End Sub

which you would usitlise like so


FindIt "min27"
FindIt "minX"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sandy" wrote in message
...
I have a formula in many worksheets that needs to be modified.

I have a macro that goes to each worksheet but I would like to use a
variable in the text to be replaced so I dont have to update the macro for
each sheet.

I would like to something like;

FOR X= 1 TO 80

Selection.Replace What:="min1", Replacement:="min27", etc etc....


NEXT X

Where the replacement string becomes

Replacement:="minX"

and increments for each x

Is this possible?

thanks
sandy

Variable in a Search and Replace macro
 

Sandy,

Perhaps:

Replacement:="min" & X

Don't know what you want to use as the replace, though....

HTH,
Bernie
MS Excel MVP


"Sandy" wrote in message
...
I have a formula in many worksheets that needs to be modified.

I have a macro that goes to each worksheet but I would like to use a
variable in the text to be replaced so I dont have to update the macro for
each sheet.

I would like to something like;

FOR X= 1 TO 80

Selection.Replace What:="min1", Replacement:="min27", etc etc....


NEXT X

Where the replacement string becomes

Replacement:="minX"

and increments for each x

Is this possible?

thanks
sandy

Variable in a Search and Replace macro
 

Hi Bob -

I only need to update part of the variable in the macro....

This is the folumla I need to update in Col D:
=IF(ISBLANK(G9)," ",IF(AND(B9<max53,B9=min53),"pass","Fail"))

This is the full macro:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveSheet.Next.Select

ActiveSheet.Unprotect
Columns("D:D").Select

Selection.Replace What:="min1", Replacement:="min54",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="max1", Replacement:="max54", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("g9").Select


Each time I go to the next worksheet, I need to update the named cell that
is called up in the formula -- ie "min54" becomes "min55"

Variable in a Search and Replace macro
 

Perhaps

i=53
For Each sh In Activeworkbooks.Worksheets
With sh
.Unprotect
.Columns("D:D").Select.Replace What:="min1", _

Replacement:="min" & i,
LookAt:=xlPart,
_

SearchOrder:=xlByRows, _

MatchCase:=False, _

SearchFormat:=False, _

ReplaceFormat:=False
.Columns("D:D").Select.Replace What:="min1", _

Replacement:="max" & i,
LookAt:=xlPart,
_

SearchOrder:=xlByRows, _

MatchCase:=False, _

SearchFormat:=False, _

ReplaceFormat:=False
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
.EnableSelection = xlUnlockedCells
i = i +1
Next sh


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sandy" wrote in message
...

Hi Bob -

I only need to update part of the variable in the macro....

This is the folumla I need to update in Col D:
=IF(ISBLANK(G9)," ",IF(AND(B9<max53,B9=min53),"pass","Fail"))

This is the full macro:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveSheet.Next.Select

ActiveSheet.Unprotect
Columns("D:D").Select

Selection.Replace What:="min1", Replacement:="min54",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="max1", Replacement:="max54", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("g9").Select


Each time I go to the next worksheet, I need to update the named cell that
is called up in the formula -- ie "min54" becomes "min55"