excel vba replace

[kds]

hello

I made about 9000 formulas in one sheet and each formula contains index, concatenate, if.wrong,... all the formulas refer to a database and everything works fine. There are 2 other sheets with the same amount of formulas. The file is 30 Mb.

To make the file smiler, i replaced in every formula = with "= (so it would be considered as text, the file becomes a lot smaller). I copied all this 1000 cells below so i can copy-paste it in the range i want using a macro. Then, also using a macro: every "= must be changed in =, followed by a copy-paste (only value)
However, when i do this and the macro is recording: excel does what i want it to do. But when i just want to run this macro it doesn't. Result: it copie-paste all the formulas beginning with "= (see attachement)

Can somebody help me? Thanks!



Sub B_Lokalen()
'
' B_Lokalen Macro
'


'
Range("A1:EI527").Select
Selection.ClearContents
Range("A1000:EI1526").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1:EI527").Select
Selection.Replace What:="""=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1:EI527").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub

[SamT]

I would just save the book as an *.xlsb and see what the size was

I can never get Quote marks right in a String containing Quote marks so I use

Const DQ as String = """
'
'
Selection.Replace What:=DQ & "=", ''''''''''''''

[offthelip]

Why not write a macro to do the "index, concatenate if.wrong" bit and really reduce the size of your worksheet and the time it takes to recalculate

[kds]

that's the next project since i'm very new at vba but thanks anyways

[rlv]

When I run your code it appears to work in that it correctly replaces all instances of "= with =

If you are still having trouble it might be better to post an example workbook to illustrate the problem.

[kds]

Here is an example workbook, i removed some things to get it under 1 mb, the layout looks very basic.

When you go to the sheet "dbase", you see cels J6:K6 in orange, before running the macro you will have to copy this formulas till J2203:L2203. This formulas are necessery for other formulas (index,...)

In the sheet 'LOK', you can run the macro B_Lokalen.

Thanks for taking time looking at it!

[kds]

thank you four your help, however i don't seem to get it quit right...

[rlv]

I think what's going on is that if excel can't intepret your formula as a formula it puts the double quote marks back after you've replaced them. When I use my own (valid) formulas it works. When I use yours it does not. The Dutch excel function names may be throwing me off a bit (ALS vs. IF) but for whatever reason you have the function calls for als and als.fout using semicolons to separate the parameters instead of commas.

"=als(Blad1!D6=0;Blad1!D7;data!G2)

"=als.FOUT(INDEX(dbase!E6:E9005;VERGELIJKEN(TEKST.SAMENVOEGEN(A1;A6;B6);dbas e!L6:L9005;0));" ")


If I add code to first replace the semicolons with commas, then the 2nd replace will successfully replace the leading quote marks and excel sees the formula as a formula, at least on my version of excel (2010).



Selection.Replace What:=";", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False



Sub B_Lokalen()
'
' B_Lokalen Macro
'
Range("A1:EI527").Select
Selection.ClearContents
Range("A1000:EI1526").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1:EI527").Select
Selection.Replace What:=";", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Range("A1:EI527").Select
Selection.Replace What:="""=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1:EI527").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
'
End Sub