Prev: If Vlookup Question
Next: Sorting multiple columns that have similar data
From: Greg on 11 May 2010 14:30
hi,

is there a way to reset (not just delete) the named ranges back to the
original references so the formulas would still work?

Using Excel 2007
thank you

--
______
Regards,
Greg
From: Chip Pearson on 11 May 2010 15:09
No, you cannot restore a name back to some previous reference. Once
you change the location to which a name refers, you can't
automatically go back to some prior value.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com


On Tue, 11 May 2010 11:30:02 -0700, Greg
<Greg(a)discussions.microsoft.com> wrote:

>hi,
>
>is there a way to reset (not just delete) the named ranges back to the
>original references so the formulas would still work?
>
>Using Excel 2007
>thank you
From: Gary''s Student on 11 May 2010 15:22
This little macro will replace each Named Range in each formula with the
orgiinal range:

Sub FixNames()
Dim n As Name, nn As String, naddy As String
Dim r As Range
For Each n In ActiveWorkbook.Names
nn = n.Name
naddy = Right(n.RefersTo, Len(n.RefersTo) - 1)
For Each r In Cells.SpecialCells(xlCellTypeFormulas)
r.Formula = Replace(r.Formula, nn, naddy)
Next
Next
End Sub

So if alpha is:
A1:A3
and beta is:
B1:B3

formulas like:
=SUM(alpha)
will become:
=SUM(Sheet1!$A$1:$A$3)
--
Gary''s Student - gsnu201002


"Greg" wrote:

> hi,
>
> is there a way to reset (not just delete) the named ranges back to the
> original references so the formulas would still work?
>
> Using Excel 2007
> thank you
>
> --
> ______
> Regards,
> Greg
From: Greg on 11 May 2010 17:38
does not work. I think there is some error with the below loop. Excel is
trying to open some file to update values. The ranges do not have external
references though

For Each r In Cells.SpecialCells(xlCellTypeFormulas)
r.Formula = Replace(r.Formula, nn, naddy)
Next




"Gary''s Student" wrote:

> This little macro will replace each Named Range in each formula with the
> orgiinal range:
>
> Sub FixNames()
> Dim n As Name, nn As String, naddy As String
> Dim r As Range
> For Each n In ActiveWorkbook.Names
> nn = n.Name
> naddy = Right(n.RefersTo, Len(n.RefersTo) - 1)
> For Each r In Cells.SpecialCells(xlCellTypeFormulas)
> r.Formula = Replace(r.Formula, nn, naddy)
> Next
> Next
> End Sub
>
> So if alpha is:
> A1:A3
> and beta is:
> B1:B3
>
> formulas like:
> =SUM(alpha)
> will become:
> =SUM(Sheet1!$A$1:$A$3)
> --
> Gary''s Student - gsnu201002
>
>
> "Greg" wrote:
>
> > hi,
> >
> > is there a way to reset (not just delete) the named ranges back to the
> > original references so the formulas would still work?
> >
> > Using Excel 2007
> > thank you
> >
> > --
> > ______
> > Regards,
> > Greg
 | 
Pages: 1
Prev: If Vlookup Question
Next: Sorting multiple columns that have similar data