+ Reply to Thread
Results 1 to 14 of 14

VBA for copying formula

  1. #1
    Registered User
    Join Date
    05-05-2020
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2019 Professional Plus
    Posts
    9

    VBA for copying formula

    Hi,
    I have 2 problems, that would appear to be utter-basic, but I cannot find the answer anywhere, so apologies in advance for if this is too simple for this forum.

    Problem 1:
    What i'd like to do is this: copy a formula with a formula.
    Example: Let's say there are these cells:
    A1: 2
    A2: 1
    A3: =A1+A2
    A4: .......

    What i need is a formula that copies the formula in A3. NOT the value.
    The reason for not wanting the value but the formula is that I want to substitute something in the formula, therefore i need a formula that copies the formula, so that I can then do a substitute on it.
    The reason I do not want to manually copy-paste the formula, is that my data and formulas will change over time. E.g. if A3 becomes '=A1-A2' instead of '=A1+A2' in the future, I want that change to be reflected in the formula in A.

    All I can find is the regular '=A3' formula, which copies the value in A3, and guides online for manual copy-pasting. Any help would be much appreciated.

    problem2:
    Problem 2 is a lot like problem one, except I need a formula that does what a manual copy-paste does, wherein it changes the relative references in the formula. So e.g. when I would copy-paste cell A3 to cell A4, it would change the formula to '=A2+A3', because the references are relative and without the $-sign. I need a formula that performs this copy-paste.
    Same reasons as above for not manually doing this: the original formula might change, and when it does, i want this other cell to change with it.
    As above, any help would be much appreciated.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,044

    Re: formula for copying formula

    I don't understand the first problem. If you change the formula in A3, then the result in A3 will change accordingly. How would replicating the formula elsewhere help in any way? You are going to be more explicit about what you are imagining here.

    If you want a column of formulae to change if you change the first formula in the column, then you need to create a structured Excel table, which allows this to happen (Insert | Table).
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    05-05-2020
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2019 Professional Plus
    Posts
    9

    Re: formula for copying formula

    Hi Ali, thanks for the reply, I looked into the structured Excel table, but this is not what I need.
    The reason for not wanting a reference to the value, but to the formula, as I noted above, is that I want to substitute something part of the formula. Therefore i need a formula that refers to the formula, so that I can then do a substitute on it. For problem 2, the practical example of me needing it is:

    A1: 1
    A2: 2
    A3: =SUM(A1:A2)
    B1: 2
    B2: 3
    B3: =SUM(B1:B2), BUT if A3 is changed to '=A1/A2' , I need B3 to change to '=B1/B2'. No way to do this manually in my sheet because there is too much data.

    I hope that someone can help!
    Last edited by Rik1234; 08-08-2020 at 04:12 PM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,044

    Re: formula for copying formula

    Why canít you just drag copy across? How much data are we talking about?

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,044

    Re: formula for copying formula

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new I will provide the link for you this time: http://www.reddit.com/r/excel/commen...ying_formulae/)

  6. #6
    Registered User
    Join Date
    05-05-2020
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2019 Professional Plus
    Posts
    9

    Re: formula for copying formula

    Hi aligw, alright apologies, and good to know.
    The answer to your questions about why I cannot just drag copy accross is, as noted above, that if the formula changes, I need the cell to which the formula is copied, also to change with it. E.g.:
    A3: =SUM(A1:A2)
    and the cell to which A3 is copy-pasted is B3. B3 then becomes:
    B3: =SUM(B1:B2)
    However, if A3 is changed to '=A1/A2' , I need B3 to change to automatically change to '=B1/B2'.
    Or when it becomes '=A1-A2', I need B3 to automatically change to '=B1-B2'.
    I hope this makes it clearer.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,044

    Re: formula for copying formula

    The only way you'll be able to do this is via VBA. Shall I move the thread for you to that section?

  8. #8
    Registered User
    Join Date
    05-05-2020
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2019 Professional Plus
    Posts
    9

    Re: formula for copying formula

    yes, please, that would be great.

  9. #9
    Forum Contributor
    Join Date
    11-28-2013
    Location
    KSA
    MS-Off Ver
    Office 2019 version 16.0.13130
    Posts
    901

    Re: formula for copying formula

    Dear Rik1234
    please kindly check attached file with VBA code that will apply formula in B3 to other cell (in my example B3:G3 with green colour)
    Please Login or Register  to view this content.
    you can change cell contain formula will be change, in my example I put it in B3 with yellow colour
    you can change range B3:G3 to any range you need to apply formula in your original data
    so when you change B3 formula, and push apply change button, it will apply formula to B3:G3 in green colour
    this is a simple way, sure there is another more professional solution you can get from expert here in forum
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    05-05-2020
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2019 Professional Plus
    Posts
    9

    Re: formula for copying formula

    this is great, precisely what i need, thanks mazan2010! I have never really dived into VBAs, this means I'm going to have to. I think it will be a good change
    best,
    Rik
    Last edited by Rik1234; 08-11-2020 at 04:51 PM.

  11. #11
    Registered User
    Join Date
    05-05-2020
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2019 Professional Plus
    Posts
    9

    Re: formula for copying formula

    ps are you all volunteers, and if so, is there any way to donate?

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,044

    Re: VBA for copying formula

    Yes and no. Yes, we are all volunteers, and no, there are no donations required. We have the Commercial Services section, but thatís really when someone wants an entire brief realising. There is no need for you to pay anything, and offering anyone payment would be against the rules and the spirit of the place. Your gratitude is enough, and you can award reputation points if you wish.

  13. #13
    Forum Contributor
    Join Date
    11-28-2013
    Location
    KSA
    MS-Off Ver
    Office 2019 version 16.0.13130
    Posts
    901

    Re: formula for copying formula

    You are welcome dear Rik1234
    regarding your question in post 11, maybe one supervisor here can reply you about it

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,044

    Re: VBA for copying formula

    Iíve already given a response to it in post #12.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copying an array formula down keeps copying data in top row
    By blackrosepetals9169 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-10-2018, 05:15 AM
  2. [SOLVED] Copying formula result to new cell, without copying formula itself
    By nappyjim1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-19-2016, 02:24 AM
  3. Replies: 6
    Last Post: 04-25-2014, 10:31 AM
  4. Copying and pasting formula into specific range based on a formula value
    By akwishestofish in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-09-2012, 01:12 PM
  5. Replies: 1
    Last Post: 04-30-2012, 10:01 PM
  6. Replies: 1
    Last Post: 03-04-2012, 12:03 AM
  7. Copying formula to new worksheet: how to stop formula linking to original source?
    By test-card girl in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-30-2007, 07:32 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1