VBA code works in Excel 2010 but not in 2013

[Lozza]

I have the following code which works fine in Excel 2010 but when a user runs it in excel 2013 it does not perform as expected - the formula is set in the whole Amount column, rather than just in the D2 cell

    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=Bal_Amt+[@[Amount]]"

where Bal_Amt is a named cell with some dollar value and [@[Amount]] is an Amount column in a table

I have also tried all of these to no avail.

    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=Bal_Amt+[@[Amount]]"
    Selection.FormulaR1C1 = "=Bal_Amt+[@[Amount]]"
    Worksheets("worksheet").Range("D2").FormulaR1C1 = "=Bal_Amt+[@[Amount]]"
    Worksheets("worksheet").Range("D2").Formula = "=Bal_Amt+[@[Amount]]"

Any tips / solutions are most appreciated

 

[NARAYANK991]

Hi ,

Is your amount column , column D ? What I mean is , is the cell D2 the first cell in the Amount column ?

If so , are you saying that the above formula is being copied to all the other cells in the Amount column ?

If this is the case , then it may be a setting in Excel Options rather than version specific.

Narayan

 

[Lozza]

hi Narayan, yes - column D is the Amount column. D2 is the first data cell in the Amount column. and yes, in Excel 2013 the formula is being written to ALL Amount cells, except the header.
Any idea what options setting it may be?

 

[NARAYANK991]

Hi ,

Under the Advanced tab , there is a checkbox labelled :

Extend data range formats and formulas

Narayan

 

[Lozza]

Thanks Narayan,

I've tried with the check box ticked and un-ticked, both work the same (correctly) for 2010 and incorrectly for 2013

 

[NARAYANK991]

Hi ,

I missed out on this earlier ; if D2 is the first cell in the Amount column , then how are you assigning a formula such as :

=Bal_Amt+[@[Amount]]

to D2 ?

Can you give the absolute cell references which the above formula reduces to ?

Have you tried inserting this manually in your table in Excel 2013 and seen what happens ?

Narayan

 

[Lozza]

Sorry - C is the Amount, D is a running balance, taking an initial balance Bal_Amt into account.
I have just installed 2013 and it is as the user says... the whole column gets filled with the value from D2!

 

[Lozza]

this is the result I'm trying to get:
C D
Amount
Balance
524.16 =Bal_Amt+[@Amount]
691.65 =D2 + C3
30 =D3 + C4
140.78 =D4 + C5
-80 =D5 + C6

 

[NARAYANK991]

Hi ,

Things seem to be getting a little clear !

In Excel , named ranges are always used in an absolute sense.

For example , suppose the cell D2 contains the Opening Balance ; your formula in column D would therefore be as follows :

D3 : =D2 + C3

D4 : =D3 + C4

D5 : =D4 + C5

D6 : =D5 + C6

The above is what would happen if you put in the formula =D2 + C3 in D3.

However , because you have created a named range called Bal_Amt , referring to D2 , Excel thinks you want it to be an absolute reference , with the result what you now get is :

D3 : =Bal_Amt + C3

D4 : =Bal_Amt + C4

D5 : =Bal_Amt + C5

D6 : =Bal_Amt + C6

What you need to do is delete the named range , and use =D2 + C3 in D3.

Narayan

 

[Lozza]

Not quite... Bal_Amt is a named range from another sheet....

I got it to work by the following:-
in 2013, I opened the workboook and ran the macros which generated the incorrect data.
I then overtyped D2 with "=Bal_Amt+[@[Amount]], which caused excel to fill the whole column
I optioned out of filling the whole column by clicking on the little icon that appears at the bottom right corner of the cell.
I then re-ran the macros and viola!

Very strange behaviour - the macro code is good as it was!

 

[NARAYANK991]

Hi ,

Thanks for the feedback , though I am still confused by how your formulae and macros are interacting. Anyway , as long as you have resolved your problem , that's what matters.

Narayan