Results 1 to 11 of 11

20070618, 13:24 #1
 Join Date
 Feb 2007
 Location
 Vienna, Wien, Austria
 Posts
 126
 Thanks
 0
 Thanked 0 Times in 0 Posts
Substitute Formulas with code (Excel 2003)
hello,
i

20070618, 13:28 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
Re: Substitute Formulas with code (Excel 2003)
Can I ask why you want to do this? VBA is slower that builtin formulas, so your workbook performance will suffer.
Regards,
Rory
Microsoft MVP  Excel

20070618, 13:30 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Substitute Formulas with code (Excel 2003)
It's possible to change formulas using Edit  Replace or using VBA code, but it's not clear to me what you want to replace, and what you want to replace it with.

20070618, 13:34 #4
 Join Date
 Feb 2007
 Location
 Vienna, Wien, Austria
 Posts
 126
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Substitute Formulas with code (Excel 2003)
the problem is not the performance but the size of my files.
In the Mastermap i have the resourcepool of my company with workers calendars, the whole projectplanning and now i should
make a worksheet, where i make an offer for the estimated amount of mandays (from the deadline) to the end.  so I want to replace it
with single values (size is up to 50 MB now...).
Stefan

20070618, 13:37 #5
 Join Date
 Feb 2007
 Location
 Vienna, Wien, Austria
 Posts
 126
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Substitute Formulas with code (Excel 2003)
well, i want to replace everything in the workseet ETC count with values; the base sheet is Data ETCs.

20070618, 13:39 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Substitute Formulas with code (Excel 2003)
If you want to replace formulas with their values, you can copy a range, then paste special with the Values option. If you want to replace formulas with their values in code, you can use something like
Selection.Value = Selection.Value
or
With Worksheets("ETC count").UsedRange
.Value = .Value
End With

20070618, 13:49 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Substitute Formulas with code (Excel 2003)
What I usually do in cases like this would be to copy the top row of formulas to an empty row (eg in your example copy row5 to row 1. This preserves a copy of all the formulas.
Then select all the A5:Kwhatever and copy, pastespecial values. this will convert all the formulas to the values and reduce the file size and speed up performance. If you ever need to regenerate the data or need to extract new values, you can copy row 1 to the appropriate rows, then copy pastespecial  values again...
Steve

20070619, 05:55 #8
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Substitute Formulas with code (Excel 2003)
Well, that is what *I* say too, most of the time. But Charles Williams (http://www.decisionmodels.com, not sure if he has put an article on his site though) has proven that in fact UDF's can be faster than some builtin formulas.
Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20070619, 10:35 #9
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
Re: Substitute Formulas with code (Excel 2003)
There is an exception to every rule! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> (including this one)
These days I tend to go for legibility and adaptability rather than outright performance in most cases (except for some of my larger workups) and UDFs can be much better in that regard! I would be interested to see that article though if you know where I can find it (haven't been on the DM site for a while but will check it out shortly to see if it's there)Regards,
Rory
Microsoft MVP  Excel

20070619, 20:07 #10
 Join Date
 Feb 2007
 Location
 Vienna, Wien, Austria
 Posts
 126
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Substitute Formulas with code (Excel 2003)
Hi Hans,
thanks for the answer, but i was looking for n other solution. in the meantime i made a workaround on it.
My problem is, that some colleagues of mine should do my work whenever i am not at work. So it should
be a more or less very automatic workaround with few work by hand...
thanks again,
stef.

20070619, 20:23 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Substitute Formulas with code (Excel 2003)
I suggested two VBA solutions in my reply. You could use these in a macro.