I'm pretty sure I can see what's happening. Here's how you can see it too: When VBA says "Object required"—or indeed when it comes up with almost any error—one of the option buttons it offers is "Debug". If you hit that button, it'll jump to the code, with the offending statement highlighted in yellow. If you run your macro again and hit Debug, this is the statement I think you'll see:
You'll remember that in the original sample I sent you, I preceded it with a comment saying "Assume that co is the cell object we're pointing at." I shouldn't have, because you did :-). I should have written something more like this: "Before you get to this part of the code, set co to the cell object we're pointing at". I assumed more knowledge on your part than I should have. Not your fault, I promise.
Ok, I should back up. Let's start here: You want your program to change the reference address not in one cell but in many. So you need to have part of your program in what we call a "loop", which just means it goes round and round, doing the same thing over and over but to a different cell each time. There are several ways to do that, but you've already made a start at one:
Code:
Sub newmacro2()
Workbooks.Open ("H:\Desktop\Z1 assessment for CP data_v3.xlsx")
Range("A1:BF85").Select
.
.
.
End Sub
That's not the only way to indicate to the program which cells you're going to work with, but since you're already doing that let's run with it: The cells whose references you want to update are in the Selection. So now all you have to do is tell Excel to do that bit of code I gave you
for each cell in that range, like this:
Code:
Sub newmacro2()
Workbooks.Open ("H:\Desktop\Z1 assessment for CP data_v3.xlsx")
Range("A1:BF85").Select
For Each co In Selection
vf = co.Formula
co.Formula = Left(vf, pr - 1) & Mid(vf, pr) + 2
Next co
ThisFile = Range("D1").Value
ActiveWorkbook.SaveAs Filename:="1 " + ThisFile
ActiveWorkbook.Close
End Sub
Selection, you see, is a collection of cells; and when you do "For Each <something> In <a Range>", it goes through the entire range, cell by cell; for each cell it sets <something> to that cell, then does the loop, then sets <something> to the next cell, does the loop, and so on until it's done it to every cell in the Range.
Now, I haven't tested this code, so there may still be syntax or other errors in there. But I'd say this is closer than you were.
---
Now, the next problem: I've been writing all along in the belief that you want to increment the reference's row number by two. Now I look again at your original post, and lo and behold, it says it's the
column number you want to change! I could have sworn....
Well, we can still do it, and it doesn't even have to be hard. But the code I gave you will update the row by two, rather than the column. So we have to change it around a bit.
First—I should have asked this before—is there a reason you want to do it in VBA? I have no objection, because I love VBA myself. Well, there are other languages I like better, easier to use and so forth. But I like doing things by programs more than by just doing them manually. But I know most people don't feel that way, and it occurs to me that you may not need a program at all.
Do all these cells point to the same
relative address? I mean, if A2 says "='H:\Desktop\[CP Final.xls]c1a'!C20", can you count on B3 saying "='H:\Desktop\[CP Final.xls]c1a'!D21"? If so, I don't think you need to write a program at all; you can just change one cell and copy it to all the rest.
But if all the cells point in different directions (so to speak), then that won't work and we're back to VBA. Which is it?