#1
|
|||
|
|||
Macro to change cell references
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. |
#2
|
||||
|
||||
So what, exactly, does the external-sheet reference to A20 look like? Like this?
Code:
='C:\path\[external.xlsx]Sheet1'!$A$20 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: Code:
' Assume that co is the cell object we're pointing at. vf = co.Formula co.Formula = Left(vf, pr - 1) & Mid(vf, pr) + 2 Code:
pr = InStrRev(vf, "$") + 1 Code:
For pr = Len(vf) To 1 Step -1 If InStr("0123456789", Mid(vf, pr, 1)) = 0 Then Exit For Next pr pr = pr + 1 |
#3
|
|||
|
|||
Thanks Bob!
The external sheet references are as follows: Quote:
Quote:
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? Last edited by SaneMan; 09-09-2013 at 07:15 AM. |
#4
|
||||
|
||||
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:
Code:
vf = co.Formula 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 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 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? |
#5
|
|||
|
|||
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: Quote:
Quote:
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. |
#6
|
||||
|
||||
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>>. Last edited by macropod; 10-01-2013 at 02:50 PM. Reason: emal address removed |
#7
|
||||
|
||||
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] |
Tags |
cell references, macro, vba excel |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating a Macro to change the shading of a cell | Triscia | Word VBA | 3 | 01-30-2013 04:18 PM |
Change formula cell range based on cell value | Scoth | Excel | 4 | 10-25-2012 07:51 AM |
Formula that references cell with text in different workbook | no1texan | Excel | 3 | 07-17-2012 05:58 AM |
Detect a cell has un-approved change or not? | LongTTH | Excel Programming | 1 | 05-20-2012 02:22 AM |
how to change number in cell | ubns | Excel | 5 | 05-14-2012 04:21 PM |