Excel's Macro-Recorder usage

Question

I would like to build a macro (using the Macro-Recorder) for a paste-special task: I have 3 groups of 3 elements in column A, and I would like to copy the transponse of each group starting from the second row (in columns C,D,E) as showed in the picture below.

I tried to do this by turning on the macro recorder while performing the paste-special operation once, but then the macro works only for the data I have on the cells where I record the macro (e.g., if I record the macro using the range A2:A4, that's the only place where input data can be transposed). Is there a proper way to use the macro-recorder for such task?

Answer 1

The macro recorder isn't the most useful tool. Most experienced VBA programmers use it only for quick experiments to discover some detail about how the Excel object model works and then throw away the actual code.

Having said that, it can still be useful, especially if you have neither the time nor the inclination to learn VBA.

To do the sort of thing that you want to do

1) On the developer tab select the option Use Relative References

2) Select the cell or range whose modification you are trying to record. Recorded macros which begin by selecting a cell are typically not as useful as recorded macros in which you have carefully thought of what you want selected before recording.

3) Turn on the macro and record your actions. See if it works.

4) If it doesn't work -- see if you can edit the code.

5) Ask a focused question on Stack Overflow if worse comes to worse.

The way you use such a recorded macro is to first select the cells that you want to change and then invoke the macro. It will perform on the selected cells the same action that you did on the other selected cells -- but with all offsets, etc. relative to your current selection.