Re: Macro to change formula

[Robert McCurdy <robertwm@PARADISE.NET.NZ>]

Ok here is a quick trick.

Select A1:A5, click in the formula bar and you should see a blue border.
Use the fill handle (bottom right) and drag it across to D1, Ctrl + Enter to
update the rest of the formulae.  The mouse pointer will move the border
proportionally.

Now I read you need a VBA to do this. -
- Based on CMM Room's examples.

Sub ChangeFor()
[D1:D5].Cut
[C1:C5].Insert shift:=xlToRight
End Sub

It is rarely, that vba is used to change formulae, more often it is used to
write them to a location or to extract a value that is too complex for a
formula to do.

Regards Robert

----- Original Message -----
From: "CMM Room" <cmmroom@DDRE.DETROITDIESEL.COM>
To: <EXCEL-G@PEACH.EASE.LSOFT.COM>
Sent: Thursday, July 24, 2003 4: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

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