+ Reply to Thread
Results 1 to 7 of 7

Adding to Variables Within a .FormulaR1C1 String

Hybrid View

Gard5096 Adding to Variables Within a... 01-08-2013, 02:58 PM
Norie Re: Adding to Variables... 01-08-2013, 03:26 PM
Gard5096 Re: Adding to Variables... 01-08-2013, 03:34 PM
Norie Re: Adding to Variables... 01-08-2013, 03:45 PM
Gard5096 Re: Adding to Variables... 01-08-2013, 04:37 PM
Norie Re: Adding to Variables... 01-08-2013, 04:44 PM
Gard5096 Re: Adding to Variables... 01-08-2013, 05:32 PM
  1. #1
    Forum Contributor
    Join Date
    09-06-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    225

    Exclamation Adding to Variables Within a .FormulaR1C1 String

    Each variable is a column that holds dates. I am trying to incorporate additional days onto the referenced date, as you can see with the "+15". It is giving me an error because I believe my syntax is off. What can I do to make it use Variable9 + 15 days, in the equation?

    Thank you.

    .FormulaR1C1 = "=IF(AND(RC" & variable9 & " > RC" & variable10 & ", RC" & variable4 & " > RC" & variable9 & "+15" & ", TRUE, FALSE)"

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: Adding to Variables Within a .FormulaR1C1 String

    Try closing the AND.
    .FormulaR1C1 = "=IF(AND(RC" & variable9 & " > RC" & variable10 & ", RC" & variable4 & " > RC" & variable9 & "+15), TRUE, FALSE)"
    Or without the IF.
    .FormulaR1C1 = "=AND(RC" & variable9 & " > RC" & variable10 & ", RC" & variable4 & " > RC" & variable9 & "+15)"
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    09-06-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    225

    Re: Adding to Variables Within a .FormulaR1C1 String

    Thanks Norie, the first solution solved my problem. (cant believe I missed that).

    I have a follow up question: If I wanted the result when TRUE to be a seperate function (using a variable), how would I impliment that?

    Here's my first thought:
    .FormulaR1C1 = "=IF(AND(RC" & variable9 & " > RC" & variable10 & ", RC" & variable4 & " > RC" & variable9 & "+15), "" & variable9 & ""+30, FALSE)"

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: Adding to Variables Within a .FormulaR1C1 String

    If variable9 is numeric you don't need to double up the quotes.

  5. #5
    Forum Contributor
    Join Date
    09-06-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    225

    Re: Adding to Variables Within a .FormulaR1C1 String

    Variable9 is a column that only has dates. I changed the syntax to the following, in order for it to use the corresponding row. It is giving me a problem response of: 1/30/1900. Do I have to tell it that the variable is a date and that I am trying to add 30 days?


    .FormulaR1C1 = "=IF(AND(RC" & variable9 & " > RC" & variable10 & ", RC" & variable4 & " > RC" & variable9 & "+15), RC" & variable9 & "+30, FALSE)"

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: Adding to Variables Within a .FormulaR1C1 String

    What should the answer be?

    If the result is 1/30/1900 then it probably means the cell with the formula is formatted as a date and the actual result is 30.

  7. #7
    Forum Contributor
    Join Date
    09-06-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    225

    Re: Adding to Variables Within a .FormulaR1C1 String

    I'm all set. The cell was blank, so it was adding 30 days to a blank cell. Thanks for your assitance.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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