Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop for macro
Howdee all.
I just recorded a macro to strip off the path to a template document, for some formulas. The recorded code is: Sub extractMe() Cells.Replace What:= _ "C:\Documents and Settings\UserName\Application _ Data\Microsoft\Templates\[TR Claim Book.xltx]" _ , Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _ False, SearchFormat:=False, ReplaceFormat:=False End Sub I just tried placing it in a for loop, and I cannot get it to work right. I keep getting a 438 error. My throughts we Dim MyCell as range For each MyCell in ActiveSheet ' "replace" code from above next What code do I use for a loop that will just run through the single page-- I want to remove all of the template pathways that exist-- varies from 2 or 3 up to around 10 instances. Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop for macro
You shouldn't have to loop through all the cells. The edit|replace code should
work for all the cells on that sheet. But your code didn't compile for me. I have a feeling that you changed it after you pasted it into your message (based on how that "application data" string was split). This should be closer, but I don't think it will do what you want: Option Explicit Sub extractMe() Cells.Replace What:= _ "C:\Documents and Settings\UserName\Application" _ & " Data\Microsoft\Templates\[TR Claim Book.xltx]", _ Replacement:="", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False End Sub But I'm guessing that you're copying a worksheet from that template workbook and it still has formulas that point to sheets/ranges in the template workbook. If that's the case, then the formula is gonna look like this (simplified) example: ='C:\(longpathhere)\[TR Claim Book.xltx]Sheet1'!A1 And that means you want to include the initial apostrophe in your string to replace. Sub extractMe() Cells.Replace What:= _ "='C:\Documents and Settings\UserName\Application" _ & " Data\Microsoft\Templates\[TR Claim Book.xltx]", _ Replacement:="='", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False End Sub Notice that the ='(longpath)[filename]" is replaced with just the =' ============ ps. Another option may be to use: Edit|links|change links (in xl2003 menus) to point at the current file (that's been saved at least once!) Record a macro when you do it manually and you'll have the code. pps. Another option that I like to use. Another way that I use: Select the range to copy (in the source worksheet in the template workbook) Convert the formulas to string edit|replace what: = (equal sign) with: $$$$$= (a nice unused string) replace all Now I can copy|paste all these strings and they stay strings. Then I fix both the source and destination ranges by doing a couple of edit|replace's. Select the range to fix edit|replace what: $$$$$= with: = replace all Steve wrote: Howdee all. I just recorded a macro to strip off the path to a template document, for some formulas. The recorded code is: Sub extractMe() Cells.Replace What:= _ "C:\Documents and Settings\UserName\Application _ Data\Microsoft\Templates\[TR Claim Book.xltx]" _ , Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _ False, SearchFormat:=False, ReplaceFormat:=False End Sub I just tried placing it in a for loop, and I cannot get it to work right. I keep getting a 438 error. My throughts we Dim MyCell as range For each MyCell in ActiveSheet ' "replace" code from above next What code do I use for a loop that will just run through the single page-- I want to remove all of the template pathways that exist-- varies from 2 or 3 up to around 10 instances. Thank you. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop for macro
Hi Dave,
Hope you had a great weekend. I don't know why I thought that I had to do a loop. I'm guessing that I just wasn't thinking clearly-- sometimes that happens as the weeks wind down.... This works great now. Thank you. "Dave Peterson" wrote: You shouldn't have to loop through all the cells. The edit|replace code should work for all the cells on that sheet. But your code didn't compile for me. I have a feeling that you changed it after you pasted it into your message (based on how that "application data" string was split). This should be closer, but I don't think it will do what you want: Option Explicit Sub extractMe() Cells.Replace What:= _ "C:\Documents and Settings\UserName\Application" _ & " Data\Microsoft\Templates\[TR Claim Book.xltx]", _ Replacement:="", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False End Sub But I'm guessing that you're copying a worksheet from that template workbook and it still has formulas that point to sheets/ranges in the template workbook. If that's the case, then the formula is gonna look like this (simplified) example: ='C:\(longpathhere)\[TR Claim Book.xltx]Sheet1'!A1 And that means you want to include the initial apostrophe in your string to replace. Sub extractMe() Cells.Replace What:= _ "='C:\Documents and Settings\UserName\Application" _ & " Data\Microsoft\Templates\[TR Claim Book.xltx]", _ Replacement:="='", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False End Sub Notice that the ='(longpath)[filename]" is replaced with just the =' ============ ps. Another option may be to use: Edit|links|change links (in xl2003 menus) to point at the current file (that's been saved at least once!) Record a macro when you do it manually and you'll have the code. pps. Another option that I like to use. Another way that I use: Select the range to copy (in the source worksheet in the template workbook) Convert the formulas to string edit|replace what: = (equal sign) with: $$$$$= (a nice unused string) replace all Now I can copy|paste all these strings and they stay strings. Then I fix both the source and destination ranges by doing a couple of edit|replace's. Select the range to fix edit|replace what: $$$$$= with: = replace all Steve wrote: Howdee all. I just recorded a macro to strip off the path to a template document, for some formulas. The recorded code is: Sub extractMe() Cells.Replace What:= _ "C:\Documents and Settings\UserName\Application _ Data\Microsoft\Templates\[TR Claim Book.xltx]" _ , Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _ False, SearchFormat:=False, ReplaceFormat:=False End Sub I just tried placing it in a for loop, and I cannot get it to work right. I keep getting a 438 error. My throughts we Dim MyCell as range For each MyCell in ActiveSheet ' "replace" code from above next What code do I use for a loop that will just run through the single page-- I want to remove all of the template pathways that exist-- varies from 2 or 3 up to around 10 instances. Thank you. -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Loop | Excel Programming | |||
Do until loop with use of another macro in loop | Excel Programming | |||
loop a macro | Excel Discussion (Misc queries) | |||
VBA Macro Loop | Excel Programming | |||
Do...Loop Macro | Excel Programming |