Consulting

Results 1 to 8 of 8

Thread: excel vba replace

Threaded View

Previous Post Previous Post   Next Post Next Post
  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

Posting Permissions

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