+ Reply to Thread
Results 1 to 3 of 3

Using variables in a R1C1 statement

  1. #1
    Registered User
    Join Date
    07-15-2018
    Location
    wiltshire, england
    MS-Off Ver
    2010
    Posts
    2

    Using variables in a R1C1 statement

    I want to sum a column of data starting from the cell above up to n cells above. n is given in a variable called custnum. This doesn't work:

    ActiveCell.FormulaR1C1 = "=SUM(r[-custnum]C:R[-1]C)"

    Hope someone can help!
    Thanks
    Sue

  2. #2
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Using variables in a R1C1 statement

    Try using -

    ActiveCell.FormulaR1C1 = "=SUM(R[-" & custnum & "]C:R[-1]C)" where custnum variable would specify the number of cells to be summed above.

    The variable custnum should not be within double quotes as it would then be considered as a string and not a variable.
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh

  3. #3
    Registered User
    Join Date
    07-15-2018
    Location
    wiltshire, england
    MS-Off Ver
    2010
    Posts
    2

    Re: Using variables in a R1C1 statement

    Many thanks Sourab, it worked fine. Sometimes I think VBA isn't intuitive!
    Best wishes
    Sue

+ 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. Insert variables on R1C1 Formula
    By ccasio04 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-27-2017, 03:34 PM
  2. Using Defined Variables in R1C1 Formula
    By nobodyukno in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-16-2017, 05:03 PM
  3. Use of variables calculated in a R1C1 formula in VBA
    By Sousoudef in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2013, 11:06 AM
  4. R1C1 with variables and the Rank Function.
    By NicholasL in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-01-2013, 11:24 PM
  5. Using named variables with R1C1 formatting?
    By jenlookxl in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-14-2012, 07:44 PM
  6. [SOLVED] Trouble Including Variables in R1C1 VBA Code...
    By michaeljoeyeager in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-26-2012, 03:46 PM
  7. Replies: 3
    Last Post: 04-11-2006, 08:10 PM

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