remove text between [ ]
Actually the example (and it was only an example) was overly
simplistic. I am trying to remove external references from a batch of
files I am creating on the fly. I want to remove external references
=[aBorders.xls]Assumptions!D$2*M2 So get rid of [aBorders.xls]
The following code works well in most instances.
Sub ExtRef_Remover()
Dim cell As Range, n As Variant
For Each cell In ActiveSheet.Cells.SpecialCells(xlFormulas)
n = Application.Find("]", cell.Formula)
If Not IsError(n) Then
'"='" Change when split name is used.
cell.Formula = "=" & Right(cell.Formula, Len(cell.Formula)
- n)
End If
Next cell
End Sub
However if the user in their infinite wisdom decide to model their
formula as such =1000 +[aBorders.xls]Assumptions!D$2*M2
The above will take the 1000 part out.
Don's answer worked for my overly simplistic example but when I got
into work today the real deal made sure it would not work.
Chad
|