Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Search and Replace | Excel Discussion (Misc queries) | |||
macro to search and replace with offset | Excel Discussion (Misc queries) | |||
Macro to Search and Replace | Excel Programming | |||
SEARCH & REPLACE MACRO | Excel Programming | |||
Macro search replace question | Excel Programming |