Replacing dependents with another cell reference in VBA

Question

I wrote VBA code that takes a single cell and identifies all its dependents in a workbook (through NavigateArrow analysis) and adds their range location to the array. From here I want to be able to update each dependent element and change the link to the original single cell to another separate cell.

The particular difficulty that I am facing is that, although I know where each dependent is, the reference to the source cell may be at the beginning, middle or end of the formula and may be unanchored, row / column / both may be on another sheet and therefore have a link to the sheet preceding it, etc. etc. Therefore, I cannot easily find and replace in each dependent cell due to these potential differences, plus I want to keep the original pin in each cell.

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

Answer 1

Regular expressions, or regexp, are what you are looking for, I think.

Next template

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

will match any type of "Sheet1! A1", "Sheet1! $ A $ 1", "Sheet1! $ A1", "Sheet1! A $ 1"

Explanation:

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

You can easily modify this template to fit only what you want. In particular, ([A-Z0-9] *)! (\ $ {0,1}) B (\ $ {0,1}) 1, will only match something with B ($) 1 in it ... Build a Regexp template with string manipulations and should be good.

You will need a link (Tool> Link) to "Microsoft VBScript Regular Expressions 5.5"

Try using 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})([AZ]{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

Answer 2

Why not turn off VBA and insert the source cell? Excel will then adjust cell references in all their magnificent variety.

I created several cells with links, and then used the Macro recorder to find out what might happen in the generated VBA when selecting a cell, cutting and copying content. The behavior is as expected, as follows:

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

This applies to the following sheet:

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

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