Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA code for find/replace
I am trying to write code in a VBA module that will find and replace all
instances of a given text within that module. The text replacement is to be obtained through user input. Can anyone help? Thanks a lot! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA code for find/replace
Sub ReplaceItems()
ans = InputBox("Enter string to replace") ans1 = InputBox("Replace it with what?") if ans < "" then cells.Replace What:=ans, Replacement:=ans1, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End if End Sub This will replace substrings or whole words, case insensitive. If you want changes only for a match at the whole value of the cell, change xlPart to xlWhole If you want case sensitive, change MatchCase:=True If either ans or ans1 is specific (not prompted), then change the assignment statement: For Example, If I want to just change the word "the" to whatever the user specifies ans = "the" ans1 = InputBox("Replace ""the"" with what?") -- Regards, Tom Ogilvy "Needles" wrote in message ... I am trying to write code in a VBA module that will find and replace all instances of a given text within that module. The text replacement is to be obtained through user input. Can anyone help? Thanks a lot! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA code for find/replace
This is right out of the Excel Help:
This example finds all cells in the range A1:A500 that contain the value 2 and changes their values to 5. With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = 5 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Not sure if it is what you are looking for, but maybe you can adapt. "Needles" wrote: I am trying to write code in a VBA module that will find and replace all instances of a given text within that module. The text replacement is to be obtained through user input. Can anyone help? Thanks a lot! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA code for find/replace
Tom:
If I had 30 worksheets, which were identical in structure, And if I Grouped them and then on the 1st of the 30 sheets then Selected only cell D311 that had in it -- =B123+b234+b432+b999 I suppose if I then ran ReplaceItems() In the first inputbox if I entered +b432 And in the 2nd inputbox I entered +432+b500+b601 That afterwards ALL the cells D311 on All 30 sheets Would contain: =B123+b234+b432+b500+b601+b999 ? Right? Jim "Tom Ogilvy" wrote in message : Sub ReplaceItems() ans = InputBox("Enter string to replace") ans1 = InputBox("Replace it with what?") if ans < "" then cells.Replace What:=ans, Replacement:=ans1, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End if End Sub This will replace substrings or whole words, case insensitive. If you want changes only for a match at the whole value of the cell, change xlPart to xlWhole If you want case sensitive, change MatchCase:=True If either ans or ans1 is specific (not prompted), then change the assignment statement: For Example, If I want to just change the word "the" to whatever the user specifies ans = "the" ans1 = InputBox("Replace ""the"" with what?") -- Regards, Tom Ogilvy "Needles" wrote in message ... I am trying to write code in a VBA module that will find and replace all instances of a given text within that module. The text replacement is to be obtained through user input. Can anyone help? Thanks a lot! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA code for find/replace
No, the code as written works on all cells. Replace, does not work on
multiple selected sheets when executed in VBA. Sub ReplaceItems() Dim sh As Worksheet Dim s As String, ans, ans1 ans = InputBox("Enter string to replace") ans1 = InputBox("Replace it with what?") s = Selection.Address(0, 0) If ans < "" Then For Each sh In ActiveWindow.SelectedSheets Set rng = sh.Range(s) rng.Replace What:=ans, Replacement:=ans1, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Next End If End Sub should be something like what you want. Tested in xl2000 -- Regards, Tom Ogilvy "Jim May" wrote: Tom: If I had 30 worksheets, which were identical in structure, And if I Grouped them and then on the 1st of the 30 sheets then Selected only cell D311 that had in it -- =B123+b234+b432+b999 I suppose if I then ran ReplaceItems() In the first inputbox if I entered +b432 And in the 2nd inputbox I entered +b432+b500+b601 That afterwards ALL the cells D311 on All 30 sheets Would contain: =B123+b234+b432+b500+b601+b999 ? Right? Jim "Tom Ogilvy" wrote in message : Sub ReplaceItems() ans = InputBox("Enter string to replace") ans1 = InputBox("Replace it with what?") if ans < "" then cells.Replace What:=ans, Replacement:=ans1, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End if End Sub This will replace substrings or whole words, case insensitive. If you want changes only for a match at the whole value of the cell, change xlPart to xlWhole If you want case sensitive, change MatchCase:=True If either ans or ans1 is specific (not prompted), then change the assignment statement: For Example, If I want to just change the word "the" to whatever the user specifies ans = "the" ans1 = InputBox("Replace ""the"" with what?") -- Regards, Tom Ogilvy "Needles" wrote in message ... I am trying to write code in a VBA module that will find and replace all instances of a given text within that module. The text replacement is to be obtained through user input. Can anyone help? Thanks a lot! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA code for find/replace
"Thanks for your response. What I really need to do is to find/replace the
code in the module, not on the worksheet. I have 12 worksheets, each representing a separate month. I also have a number of worksheets, each representing a separate account. My code copies the month end totals from each "account worksheet" to the "month worksheet" . This involves moving back and forth among worksheets to copy and paste. I want the user to identify the new month and replace the previous month in the code designating the active worksheet for the paste operation. Does this make any sense at all? Is it possible? "Tom Ogilvy" wrote: No, the code as written works on all cells. Replace, does not work on multiple selected sheets when executed in VBA. Sub ReplaceItems() Dim sh As Worksheet Dim s As String, ans, ans1 ans = InputBox("Enter string to replace") ans1 = InputBox("Replace it with what?") s = Selection.Address(0, 0) If ans < "" Then For Each sh In ActiveWindow.SelectedSheets Set rng = sh.Range(s) rng.Replace What:=ans, Replacement:=ans1, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Next End If End Sub should be something like what you want. Tested in xl2000 -- Regards, Tom Ogilvy "Jim May" wrote: Tom: If I had 30 worksheets, which were identical in structure, And if I Grouped them and then on the 1st of the 30 sheets then Selected only cell D311 that had in it -- =B123+b234+b432+b999 I suppose if I then ran ReplaceItems() In the first inputbox if I entered +b432 And in the 2nd inputbox I entered +b432+b500+b601 That afterwards ALL the cells D311 on All 30 sheets Would contain: =B123+b234+b432+b500+b601+b999 ? Right? Jim "Tom Ogilvy" wrote in message : Sub ReplaceItems() ans = InputBox("Enter string to replace") ans1 = InputBox("Replace it with what?") if ans < "" then cells.Replace What:=ans, Replacement:=ans1, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End if End Sub This will replace substrings or whole words, case insensitive. If you want changes only for a match at the whole value of the cell, change xlPart to xlWhole If you want case sensitive, change MatchCase:=True If either ans or ans1 is specific (not prompted), then change the assignment statement: For Example, If I want to just change the word "the" to whatever the user specifies ans = "the" ans1 = InputBox("Replace ""the"" with what?") -- Regards, Tom Ogilvy "Needles" wrote in message ... I am trying to write code in a VBA module that will find and replace all instances of a given text within that module. The text replacement is to be obtained through user input. Can anyone help? Thanks a lot! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA code for find/replace
It doesn't make any sense from a design/accepted practice standpoint.
Think you need to rethink your approach. Best would be to eliminate any need to change code. Next best would be to use declared constants at the top so only the constants would need to be changed. Code doesn't need to move back and forth to copy and paste. sMonth = Format(date,"mmm") set sh = worksheets(sMonth) sh.Range("C20:F20").copy Destination:=Worksheets("Summary").Range("B5") as an example. -- regards, Tom Ogilvy "Needles" wrote in message ... "Thanks for your response. What I really need to do is to find/replace the code in the module, not on the worksheet. I have 12 worksheets, each representing a separate month. I also have a number of worksheets, each representing a separate account. My code copies the month end totals from each "account worksheet" to the "month worksheet" . This involves moving back and forth among worksheets to copy and paste. I want the user to identify the new month and replace the previous month in the code designating the active worksheet for the paste operation. Does this make any sense at all? Is it possible? "Tom Ogilvy" wrote: No, the code as written works on all cells. Replace, does not work on multiple selected sheets when executed in VBA. Sub ReplaceItems() Dim sh As Worksheet Dim s As String, ans, ans1 ans = InputBox("Enter string to replace") ans1 = InputBox("Replace it with what?") s = Selection.Address(0, 0) If ans < "" Then For Each sh In ActiveWindow.SelectedSheets Set rng = sh.Range(s) rng.Replace What:=ans, Replacement:=ans1, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Next End If End Sub should be something like what you want. Tested in xl2000 -- Regards, Tom Ogilvy "Jim May" wrote: Tom: If I had 30 worksheets, which were identical in structure, And if I Grouped them and then on the 1st of the 30 sheets then Selected only cell D311 that had in it -- =B123+b234+b432+b999 I suppose if I then ran ReplaceItems() In the first inputbox if I entered +b432 And in the 2nd inputbox I entered +b432+b500+b601 That afterwards ALL the cells D311 on All 30 sheets Would contain: =B123+b234+b432+b500+b601+b999 ? Right? Jim "Tom Ogilvy" wrote in message : Sub ReplaceItems() ans = InputBox("Enter string to replace") ans1 = InputBox("Replace it with what?") if ans < "" then cells.Replace What:=ans, Replacement:=ans1, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End if End Sub This will replace substrings or whole words, case insensitive. If you want changes only for a match at the whole value of the cell, change xlPart to xlWhole If you want case sensitive, change MatchCase:=True If either ans or ans1 is specific (not prompted), then change the assignment statement: For Example, If I want to just change the word "the" to whatever the user specifies ans = "the" ans1 = InputBox("Replace ""the"" with what?") -- Regards, Tom Ogilvy "Needles" wrote in message ... I am trying to write code in a VBA module that will find and replace all instances of a given text within that module. The text replacement is to be obtained through user input. Can anyone help? Thanks a lot! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA code for find/replace
Thanks, Tom. As you can tell, I'm a bit muddled on this. The find/replace
thing was code produced by recording a macro. I'm going to try rethinking with your suggestiions. Thanks again. "Tom Ogilvy" wrote: It doesn't make any sense from a design/accepted practice standpoint. Think you need to rethink your approach. Best would be to eliminate any need to change code. Next best would be to use declared constants at the top so only the constants would need to be changed. Code doesn't need to move back and forth to copy and paste. sMonth = Format(date,"mmm") set sh = worksheets(sMonth) sh.Range("C20:F20").copy Destination:=Worksheets("Summary").Range("B5") as an example. -- regards, Tom Ogilvy "Needles" wrote in message ... "Thanks for your response. What I really need to do is to find/replace the code in the module, not on the worksheet. I have 12 worksheets, each representing a separate month. I also have a number of worksheets, each representing a separate account. My code copies the month end totals from each "account worksheet" to the "month worksheet" . This involves moving back and forth among worksheets to copy and paste. I want the user to identify the new month and replace the previous month in the code designating the active worksheet for the paste operation. Does this make any sense at all? Is it possible? "Tom Ogilvy" wrote: No, the code as written works on all cells. Replace, does not work on multiple selected sheets when executed in VBA. Sub ReplaceItems() Dim sh As Worksheet Dim s As String, ans, ans1 ans = InputBox("Enter string to replace") ans1 = InputBox("Replace it with what?") s = Selection.Address(0, 0) If ans < "" Then For Each sh In ActiveWindow.SelectedSheets Set rng = sh.Range(s) rng.Replace What:=ans, Replacement:=ans1, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Next End If End Sub should be something like what you want. Tested in xl2000 -- Regards, Tom Ogilvy "Jim May" wrote: Tom: If I had 30 worksheets, which were identical in structure, And if I Grouped them and then on the 1st of the 30 sheets then Selected only cell D311 that had in it -- =B123+b234+b432+b999 I suppose if I then ran ReplaceItems() In the first inputbox if I entered +b432 And in the 2nd inputbox I entered +b432+b500+b601 That afterwards ALL the cells D311 on All 30 sheets Would contain: =B123+b234+b432+b500+b601+b999 ? Right? Jim "Tom Ogilvy" wrote in message : Sub ReplaceItems() ans = InputBox("Enter string to replace") ans1 = InputBox("Replace it with what?") if ans < "" then cells.Replace What:=ans, Replacement:=ans1, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End if End Sub This will replace substrings or whole words, case insensitive. If you want changes only for a match at the whole value of the cell, change xlPart to xlWhole If you want case sensitive, change MatchCase:=True If either ans or ans1 is specific (not prompted), then change the assignment statement: For Example, If I want to just change the word "the" to whatever the user specifies ans = "the" ans1 = InputBox("Replace ""the"" with what?") -- Regards, Tom Ogilvy "Needles" wrote in message ... I am trying to write code in a VBA module that will find and replace all instances of a given text within that module. The text replacement is to be obtained through user input. Can anyone help? Thanks a lot! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tidy up multiple find and replace code | Excel Worksheet Functions | |||
Code to modify find/replace | Excel Programming | |||
Pulling up the Find and Replace Dialog via Code | Excel Programming | |||
Using code to mimic a find and replace | Excel Programming | |||
Find and Replace code in Sheet modules | Excel Programming |