Re: Macro to change formula

[Andy Armstrong <a.armstrong@SHOTOVERJET.CO.NZ>]

Can you use find and replace under edit menu?

-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-G@PEACH.EASE.LSOFT.COM]On
Behalf Of Don Guillett
Sent: Thursday, July 24, 2003 4:55 AM
To: EXCEL-G@PEACH.EASE.LSOFT.COM
Subject: Re: Macro to change formula

I meant
Sub changeformula()
For Each c In Selection
x = Right(c.Formula, Len(c.Formula) - 1)
c.Formula = "=if(iserror(" & x & "),""""," & x & ")"
Next
End Sub


Don Guillett
SalesAid Software
Granite Shoals, TX
donaldb@281.com
----- Original Message -----
From: "Don Guillett" <donaldb@281.COM>
To: <EXCEL-G@PEACH.EASE.LSOFT.COM>
Sent: Wednesday, July 23, 2003 11:53 AM
Subject: Re: Macro to change formula


> Maybe this will help.
>
> Sub changeformula()
>  For Each c In Selection
>   x = Right(c.Formula, Len(c.Formula) - 1)
>   c.Formula = "=if(iserror(" & x & ")," & x & ")"
>  Next
> End Sub
>
> Don Guillett
> SalesAid Software
> Granite Shoals, TX
> donaldb@281.com
> ----- Original Message -----
> From: "CMM Room" <cmmroom@DDRE.DETROITDIESEL.COM>
> To: <EXCEL-G@PEACH.EASE.LSOFT.COM>
> Sent: Wednesday, July 23, 2003 11:01 AM
> Subject: Re: Macro to change formula
>
>
> > Don't know if you can use this or not, but let's say I have these
formulas:
> >
> > Cell A1, I have =SUM(B1:C1)
> > Cell A2, I have =SUM(B2:C2)
> > Cell A3, I have =SUM(B3:C3)
> > Cell A4, I have =SUM(B4:C4)
> > Cell A5, I have =SUM(B5:C5)
> >
> > but I want to change the formulas to look like this:
> >
> > Cell A1, I have =SUM(B1:D1)
> > Cell A2, I have =SUM(B2:D2)
> > Cell A3, I have =SUM(B3:D3)
> > Cell A4, I have =SUM(B4:D4)
> > Cell A5, I have =SUM(B5:D5)
> >
> > I can press the F5 key, select the Special button, select the Formulas
> > Option, click OK, then type =SUM(B1:D1) and then hold down the Ctrl key
> > while pressing enter and it will adjust all of the formulas accordingly.
> > HTH.
> >
> >
> >
> >
> >
> >
> >                                         Howard Groves
> >                                         cmmroom@ddre.detroitdiesel.com
> >
> > > -----Original Message-----
> > > From: David_McKay@EXTERNAL.MCKINSEY.COM
> > > [SMTP:David_McKay@EXTERNAL.MCKINSEY.COM]
> > > Sent: Wednesday, July 23, 2003 11:45 AM
> > > To:   EXCEL-G@PEACH.EASE.LSOFT.COM
> > > Subject:      Macro to change formula
> > >
> > > Does anybody have a little bit of code that I could use that would
help me
> > > to adjust lots of formulas at the same time (e.g. change <XXXX> to
> > > <if(iserror(XXXX),"",XXXX)> for many cells at once), either by
selection
> > > or
> > > by the entire workbook?
> > >
> > > Many thanks in advance,
> > >
> > > David
> > >
> > > +---------------------------------------------------------+
> > > This message may contain confidential and/or privileged
> > > information.  If you are not the addressee or authorized to
> > > receive this for the addressee, you must not use, copy,
> > > disclose or take any action based on this message or any
> > > information herein.  If you have received this message in
> > > error, please advise the sender immediately by reply e-mail
> > > and delete this message.  Thank you for your cooperation.
> > > +---------------------------------------------------------+
> > >
> >
> --------------------------------------------------------------------------
> > > The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
> > > international's LISTSERV(R) software.  For subscription/signoff info
> > > and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
> > >                              COPYRIGHT INFO:
> > > http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
> >
>
> --------------------------------------------------------------------------
> > The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
> > international's LISTSERV(R) software.  For subscription/signoff info
> > and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
> >                              COPYRIGHT INFO:
> > http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
> >
> >
>
> --------------------------------------------------------------------------
> The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
> international's LISTSERV(R) software.  For subscription/signoff info
> and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
>                              COPYRIGHT INFO:
> http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
>

--------------------------------------------------------------------------
The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
international's LISTSERV(R) software.  For subscription/signoff info
and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G

--------------------------------------------------------------------------
The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
international's LISTSERV(R) software.  For subscription/signoff info
and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G