6

I've written some VBA code which takes a single cell and identifies all its dependents in the workbook (via a NavigateArrow analysis) and adds their range location to an array. From here I want to be able to update each dependent and change the reference to the original single cell to another single specified cell.

The particular difficulty I'm having here is that although I know where each dependent is, the reference to the original cell may be at the start, middle or end of a formula, and may be unanchored, row/column/both anchored, may be on a different worksheet and hence have a worksheet reference preceding it, etc, etc. Therefore I can't make an easy find and replace in each dependent cell, because of these potential differences, plus I want to maintain the original anchoring held in each cell reference.

Is there an elegant - or even inelegant - VBA solution to this problem?

Pekka
  • 3,529
  • 27
  • 45
Man of Leng
  • 73
  • 4
  • 1
    Seems like there are only four variants of the reference/address you'd need to replace (relative/fixed row/fixed col/fixed row+col), so you could just loop through all those and replace if found (adjusting the replacement address accordingly) – Tim Williams Nov 12 '12 at 21:59
  • Yeah, I can't see any other way, which is a shame as it seems a bit clunky. Thanks for your help! – Man of Leng Nov 14 '12 at 16:11
  • I think those aren't the only variants - wouldn't it include named ranges as well? Or are they counted as separate references? – Chris Rae Nov 30 '12 at 01:36
  • Getting this right can be very tricky. Give http://www.technicana.com/products/xlcomponents.html a try (an application I wrote), as it moves reference dependencies in the way you describe. – Chris Spicer Jan 07 '13 at 14:08

2 Answers2

1

Regular Expressions, or Regexp, are what you are looking for I think.

The following Pattern

([A-Z0-9]*)!(\${0,1})([A-Z]{1,3})(\${0,1})([0-9]*)

will match anything like "Sheet1!A1", "Sheet1!$A$1", "Sheet1!$A1", "Sheet1!A$1"

Explanation:

([A-Z0-9]*)!  =  Find anything that is before "!"
(\${0,1}) = $ or nothing
([A-Z]{1,3})  = between one and three letters
([0-9]*)  = Any number

You should easily be able to modify that pattern to match only what you want. In particular, ([A-Z0-9]*)!(\${0,1})B(\${0,1})1, will only match something with B($)1 in it... Construct the Regexp pattern with string manipulation and should be good.

You'll need to reference (Tool > Reference) the "Microsoft VBScript Regular Expressions 5.5"

Try the following code, this should give you all the tools to achieve your goal

Sub ReplaceReference()
' Reference: Microsoft VBScript Regular Expressions 5.5

Dim RegEx As Object
Set RegEx = New RegExp

Dim s As String

' Here I have hardcoded the reference to the original cell for demonstration purposes
s = "Sheet1!$AB$2"


' Replacement: New sheetname, New Column, new row number
Dim NewCol As String, NewRow As String
NewCol = "C"
NewRow = "10"

Dim NewSheet As String
NewSheet = "Sheet2"


With RegEx
    .Pattern = "([A-Z0-9]*)!(\${0,1})([A-Z]{1,3})(\${0,1})([1-9]*)"
    .IgnoreCase = True
    .Global = True
End With


Debug.Print RegEx.Replace(s, NewSheet & "!" & "$2" & NewCol & "$4" & NewRow)


End Sub

Cheers, Julien

Julien Marrec
  • 11,605
  • 4
  • 46
  • 63
0

Why not have the VBA cut and paste the source cell? Then, Excel will adjust the cell references in all their glorious variety.

I created a few cells with references, and then used the Macro recorder to see what could happen in the generated VBA when selecting a cell, cutting and copying the contents. The behaviour is as expected, as follows:

Range("A1").Select
Selection.Cut
Range("B1").Select
ActiveSheet.Paste

This will apply to the following worksheet:

A1  1
A2  =A1
A3  =$A$1
A4  =1+A1
A5  =1+A1+1

After running the macro, all the references point to the B1 destination cell.

Pekka
  • 3,529
  • 27
  • 45