the code just replaces the equal sign in all the formulas in the worksheet
with an equal sign. This causes Excel to re-evaluate the entry and it then
treats it as a formula (without changing any formats). Formulas that were
already working should continue to work.

--
Regards,
Tom Ogilvy

"CB Hamlyn" wrote in message
...
In this particular case I was simply able to change the format of the
linked
cells. Out of curiosity, what exactly does all that code you just posted
actually do? If you don't have the time to do a quick breakdown, that's
fine too. As I said, I'll just change the format of the linked cells.

the thing that kills me is having the format carry over is brilliant for
dates and currency and what not, but it seems like a no brainer that doing
it for Text would void the original formula.

I'm using Excel 2000 and I wonder if this problem has been addressed in
more
recent releases.

Anyway, thank you very much for the reply Tom.

CB Hamlyn

"Tom Ogilvy" wrote in message
...
No. Not that I am aware of

At the end of your macro or at least after putting in the formulas you
can
add:

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

--
Regards,
Tom Ogilvy


"CB Hamlyn" wrote in message
...
Ok, I figured this out on my own... sort of. I actually figured it out
as
I
was preparing to send out the original post, but I posted anyway in
case
someone else runs into this. I couldn't find anything in Google about
this
so I hope this helps others at least.

It seems VBA is applying the Formula's linked cell's format. So in
this
case CabinetInfo!G2 is formatted as General and CabinetInfo!H2 is set
as
Text. I can see the applied benefit of this, however it seems like
this
particular case I'm having makes it more a hindrance than a feature.

Is there a way to turn it off?

Thank you
CB Hamlyn

"CB Hamlyn" wrote in message
...
This is NOT a priority as I've worked around it, but I'm really
curious
if
there's a way to make VBA stop doing this...

I have the following code in a "Repair Formulas" Sub...


-------------------------------------------------------------------------
-
-----
Range("AA103").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$G$2"
Range("AA104").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$H$2"
Range("AA105").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$I$2"
Range("AA106").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$J$2"
Range("AA107").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$K$2"
Range("AA108").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$L$2"
Range("AA109").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$M$2"
Range("AA110").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$N$2"


-------------------------------------------------------------------------
-
------

All of the cells I'm repairing are formatted as General. For
whatever
reason when the code runs to repair Range AA104 it changes that cells
format to Text... which of course makes the formula show up instead
of
the
result, which crashes my program when the user tries to get the data
from
that cell to show up on a userform. If I do this:
--------------------------------------------------------------
Range("AA101:AG148").NumberFormat = "General"
--------------------------------------------------------------
At the end of the repair Sub it does in fact set the entire range to
General. If I have this code at the top of the Sub it changes AA104
to
Text as soon as it executes that line in the code.

This is also happening with several other seemingly random cells on
this
same worksheet (which is why my NumberFormat Range is as big as it
is.

Any ideas why this is happening and what I might do to correct it?...
other than the work around I'm already using.

Thank you
CB Hamlyn