Consulting

Results 1 to 8 of 8

Thread: excel vba replace

  1. #1
    VBAX Newbie
    Joined
    Mar 2018
    Posts
    4
    Location

    excel vba replace

    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
    Attached Images Attached Images
    Last edited by Aussiebear; 03-20-2018 at 02:03 PM. Reason: Added code tags

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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 & "=", ''''''''''''''
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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

  4. #4
    VBAX Newbie
    Joined
    Mar 2018
    Posts
    4
    Location
    that's the next project since i'm very new at vba but thanks anyways

  5. #5
    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.

  6. #6
    VBAX Newbie
    Joined
    Mar 2018
    Posts
    4
    Location

    example

    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!
    Attached Files Attached Files

  7. #7
    VBAX Newbie
    Joined
    Mar 2018
    Posts
    4
    Location
    thank you four your help, however i don't seem to get it quit right...

  8. #8
    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);dba se!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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •