Using code to edit spreadsheet formulas

  • Thread starter Thread starter JingleRock
  • Start date Start date
J

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
 
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...
 
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
Click to expand...

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
 
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
Click to expand...

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.
 
Back
Top