sm789
06-11-2012, 09:28 PM
Hi I need to insert a code in a macro which needs to Sum x number of rows just above it. X is a variable which is stored in SortCount earlier in the macro (its the number of instances hence the number of rows). The range is also named as MyRange. I had earlier put
ActiveCell.FormulaR1C1 = "=sum(MyRange)"
which works but since this macro needs to run several times without disturbing previously stored data, I need to delete all names which then results an error for the above formula.
So either please suggest how to convert the above so that the formula has the actual cell range instead of the name or simply advise a code that will do something like:
ActiveCell.FormulaR1C1 = "=sum(R[-SortCount]c[0]:R[-1]C[0])"
Thank you for your help.
ActiveCell.FormulaR1C1 = "=sum(MyRange)"
which works but since this macro needs to run several times without disturbing previously stored data, I need to delete all names which then results an error for the above formula.
So either please suggest how to convert the above so that the formula has the actual cell range instead of the name or simply advise a code that will do something like:
ActiveCell.FormulaR1C1 = "=sum(R[-SortCount]c[0]:R[-1]C[0])"
Thank you for your help.