My macro is copying data in error

**steinfm** · 12-02-2010, 06:45 PM

I am trying to create a macro to use the data "Text to column" function to convert a column of data to text. (Just so you understand how I'm creating the macro - I highlight the column (A) and hit ALT DEWNNTF)

A B C D
INV USER MNO SCRNO
9242852 38026 6966 A2046443
9243250 06046 6948 BS047985
9243293 20123 7594 AM051514
9243282 31153 2822 AU045045
9243280 04218 2552 BU122874
9243260 02031 1223 A0232935

What ends up happening after I record the macro (say on column A) and try it on another column (say B) is that the data from column B is copied to column A. If I do column C, then A has the data of column C.

A B C D
USER USER MNO SCRNO
38026 38026 6966 A2046443
38026 06046 6948 BS047985
20123 20123 7594 AM051514
31153 31153 2822 AU045045
04218 04218 2552 BU122874
02031 02031 1223 A0232935

I don't know if it's the way I'm creating the macro, but I'm not sure why it's copying the infomation and not sure how to create the mcro correctly to do what I want.

Can anyone help?

**Maistrye** · 12-02-2010, 07:47 PM

Hi,

The macro recorder is good, but it does have limitations. In this case, I think what you are looking for could be fixed by re-recording the macro in a slightly different fashion. (Alternatively, you can look at the code itself and make a fix)

To do what you're trying to do, I'd recommend the following steps:
(A) Highlight the column.
(B) Enable the Macro Recorder.
(C) Click the button on the Macro Recorder that says "Relative Reference" (It should be highlighted/enabled).
(D) Do the Text-To-Columns thing.
(E) Unclick the "Relative Reference" button and end the macro.

Then, to use, select a cell in the column in question.

S

**Richard Buttrey** · 12-02-2010, 08:40 PM

Hi,

It's always difficult to guess what you've done which is why an example workbook is worth a thousand words, but probably because you changed the "Columns("A:A").Select" bit to "Columns("B:B").Select", but you didn't change the "Destination:=Range("A1")".

But a more fundamental question is why, if you've already parsed a single column A into 4 columns, do you want to parse column B? If this needs to be split further why not make sure the original parse statement handles it.

Regards