Search through cells, if I find a string (external Link), replace it with a formula

Hi everyone. Thanks for all the generous help in the past few weeks. Once I know what the heck I am doing, i hope to assist as well.

I want to:
1) Look through a range of cells on a worksheet
2) If I locate a particular string (which is actually a link to an external workbook), I want to replace it with a formula.
3) A typical link looks like this: ='Y:\FY14 Strategic Plan\BU Templates\Consolidated and Parsed by Template\Initial Test\[Copy of Manual_Schedules_BU_Strat Plan_final TEST.xlsx]Brand Sales'!C32
where the link address changes obviously depending on the cell address. This is probably superfluous, but I figured I'd provide.

Here is my code so far (dimming and such omitted.) It just isn't working. The inStr function seems to be returning, like everything. I clearly don't have a good grasp of cycling through a worksheet. I appreciate your advice:

Please Login or Register  to view this content.

Thanks!

If you look at VBA help you will see the following about Instr

Returns a Variant (Long) specifying the position of the first occurrence of one string within another.

Syntax

InStr([start, ]string1, string2[, compare])

The InStr function syntax has these arguments:

Part Description
start Optional. Numeric expression that sets the starting position for each search. If omitted, search begins at the first character position. If start contains Null, an error occurs. The start argument is required if compare is specified.
string1 Required. String expression being searched.
string2 Required. String expression sought.
compare Optional. Specifies the type of string comparison. If compare is Null, an error occurs. If compare is omitted, the Option Compare setting determines the type of comparison. Specify a valid LCID (LocaleID) to use locale-specific rules in the comparison.

In particular look at string1 and string2 - the other arguments are optional - and compare with your code. Does that help?

I've read that through and I thought I was based on that. Maybe I should use something different than inStr?

What is the string expression being searched and what is the string expression sought?

In fact in your code there is no reference anywhere in your code to rCell.

In any case why not use Find and Replace?

StephenR,

I suspect this might not be the right approach. I just want to find anything that contains any version of this:

'Y:\FY14 Strategic Plan\BU Templates\Consolidated and Parsed by Template\Initial Test\[Copy of Manual_Schedules_BU_Strat Plan_final TEST.xlsx]

and replace it with a formula. mikerickson's approach to change via the .formula is a good one (previous post), but actually searching and finding the string and replacing it isn't working.

I've got it down to this, but it's still replacing ALL cells rather than just the ones with =Y:\ in the front.

Please Login or Register  to view this content.

I know I'm close. I just don't know if the inStr is the right approach. For some reason it thinks EVERY cell has =Y:\ in it, which doesn't make sense.

I think you are right, but when I change it to rCell instead of "=Y:\" for the first argument I get a type mismatch. But you definitely are right, I just don't quite know how to do that.

How do I make it look in rCell rather than =Y:\?

Tried rCell.value but still getting a type mismatch error. is it because it is a link and not a string?

Try this

Please Login or Register  to view this content.

StephenR,

Getting closer but not quite right. No more error, but it appears to not be finding anything. It just runs and does no changes. The sheet is filled with links that look similar, only with a different cell reference:

='Y:\FY14 Strategic Plan\BU Templates\Consolidated and Parsed by Template\2_Brand Sales\[Copy of Manual_Schedules_BU_Strat Plan_final TEST.xlsx]Brand Sales'

I also fixed the piece to be "='Y:\" since the ' was missing but that still didn't correct it. Really sorry to keep bothering you, I'm not sure why it's not working.

You need to compare the string with Formula property, not .Value/.Text
e.g

Please Login or Register  to view this content.

That did it! Would have never figured that out on my own without you guys. I always had a feeling that somehow that had to be seen as a formula, not a value from a string, but I just don't quite understand the object model as much as I should for the complicated things I've been doing. Can't wait to have the project done so I can go back and really try to understand the object model better.

I can't thank you both enough.