Using code to edit spreadsheet formulas

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
 
J

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

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
 
A

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top