Formula with relative references without selecting cell

[elnds]

Hi,

I'm trying to use VBA to put a formula in a cell which will average the values in the last 6 columns. The following works OK:

TableSheet.Cells(WorkingRow, LastColumn + 1).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-6]:RC[-1])"

(WorkingRow and LastColumn are integers) but I'd like to avoid selecting the target cell. If I try the following:

TableSheet.Cells(WorkingRow, LastColumn + 1).FormulaR1C1 = "=AVERAGE(RC[-6]:RC[-1]"

nothing is inserted in the target cell (I'm guessing because the relative references refer to the currently selected cell?)

Any suggestions gratefully recieved - thanks

 

[steve the fish]

You have missed closing the brackets after RC[-1]

 

[enijhuis]

Hi,

The error is due to the fact that a closing comma is missing from your formula.
Try:
TableSheet.Cells(WorkingRow, LastColumn + 1).FormulaR1C1 = "=AVERAGE(RC[-6]:RC[-1])"

 

[elnds]

Doh! Stared it for hours for something simple like that. Did the trick, thanks

 

[Jonmo1]

Glad you found solution..

FYI, I'd have to guess that you had an On Error Resume Next type of statement in there.
That was hiding the error you should have been seeing when entering the formula with the missing )
That's why you said it simply didn't enter the formula, instead of "I get an error"..


When troubleshooting, it's a good idea to remove those error handling lines.

 

[enijhuis]

I suspected the same thing.
Instead of manually removing error handling lines you can also change the error trapping setting:
-Go to the VBA editor (Alt+F11)
-From the menu select Tools ; Options
-Tab General ; Error Trapping ; Break on all errors

http://redcirclesoftware.com/tmp/VBA_Options_ErrH.png