I thought I actually ran that code before listing it for you, so I'm not sure why it isn't working for you. Hold on, let me try it myself....

Oh, I see the problem; I left it to you to decide what code to use to determine what value to put in pr, and you skipped that part entirely so pr doesn't have any value in it. That's why the statement "co.Formula = Left(vf, pr - 1) & Mid(vf, pr) + 2" comes up with an error, because pr doesn't mean anything yet.

Look, in order to write a program, you have to understand that each part of it actually does something specific, something that you can understand. It's not (so to speak) magic; you're just constructing a set of instructions that the machine will follow exactly. So when I talk generally, and perhaps vaguely, about a way to accomplish something, then you have to figure out what instructions will actually accomplish it. You. I could just write the program, without explaining it, but the only way that would do you any good is by you looking at the program I wrote and saying "oh, I see—you do this, and then that, and then the result is right. Ok, I can do that". Because, after all, if you can't do that yourself by the time we're done, then you can't fix it when it doesn't work. Which is what's happening now, actually.

To do this stuff, then, you have to look at each piece of the code I suggest, understand it, and ask questions about anything you're not sure of. Also feel free to try to change it, to test out your own suppositions about how it works—because even if the tests prove your supposition was wrong, the very act of thinking and testing is what'll turn you into a programmer in the end.

Now, back to the program. I see what happened, why it didn't work; but even if you'd understood what I meant, and put in code that could work out a value for pr, that program wouldn't do what you wanted; it would have changed "A20" to "A22", when what you want is to change it to "C20". So let's keep the basic outline of that program, but we'll have to change the part of it that's in the middle of the loop.

There are two basic ways to do this:

1) You can convert the column letter to a number, add 2 to the number, then calculate back the other way to a letter. Sort of like this: The address is [something]M99; M is the 13th letter in the alphabet; 13+2=15; the 15th letter in the alphabet is O; so change the address to [something]O99. There are actually built-in VBA functions that'll do the letter-to-number and number-to-letter conversions for you: ASC("A"), for example, gives you 65 (because A uses code 65 in ASCII), and CHR(65+2) returns "C".

2) You can get Excel to tell you the column number, rather than the letter, and add 2 to that. I prefer this way—it seems simpler to me.

Most people use Excel's default way of addressing cells, what's called the "A1" reference style. But Excel can also use what's called R1C1 reference style; it can switch back and forth as often as you like. In R1C1 style, $A$1 is displayed as R1C1 (row 1, column 1, you see). That's for absolute address, of course. Relative addresses would be, for example, R[-1]C; that means one row up (the [-1] part), and this column. Personally I use R1C1 all the time, except when talking to someone else about Excel. Why? Well, for one thing, not many people use that style; for an oddball like me that's actually a selling point ("I'm different!"). But even more than that, it's simpler to think that way when you're writing programs.

And there's also this: When you're in A1 style using relative addressing up and down a particular column, the formula on every row is different, because the row and/or column designations change. But in R1C1, the formula is exactly the same through the range. That's a minor advantage when trying to see where something changed accidentally.

But you don't have to switch to R1C1 to use it in VBA. Just as co.Formula points to the cell's formula in A1 style, co.FormulaR1C1 gives you the same formula translated into R1C1 reference style. "='C:\path\[external.xlsx]Sheet1'!$A$20" in A1 notation is just "='C:\path\[external.xlsx]Sheet1'!R1C20"; and if your cell B16 says "='H:\Desktop\[CP Final.xls]c1a'!A20", that's a relative reference and would be "='H:\Desktop\[CP Final.xls]c1a'!R[-1]C[4]". If you can find the column number in the FormulaR1C1 property, then you can increment it by two and it'll have its effect in your worksheet without ever leaving A1 notation.

Which method would you like to use? Or would you rather try them both and see which one you like better?

By the way, it might be easier to do this by email. If you want, feel free to contact me at<<email address deleted>>.