loop for macro

[Steve]

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 were:


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]

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

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 were:

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.

 

[Steve]

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.

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

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 were:

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.