Using code to edit spreadsheet formulas

[JingleRock]

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

 

[Jim Cone]

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" <[email protected]>
wrote in message
news:936479fb-7c26-4c9d-abcd-8cebbde74ea3@e35g2000yqc.googlegroups.com...

 

[Martin Brown]

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

 

[Gord]

Edit>Replace will do the trick.


Gord Dibben Microsoft Excel MVP

 

[kittronald]

JingleRock,

Sounds similar to something I just went through !

http://groups.google.com/group/micr...nctions/browse_thread/thread/6712d91d683ad81e



- Ronald K.

 

[Andrew]

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.