Excel的宏记录器用法 [英] Excel's Macro-Recorder usage

问题描述

我想构建一个宏（使用宏记录器）进行粘贴特殊任务：我在列A中有3组3个元素，我想从第二个列开始复制每个组的转发行（列C，D，E），如下图所示。

我试图通过打开宏记录器在执行粘贴特殊操作一次，但是宏仅对我在记录宏的单元格上的数据工作（例如，如果我使用范围A2：A4记录宏，这是唯一的地方其中可以转置输入数据）。是否有正确的方法来使用宏记录器进行此类任务？

解决方案

宏记录器不是最有用的工具。大多数经验丰富的VBA程序员只用于快速实验来发现Excel对象模型的一些细节，然后丢弃实际代码。

说完，仍然是有用的，特别是如果你没有时间和倾向来学习VBA。

做你想做的事情

1）在开发者标签上，选择选项使用相对引用

2 ）选择要进行修改的单元格或范围。通过选择单元格开始的记录宏通常不会像录制的宏一样有用，在记录的宏中，您仔细考虑了录制之前要选择的内容。

3）打开宏并记录您的操作。看看它是否有效。

4）如果不起作用 - 看看是否可以编辑代码。

使用这样一个记录的宏的方式是首先选择你想要的单元格改变然后调用宏。它将对所选单元格执行与您在其他选定单元格上执行的相同操作 - 但与当前选择相关的所有偏移量等。

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?

解决方案

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.

这篇关于Excel的宏记录器用法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！