Macro to change cell references

BobBridges · #2 09-06-2013, 07:39 AM

So what, exactly, does the external-sheet reference to A20 look like? Like this?

Code:

='C:\path\[external.xlsx]Sheet1'!$A$20

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:

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

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:

Code:

pr = InStrRev(vf, "$") + 1

If you don't know that, you can still search backward in a loop:

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

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.

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