Re: Macro to change formula

[Alesa Ward <award@CMFOODS.COM>]

FWIW : I use a variation of this for VLOOKUPs, where the selected cells
hold the column number of the table to look in.

Something like:

Sub CngVlookupColumn()
For Each c In Selection
x = Right(c.Formula, Len(c.Formula))
c.Formula = "=VLOOKUP(MyDate,MyRange," & x & ",FALSE)"

Next


End Sub




Thank you,
Alesa Ward
Cal-Maine Foods, Inc.
3320 Woodrow Wilson Ave
Jackson, MS  39209

601-948-6813


-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-G@PEACH.EASE.LSOFT.COM]
On Behalf Of Don Guillett
Sent: Wednesday, July 23, 2003 11: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



**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This email message has been swept by
MAILsweeper for the presence of computer viruses.


**********************************************************************

--------------------------------------------------------------------------
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