Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: copy formule

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a sheet with in column B1:B5000 stock prices.In each of these cells there is the same formule.Now I want a macro that automaticly makes a copy from all these formules from column B to column C and the formules in column B deleted.
    Have anyone such a macro?
    Thanks

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How's this:

    Sub theCutter()
    [b1:b5000].Cut Destination:=[c1]
    End Sub

    Hope this helps.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-19 12:52, NateO wrote:
    How's this:

    Sub theCutter()
    [b1:b5000].Cut Destination:=[c1]
    End Sub

    Hope this helps.

    Thanks,it works, but the question was that only the formules must be deleted.The values
    must be intact.
    Have you a solution on this?


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    You could give this a try

    Range("B1:B5000").Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("B1:B5000").Select
    Application.CutCopyMode = False
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    Selection.ClearContents
    End Sub


    Or, if you mean you also want to convert the formulas in column B to values as well, then:

    Range("B1:B5000").Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("B1:B5000").Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    End Sub


    Regards
    Derek


    [ This Message was edited by: Derek on 2002-03-20 01:30 ]

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi


    Try this method

    Sub Doit()
    Range("C1:C5000") = Range("B1:B5000").Formula
    Range("B1:B5000") = Range("B1:B5000").Value
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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