Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I have a need to create a formula after having populated a number of rows across a number of columns.

    Is it just a matter of doing the following:
    mySheet.cells("D12")..formula = "=SUM(1stRowCol:lastRowCol)"
    where 1st rowcol could be D4 and the last rowcol could be D11? Can you use the actual row number rather thean the letter (eg D)?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Perhaps you could set the FormulaR1C1 property:

    mySheet.Range("D12").FormulaR1C1 = "=SUM(R1C:R[-1]C)"

    R1C means: the cell in the first row, and because no column number is specified, in the same column as the cell containg the formula (in this example, column 4).

    R[-1]C means: the cell in the row above the cell with the formula, and in the same column.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='patt' post='768958' date='03-Apr-2009 08:58']I have a need to create a formula after having populated a number of rows across a number of columns.

    Is it just a matter of doing the following:
    mySheet.cells("D12")..formula = "=SUM(1stRowCol:lastRowCol)"
    where 1st rowcol could be D4 and the last rowcol could be D11? Can you use the actual row number rather thean the letter (eg D)?[/quote]
    That is a nifty solution, thank you.

    Is there a way to use a column number (eg 4) instaead of D as in D12? I would be using variables instead of numbers of course, eg iRowNo and iColNo.
    Can i use mySheet.Range(4,12).FormulaR1C1

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use mySheet.Cells(12, 4) instead of mySheet.Range("D12"). Note that the first argument of Cells is the row number and the second one is the column number.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='768969' date='03-Apr-2009 10:30']You can use mySheet.Cells(12, 4) instead of mySheet.Range("D12"). Note that the first argument of Cells is the row number and the second one is the column number.[/quote]
    Thank you very much.
    That will keep people happy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •