Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have an Access database that is creating a workbook in Excel. Several of the columns are used to rank the data in the previous column. I am currently passing this code for the rank column
    wks.Cells(y, x).FormulaR1C1 = "=RANK(RC[-1],C[-1])"

    I would like to change it to
    wks.Cells(y, x).FormulaR1C1 = "=RANK(RC[-1],R[" & (6 - y) & "]C[-1]:R[" & (10 - y) & "]C[-1])" so I can have more data on the tab.

    I have five rows that show Division data. I need to add below that 28 forws that show Regional data. With the first example, the complete column is used for the ranking function. The second example ranks the correct rows, but when resorted by Overall Rank the other rank columns do not have the correct formula. Excel corrects the formula based on the movement from one row to the next.

    Example...
    The above code is converted to
    =RANK(E6,E4:E8)

    I know if I add the $ to the row number it should not change when resorted, but am not sure how to go about that in my code string.


    Thanks for any help with this issue.
    Richard

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Richard,

    If you leave out the brackets [ ] in it becomes a fixed reference in R1C1 style.
    e.g. R1C[-2] would always refer to Row 1.
    R[-1]C2 would always refer to Col 2 (B)
    RC2 would always refer to the Current Row and Col 2 (B), etc.
    Just code your VBA string accordingly.

    Good Luck.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That seems to be working.


    Thanks for your help.
    Richard

Posting Permissions

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