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