+ Reply to Thread
Results 1 to 7 of 7

How to correctly use FormulaR1C1 to sum dynamic range

  1. #1
    Registered User
    Join Date
    04-10-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    Office365
    Posts
    17

    How to correctly use FormulaR1C1 to sum dynamic range

    Hi all,

    Can someone please help with a syntax error I'm getting or offer possible a better solution?

    I am trying to sum all the entries in the last column. This a team rota/calendar and the last column and row aren't fixed. For the columns because ecause a months can have 29/30 0r 31 days and for the rows as the team members vary.

    I frist thired to use the WorkSheet Function Object, but it then sums immediately and doesn't insert the the actual formula it to the sheet which is necessary as people will add their days's off etc later.

    I use through the code LastRow and LastCol to find the last column and last row. The below doesn't work of course, but just to illustrate what I am trying to do.

    Please Login or Register  to view this content.
    I am basically trying to do for xample ...
    Please Login or Register  to view this content.
    but only ever R5 will be fixed the rest won't.

    Thanks for any tips. Much appreciated.
    Last edited by RuairiO; 05-23-2020 at 04:41 AM.

  2. #2
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,203

    Re: How to correctly use FormulaR1C1 to sum dynamic range

    Perhaps
    Please Login or Register  to view this content.
    Or perhaps
    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Registered User
    Join Date
    04-10-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    Office365
    Posts
    17

    Re: How to correctly use FormulaR1C1 to sum dynamic range

    Thanks so much. First one didn't work. I got a similar error. but the second one did. This is also a much easier solution anyway. So thanks, I didn't think of using relative references.

    I'll try and puzzle a bit more and see if I can make your first suggestion work just out of curiosity, but my problem has been solved


    Much appreciated!

  4. #4
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,203

    Re: How to correctly use FormulaR1C1 to sum dynamic range

    I find it easier to test my construction of formula strings with MsgBox or Debug.Print, before coding it to be put in a cell.
    And I see the problem, there should be a colon before the last C, as well as a closing parenthisis

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-10-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    Office365
    Posts
    17

    Re: How to correctly use FormulaR1C1 to sum dynamic range

    Thanks a lot. I think I get it now, but definately tricky.

    Can I be cheeky and ask one more question relating to similar syntax?

    I have 1 other formula:

    Please Login or Register  to view this content.

    Instead of using a fixed value 30 which could also 29 or 31 depending on the month I tried to use:

    Please Login or Register  to view this content.
    This doesn't work I tried in several ways, using debug.print with & and "" but can't seem to get it.

    Thanks a lot.

  6. #6
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,203

    Re: How to correctly use FormulaR1C1 to sum dynamic range

    Try

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    04-10-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    Office365
    Posts
    17

    Re: How to correctly use FormulaR1C1 to sum dynamic range

    It worked! thanks so much.

    When you see the solution it looks so easy, but when you're doing it yourself...)

    Big thanks!

+ 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 range fails to copy correctly to new workbook
    By RuairiO in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-13-2020, 03:26 AM
  2. [SOLVED] Dynamic name range not working correctly
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 10
    Last Post: 12-14-2013, 09:09 PM
  3. [SOLVED] Dynamic FormulaR1C1 Use with Column and Row Number Change.
    By goradiar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-23-2012, 05:33 AM
  4. Dynamic FormulaR1C1 with Column Range Count?
    By goradiar in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-19-2012, 05:50 AM
  5. ActiveCell.FormulaR1C1 Dynamic Row
    By goldenbear10 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-07-2009, 04:47 AM
  6. Dynamic FormulaR1C1 value
    By Nuraq in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-28-2005, 06:05 PM
  7. [SOLVED] Using .formular1c1 with the Range/Cells Method
    By Grumpy Aero Guy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-01-2005, 10:06 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