macro to remove ALL the " ' and [String]" (links) on a worksheet

[sdoppke]

Hi everyone, can someone help me insert some script into my macro that will remove all the text in pink on a whorksheet (its not really in pink, just helping to describe :

=VLOOKUP(A15,'S:\hello\WFR\Test\[Post and Script]VCR Copy and Paste'!A$1:C$65536,3,0)

Thank you so much for any help. :0

sd

 

[seenfresh]

Hi everyone, can someone help me insert some script into my macro that will remove all the text in pink on a whorksheet (its not really in pink, just helping to describe :

=VLOOKUP(A15,'S:\hello\WFR\Test\[Post and Script]VCR Copy and Paste'!A$1:C$65536,3,0)

Thank you so much for any help. :0

sd

You just want to remove all text "'S:\hello\WFR\Test\[Post and Script]" in your worksheet formulas?

Have you tried the Find and Replace procedure? Hit CTRL+F and in the Find dialogue paste: 'S:\hello\WFR\Test\[Post and Script] and leave the Replace dialogue blank...?

 

[sdoppke]

You just want to remove all text "'S:\hello\WFR\Test\[Post and Script]" in your worksheet formulas?

Have you tried the Find and Replace procedure? Hit CTRL+F and in the Find dialogue paste: 'S:\hello\WFR\Test\[Post and Script] and leave the Replace dialogue blank...?

Yep, i tried using the macro recorder but it doesnt work. I am trying to remove the entire link, so yes , everything from the S to the ]

Any idea how to write that in a script?

sd

 

[mirabeau]

try

Dim x
x = ActiveCell.Replace("'*]", "")

or equivalent.

In this, no need to define x as anything.

 

[sdoppke]

try

Dim x
x = ActiveCell.Replace("'*]", "")

or equivalent.

In this, no need to define x as anything.

Thank you so much, im on the right track, what would it take to modify that so that it does that to ever cell on that worksheet?

sd

 

[RSXchin]

cells.replace("'*], "")

 

[sdoppke]

cells.replace("'*], "")

sa weet!

 

[mirabeau]

Dim x As Boolean
x = ActiveSheet.UsedRange.Replace("'*]", "")

although it may not work for cells with the ' at the start.

But should be easily modified if that's a problem for you.

 

[RSXchin]

so it worked? awesome. I didn't even try, but it SEEMED like it would have worked.