Using code to edit spreadsheet formulas
 

I need to edit formulas in about 10,000 cells. The formulas are all
basically the same: they differ only in that two cell references in
each formula are unique to that cell. The basic format of each formula
is '=Function1(CellRef1,CellRef2,"")', where Function1 is a User
Defined Function. Also, the edits I need to make are simplistic: I
need to change each 'Function1' to 'Function2', where Function2 is
also a User Defined Function.

Is there a way to use XCL 2003 or XCL 2010 Code to do my edits?
TIA,
JingleRock

Using code to edit spreadsheet formulas
 

A definition/explanation of what "XCL" code is would be helpful.
Using VBA code, the InStr function, Mid statement and the range Formula property might be your
tools.
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)




"JingleRock"
wrote in message
...
I need to edit formulas in about 10,000 cells. The formulas are all
basically the same: they differ only in that two cell references in
each formula are unique to that cell. The basic format of each formula
is '=Function1(CellRef1,CellRef2,"")', where Function1 is a User
Defined Function. Also, the edits I need to make are simplistic: I
need to change each 'Function1' to 'Function2', where Function2 is
also a User Defined Function.

Is there a way to use XCL 2003 or XCL 2010 Code to do my edits?
TIA,
JingleRock

Using code to edit spreadsheet formulas
 

On 29/07/2011 03:47, JingleRock wrote:
I need to edit formulas in about 10,000 cells. The formulas are all
basically the same: they differ only in that two cell references in
each formula are unique to that cell. The basic format of each formula
is '=Function1(CellRef1,CellRef2,"")', where Function1 is a User
Defined Function. Also, the edits I need to make are simplistic: I
need to change each 'Function1' to 'Function2', where Function2 is
also a User Defined Function.

Is there a way to use XCL 2003 or XCL 2010 Code to do my edits?
TIA,
JingleRock

For something as simple minded as that you can probably get away with
using the global search and replace on the appropriate cells range.

Cells.Replace What:="Fred", Replacement:="Jim", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Swaps every "Fred" for "Jim".

Regards,
Martin Brown

Using code to edit spreadsheet formulas
 

EditReplace will do the trick.


Gord Dibben Microsoft Excel MVP

On Thu, 28 Jul 2011 19:47:31 -0700 (PDT), JingleRock
wrote:

I need to edit formulas in about 10,000 cells. The formulas are all
basically the same: they differ only in that two cell references in
each formula are unique to that cell. The basic format of each formula
is '=Function1(CellRef1,CellRef2,"")', where Function1 is a User
Defined Function. Also, the edits I need to make are simplistic: I
need to change each 'Function1' to 'Function2', where Function2 is
also a User Defined Function.

Is there a way to use XCL 2003 or XCL 2010 Code to do my edits?
TIA,
JingleRock

Using code to edit spreadsheet formulas
 

JingleRock,

Sounds similar to something I just went through !

http://groups.google.com/group/micro...12d91d683ad81e



- Ronald K.

Using code to edit spreadsheet formulas
 

On Jul 29, 12:52*am, Martin Brown
wrote:
On 29/07/2011 03:47, JingleRock wrote:

I need to edit formulas in about 10,000 cells. The formulas are all
basically the same: *they differ only in that two cell references in
each formula are unique to that cell. The basic format of each formula
is '=Function1(CellRef1,CellRef2,"")', where Function1 is a User
Defined Function. Also, the edits I need to make are simplistic: *I
need to change each 'Function1' to 'Function2', where Function2 is
also a User Defined Function.

Is there a way to use XCL 2003 or XCL 2010 Code to do my edits?
TIA,
JingleRock

For something as simple minded as that you can probably get away with
using the global search and replace on the appropriate cells range.

Cells.Replace What:="Fred", Replacement:="Jim", LookAt:=xlPart, _
* * * * SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
* * * * ReplaceFormat:=False

Swaps every "Fred" for "Jim".

Regards,
Martin Brown

If you only doing this modification one time, you can also use find
and replace (ctrl h). I often use that to change cell functions.