locked
Cell formulae not being inserted as I expect RRS feed

  • Question

  • I have an Access application with VBA code for doing various things, including building a report in Excel.

    In the report-building code, I have the following:

    Dim rng As Excel.Range

    Dim shtMySheet As Excel.Worksheet

    Dim intMonthLoop As Integer

                    Set rng = shtMySheet.Cells(5, intMonthLoop + 2)
                    rng.FormulaR1C1 = "=ABS(R[-2]C-R[-1]C)"

    I run the code, and everything in the report is fine, except where the formula was inserted.  Here's what I see in the Formula Bar when I select the cell:

    (In Column 2, Row 5)

    =ABS(R[65530]C[-1]-R[65531]C[-1])

    (In Column 3, Row 5)

    =ABS(R[65530]C[-2]-R[65531]C[-2])

    And so on across the sheet.  What's wrong with specifying a negative relative row reference?  And why does it try to change my column reference?

    The same thing is going on at row 10, but then I get

    =ABS(R[65525]C[-1]-R[65526]C[-1])

    and so on.

    I'm using relative referencing because it's happening in a loop, with the variable intMonthLoop increasing across the sheet. 

    Monday, December 17, 2012 6:07 PM

All replies

  • On Mon, 17 Dec 2012 18:07:12 +0000, AndyE1959 wrote:
     
    >
    >
    >I have an Access application with VBA code for doing various things, including building a report in Excel.
    >
    >In the report-building code, I have the following:
    >
    >Dim rng As Excel.Range
    >
    >Dim shtMySheet As Excel.Worksheet
    >
    >Dim intMonthLoop As Integer
    >
    >
    >
    >                Set rng = shtMySheet.Cells(5, intMonthLoop + 2)
    >                rng.FormulaR1C1 = "=ABS(R[-2]C-R[-1]C)"
    >
    >
    >
    >I run the code, and everything in the report is fine, except where the formula was inserted.  Here's what I see in the Formula Bar when I select the cell:
    >
    >(In Column 2, Row 5)
    >
    >=ABS(R[65530]C[-1]-R[65531]C[-1])
    >
    >(In Column 3, Row 5)
    >
    >=ABS(R[65530]C[-2]-R[65531]C[-2])
    >
    >And so on across the sheet.  What's wrong with specifying a negative relative row reference?  And why does it try to change my column reference?
    >
    >The same thing is going on at row 10, but then I get
    >
    >=ABS(R[65525]C[-1]-R[65526]C[-1])
    >
    >and so on.
    >
    >I'm using relative referencing because it's happening in a loop, with the variable intMonthLoop increasing across the sheet. 
     
    I cannot reproduce your problem in Excel 2007, using VBA from Excel.
    Depending on my worksheet settings, in Col2 Row5, after running your assignment code line, I see either
     
    =ABS(B3-B4)
    or
    =ABS(R[-2]C-R[-1]C)
     
    I suspect there is something going on with either your environment, or with parts of your code you have not shared with us.
     

    Ron
    Monday, December 17, 2012 8:33 PM