+ Reply to Thread
Results 1 to 11 of 11

dynamic formula with VBA

  1. #1
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    dynamic formula with VBA

    Hi all,

    How can I write below formula to make it dynamic when inserting across the array?

    Please Login or Register  to view this content.
    e.g. formula in cell E1 should look like this:

    Please Login or Register  to view this content.
    e.g. formula in F10:

    Please Login or Register  to view this content.
    etc...

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: dynamic formula with VBA

    Please Login or Register  to view this content.
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: dynamic formula with VBA

    Yup, that also works! Learned something new again. Thanks!

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: dynamic formula with VBA

    This also works
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: dynamic formula with VBA

    Great! what I was initially looking for. Is there a way that it always starts from let's say column A and then moves one to the right? The range keeps shifting and I don't always want to change the
    Please Login or Register  to view this content.
    part and replace the -1 by let's say -65...

    e.g.

    The formula in E1 is technically just:

    Please Login or Register  to view this content.
    Then in F1 it would be:

    Please Login or Register  to view this content.
    it is basically always starting from 4 and then +1 for the next column...

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: dynamic formula with VBA

    Is there a way that it always starts from let's say column A and then moves one to the right?
    not sure why you would to do that in this formula but in R1C1 notation...no, as its relative to the cell you are inputting formula in
    so if you put the same formula in a difference column...it will yield a different formula

  7. #7
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: dynamic formula with VBA

    Because the range the formula is applied to keeps changing now and then, let's say from Range("E1:R250") to Range("AA1:AN250") but the column number that should be returned in my INDEX formula always starts at 4!

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,886

    Re: dynamic formula with VBA

    Your original code looks fine to me. What was the problem with it exactly?

  9. #9
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: dynamic formula with VBA

    ... that's embarrassing! I literally didn't try the code before, as I was 100% sure that it would just insert an identical formula in every single cell of the array without moving columns or rows.

    Thanks both for helping out!!

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,886

    Re: dynamic formula with VBA



    It also fixes your later problem.

  11. #11
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: dynamic formula with VBA

    Exactly!!

+ 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] dynamic chart values formula with dynamic starting point
    By Kramxel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-24-2015, 03:30 AM
  2. [SOLVED] Dynamic formula, (copy from dynamic formula and ignore 0)
    By Kartoffelmos in forum Excel Formulas & Functions
    Replies: 42
    Last Post: 10-20-2014, 09:14 AM
  3. VBA - Updating Dynamic Line Chart & Inserting Dynamic Formula
    By bruno08102013 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-09-2014, 10:10 AM
  4. Replies: 0
    Last Post: 02-28-2006, 09:10 PM
  5. Replies: 0
    Last Post: 02-28-2006, 09:10 PM
  6. Replies: 0
    Last Post: 02-28-2006, 09:10 PM
  7. Replies: 2
    Last Post: 02-02-2006, 04:10 PM

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