+ Reply to Thread
Results 1 to 4 of 4

Using .formular1c1 with the Range/Cells Method

  1. #1
    Grumpy Aero Guy
    Guest

    Using .formular1c1 with the Range/Cells Method

    Given a worksheet function that accepts a range of values, Average(Range),
    for example.....

    What is the method to use the .formulaR1C1 method within VBA to assign the
    Average formula to a cell, assuming that I want to define the formula from
    within VBA using the Range/Cells method(s).

    Example:

    Macro recorder provides the following code to define a cell using the
    Average function:

    ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C[-2]:R[12]C[-2])"

    Can this be done via something like:

    Dim Rindex as Integer
    Dim Cindex as Integer

    Rindex=3
    Cindex=7

    strArg= "=" & Range(Cells(Rowindex, Colindex),Cells(Rowindex+7,Colindex))
    ActiveCell.FormulaR1C1 = "=" & strArg

    and have it parse correctly....

    i.e. I would like to be able to manipulate the .FormulaR1C1 line via
    variables that get worked upon prior to setting the formula in the cell.

    Thank you in advance for the assistance.

    --


    Frank Bachman
    (Grumpy Aero Guy)




  2. #2
    Myrna Larson
    Guest

    Re: Using .formular1c1 with the Range/Cells Method

    If you tried to run the code you show here, you would find out that it won't
    work.

    For the first thing, you didn't specify the key word AVERAGE as part of the
    formula (typo?). For the 2nd, the word AVERAGE must be followed by a left
    parenthesis, then the ADDRESS of the range you are talking about, in R1C1
    format, then a closing parenthesis.

    What you wrote gets the 8 VALUEs from the range G3:G10, not the address.

    You have to generate the address of the range in the correct format, as a
    string, then incorporate that into the formula. I would do it like this

    Dim Rindex as Long 'row numbers can be > 32767
    Dim Cindex as Integer

    Rindex=3
    Cindex=7

    strArg = Cells(RowIndex, ColIndex).Resize(8,1).Address(,,xlR1C1)
    strArg= "=AVERAGE(" & strArg & ")"
    ActiveCell.FormulaR1C1 = strArg

    That should produced a formula with absolute references, =AVERAGE($G$3:$G$10).
    Look up the address property in Help if you want a formula with relative
    references.

    On Mon, 31 Jan 2005 23:30:03 -0500, "Grumpy Aero Guy" <fbachman@beer_me.com>
    wrote:

    >Given a worksheet function that accepts a range of values, Average(Range),
    >for example.....
    >
    >What is the method to use the .formulaR1C1 method within VBA to assign the
    >Average formula to a cell, assuming that I want to define the formula from
    >within VBA using the Range/Cells method(s).
    >
    >Example:
    >
    >Macro recorder provides the following code to define a cell using the
    >Average function:
    >
    >ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C[-2]:R[12]C[-2])"
    >
    >Can this be done via something like:
    >
    >Dim Rindex as Integer
    >Dim Cindex as Integer
    >
    >Rindex=3
    >Cindex=7
    >
    >strArg= "=" & Range(Cells(Rowindex, Colindex),Cells(Rowindex+7,Colindex))
    >ActiveCell.FormulaR1C1 = "=" & strArg
    >
    >and have it parse correctly....
    >
    >i.e. I would like to be able to manipulate the .FormulaR1C1 line via
    >variables that get worked upon prior to setting the formula in the cell.
    >
    >Thank you in advance for the assistance.



  3. #3
    Myrna Larson
    Guest

    Re: Using .formular1c1 with the Range/Cells Method

    Sorry, I see I changed the variable names in the code I wrote. Should be

    strArg = Cells(RIndex, CIndex).Resize(8,1).Address(,,xlR1C1)

    On Mon, 31 Jan 2005 23:34:03 -0600, Myrna Larson
    <anonymous@discussions.microsoft.com> wrote:

    >If you tried to run the code you show here, you would find out that it won't
    >work.
    >
    >For the first thing, you didn't specify the key word AVERAGE as part of the
    >formula (typo?). For the 2nd, the word AVERAGE must be followed by a left
    >parenthesis, then the ADDRESS of the range you are talking about, in R1C1
    >format, then a closing parenthesis.
    >
    >What you wrote gets the 8 VALUEs from the range G3:G10, not the address.
    >
    >You have to generate the address of the range in the correct format, as a
    >string, then incorporate that into the formula. I would do it like this
    >
    >Dim Rindex as Long 'row numbers can be > 32767
    >Dim Cindex as Integer
    >
    >Rindex=3
    >Cindex=7
    >
    >strArg = Cells(RowIndex, ColIndex).Resize(8,1).Address(,,xlR1C1)
    >strArg= "=AVERAGE(" & strArg & ")"
    >ActiveCell.FormulaR1C1 = strArg
    >
    >That should produced a formula with absolute references,

    =AVERAGE($G$3:$G$10).
    >Look up the address property in Help if you want a formula with relative
    >references.
    >
    >On Mon, 31 Jan 2005 23:30:03 -0500, "Grumpy Aero Guy" <fbachman@beer_me.com>
    >wrote:
    >
    >>Given a worksheet function that accepts a range of values, Average(Range),
    >>for example.....
    >>
    >>What is the method to use the .formulaR1C1 method within VBA to assign the
    >>Average formula to a cell, assuming that I want to define the formula from
    >>within VBA using the Range/Cells method(s).
    >>
    >>Example:
    >>
    >>Macro recorder provides the following code to define a cell using the
    >>Average function:
    >>
    >>ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C[-2]:R[12]C[-2])"
    >>
    >>Can this be done via something like:
    >>
    >>Dim Rindex as Integer
    >>Dim Cindex as Integer
    >>
    >>Rindex=3
    >>Cindex=7
    >>
    >>strArg= "=" & Range(Cells(Rowindex, Colindex),Cells(Rowindex+7,Colindex))
    >>ActiveCell.FormulaR1C1 = "=" & strArg
    >>
    >>and have it parse correctly....
    >>
    >>i.e. I would like to be able to manipulate the .FormulaR1C1 line via
    >>variables that get worked upon prior to setting the formula in the cell.
    >>
    >>Thank you in advance for the assistance.



  4. #4
    Grumpy Aero Guy
    Guest

    Re: Using .formular1c1 with the Range/Cells Method

    thankx for the insight !

    --


    Frank Bachman
    (Grumpy Aero Guy)


    "Grumpy Aero Guy" <fbachman@beer_me.com> wrote in message
    news:uyKYzaBCFHA.2076@TK2MSFTNGP15.phx.gbl...
    > Given a worksheet function that accepts a range of values, Average(Range),
    > for example.....
    >
    > What is the method to use the .formulaR1C1 method within VBA to assign the
    > Average formula to a cell, assuming that I want to define the formula from
    > within VBA using the Range/Cells method(s).
    >
    > Example:
    >
    > Macro recorder provides the following code to define a cell using the
    > Average function:
    >
    > ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C[-2]:R[12]C[-2])"
    >
    > Can this be done via something like:
    >
    > Dim Rindex as Integer
    > Dim Cindex as Integer
    >
    > Rindex=3
    > Cindex=7
    >
    > strArg= "=" & Range(Cells(Rowindex, Colindex),Cells(Rowindex+7,Colindex))
    > ActiveCell.FormulaR1C1 = "=" & strArg
    >
    > and have it parse correctly....
    >
    > i.e. I would like to be able to manipulate the .FormulaR1C1 line via
    > variables that get worked upon prior to setting the formula in the cell.
    >
    > Thank you in advance for the assistance.
    >
    > --
    >
    >
    > Frank Bachman
    > (Grumpy Aero Guy)
    >
    >
    >




+ 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