1
How to create a macro in Excel that does the same calculations when I move from column to column.?
In Excel, we have to run a array of calculations for each day of a month. Each day is subsequent to to a brand new column. we can emanate a macro which functions for a stream day/column, though when we come in a subsequent day, how do we cgange it to run a same calcs for a stream day/column?
See Dear, if you are recording any macro using 2007 version of Excel then it is easier for you to record a macro and to run from your selection.
Before recording any macro you must know that while recording any macro Excel provides an option to use the Relative and Absolute references.
what are Absolute and Relative References.
if you record any macro using absolute references then macro will run through only exact ranges wherever you are selecting
like if you select A1 and after you select B1 then while running macro only A1 and B1 will be selected.
but if you record any macro using Relative referances then macro will run through the location in reReferenceo your first selection
like if you select A1 and after you select B1 then while running macro only if your pointer is on C1 then D1 will be selected.
Before recording any macro click on Developer Tab – and Check "Use Relative ReReferencesmenu Option.
another example: suppose you have data in Column "A"
then you record a macro use absolute reReferencend then you run it. It will use rereferencesf Column A Only regardless wherever your current selection but if you record macro using Relative reReferencehen and after recording it if your current selection is somewhere in Column then the macro will run in column F.
Example of Absolute Referance
Range("A2").Select
ActiveCell.FormulaR1C1 = "123123"
Range("A3").Select
ActiveCell.FormulaR1C1 = "123123"
Range("A4").Select
ActiveCell.FormulaR1C1 = "123123"
Range("A5").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
Range("A6").Select
Example of Relative Referance
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "12345"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "12345"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "1234"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(1, 0).Range("A1").Select
so next time you create or record macro do consider the requirement of you macro and accordingly use absolute & relative referances.
in your case you can just select a column and run a macro if you have recorded using Relative Referances.
(*.*)
Was this answer helpful?
LikeDislike