+ Reply to Thread
Results 1 to 5 of 5

Concatenating literal, with ActiveCell and FormulaR1C1

  1. #1
    Registered User
    Join Date
    06-26-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2003
    Posts
    40

    Concatenating literal, with ActiveCell and FormulaR1C1

    I can't find a way to do this. If I don't use the literal "R", it works fine, but I need that R. Any ideas? Here's my line:

    Please Login or Register  to view this content.
    I want to concatenate an "R" at the end. How to do it?

    If I just try to add

    & "R"

    I get "Application-defined or object-defined error. "
    Last edited by Paul; 06-29-2011 at 05:40 PM. Reason: Added code tags.

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Concatenating literal, with ActiveCell and FormulaR1C1

    Hi LydiasDad, try adding a few more quotation marks, like so:
    Please Login or Register  to view this content.
    Also, be sure to wrap code within [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags.

  3. #3
    Registered User
    Join Date
    06-26-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2003
    Posts
    40

    Re: Concatenating literal, with ActiveCell and FormulaR1C1

    OK, that worked. Thanks. That was the first step. I thought I could use that example to get the whole thing working, but no luck. Here's what I really want (calculating a remainder).

    ActiveCell.FormulaR1C1 = "=INT( R[-2]C/R[-1]C)&"" R"" &(R[-2]C - R[-1]C * INT ( R[-2]C / R[-1]C ))"

    So, if R[-2]C is 4, and R[-1]C is 3, I should get "1 R1" (one, remainder one) as the answer. But I still get that same error.

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Concatenating literal, with ActiveCell and FormulaR1C1

    Try removing all spaces from your formula except where you want a literal space before the R. A good way to see what the formula should be is to record a macro while typing the formula (in A1 style) into a cell.

    Please Login or Register  to view this content.
    Start the macro recorder, then in C3 type:

    =INT(C1/C2)&" R"&(C1-C2*INT(C1/C2))

    Stop the macro recorder and open the VB Editor. View the code created in the module and you should see the formula in R1C1 notation.

  5. #5
    Registered User
    Join Date
    06-26-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2003
    Posts
    40

    Re: Concatenating literal, with ActiveCell and FormulaR1C1

    Oh, you're right. It was the stupid spaces. Didn't know that mattered. Thanks a ton.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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