Related:
- Replace naming with actual cell reference
- If cell contains date then return value ✓ - Office Software Forum
- How to replace a word in word - Guide
- Which example represents cell data with the date format applied to it? - Excel Forum
- Clear only the formatting from the selected cell (leaving the content) - Guide
- Based on the cell values in cells b77 - Excel Forum
2 responses
I think that question was self-explanatory. Unfortunately, the best I could come up with is what I have below. The limitation is that it won't convert names in other workbooks (e.g. 'AnotherWorkbook.xls!foo') to cell references, but it's still better than other solutions I've seen, including the TransitionFormEntry "trick", which only converts names from the same worksheet to cell references. The example below only converts the formula in the ActiveCell, but you can add code to loop thru all cells with formulas in the workbook.
Dim myRegExp As RegExp
Dim newEqn As String
Dim nm As Name
Set myRegExp = New RegExp
With myRegExp
.Global = True
.IgnoreCase = False
End With
newEqn = ActiveCell.Formula
For Each nm In ActiveWorkbook.Names
With nm
If InStr(1, .RefersTo, "#REF!") = 0 Then
myRegExp.Pattern = "([\=\+\-\*/\^\,\(\)<>\[\]])(" & .Name & ")([\+\-\*/\^\,\(\)<>\[\]]?)"
newEqn = myRegExp.Replace(newEqn, "$1" & Replace(Right(.RefersTo, Len(.RefersTo) - 1), "$", "$$") & "$3")
End If
End With
Next nm
sharpman
Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
183
Aug 25, 2009 at 01:28 PM
Aug 25, 2009 at 01:28 PM
example of data and what you are trying to do?
