How to create a macro in Excel that does the same calculations when I move from column to column.?

Question

In Excel, I have to run a series of calculations for every day of the month. Each day is equal to a new column. I can create a macro that works for the current day/column, but when I come in the next day, how do I modify it to run the same calcs for the current day/column?

Answer 1

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.



(*.*)

Answer 2

Cozmosis is right, Ajax is wrong, whether you click Relative or Absolute while recording the macro won't make any difference as to your specific question / dilemma.



You'll need to actually record the macro first, then edit the code to loop through, say, columns A through Z, or however many days/columns there are.



IN the loop, there will be a spot where you'll have your code that deals with each column.



Absolute/Relative references have to do with Formulas, not with looping through many items in the sheet.

Answer 3

The Macro will have to get the actual day, then search for the column that corresponds to this day.



Or, easier, just select the column and have the Macro work on the selected column, not a particular day.

Answer 4

You would have to modify your macro. Paste the macro code and give an example of what you want it to do from day to day, and we can modify the macro for you.