I've been scratching my head for the last couple of days with a Visual Basic problem and was hoping for some help.



I have a spreadsheet in Excel with a hundred and so rows of data and each cell has a reference to another spreadsheet it feeds its information from. For example, the data in column B16 of my spreadsheet is populated by its cell reference to this other spreadsheet.

I've been trying to write a macro which changes the cell references (i.e. A20) by going two letters up in the alphabet. So A20 would become C20, B12 would become D12, etc). The macro needs to be able to amend the cell reference formulas this way, if it's possible. So in the end the macro would run this in a loop and save copies of various spreadsheets and save them with a identifiable filename. I have everything working but the cell reference bit.

Any help would be much appreciated.

So what, exactly, does the external-sheet reference to A20 look like? Like this?If so, I guess what you want is some code that a) looks at the cell's Formula property, b) finds the row number and c) replaces it with a different number.

In the character string "='C:\path\[external.xlsx]Sheet1'!$A$20", the row (20) starts in character position 37. Once you have that position in a variable—let's call it pr, ie the Position of the Row—you can modify the cell's Formula property like this:But how do we find pr? If you know that all the references will be absolute, and (an important point) always will be, that is, the next time you run this macro as well, then you can use InStrRev:If you don't know that, you can still search backward in a loop:Me, I'm tempted to go with Regular Expressions. But I'm new to them (shame on me), and would have to look up exactly how to do it in this case. Maybe this is enough to get you started.

Thanks Bob!

The external sheet references are as follows:

I tried inserting the code you provided into my macro and it when I ran it a dialogue box comes up saying "Object required". Here's what my macro looks like:

I didn't think it was likely it'd work after just slotting it in, but as I'm fairly new to this I couldn't really tell what your macro was doing.

Thanks for the help with this. It's much appreciated. Do you know what I should do to make your code work with the rest of my macro?

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

Thanks again Bob. You're very good at explaining this to someone as inexperienced as myself!

Unfortunately the cell references are completely random. This spreadsheet was not set up by me and the cell references are structured so that the data reads in a specific way to make the data clearer. This means it is completely re-ordered from the original data which is why I went ahead with VBA.

Your code definitely seems to be almost there. This is what it looks like now:

It just seems to be the following line which doesn't work:

I tried changing this line with a simple code to replace each cell with 'blah' and the macro worked fine, so it's something in this line which VBA doesn't seem to like. A dialogue comes up when I run it saying "invalid procedure cell or argument". There is no option to debug, only 'ok' or 'help' which doesn't reveal anything helpful, at least from what I can understand.

You are correct that it's the cell letter that I wish to change and not also the number. Any ideas?

I can't stress how grateful I am for you taking the time to answer my queries! Life saver.

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>>.

A simple way of doing this without code is:
1. Open both workbooks, allowing the target workbook to update its links.
2. Insert two columns into the source workbook
3. Save & close the target workbook
4. Close the source workbook without saving
Now, when you re-open the target workbook, all the column references should be correct.

The above process could, of course, be automated with a macro (without any of the circumlocutions of what's been discussed so far).

Rob: Please don't solicit or accept requests for off-line assistance.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]