Ask Your Question

A problem with a macro containing the use of CTRL+X

asked 2015-06-15 23:54:15 +0200

lemelinm gravatar image

In one cell, I have this: Albert Einstein I need a macro to rewrite it this way: Einstein, Albert

I recorded a macro with the combination of the following keyboard keys:

F2 -> Home -> CTRL+Shift+right arrow (Which highlight the name Albert and the space before E) -> CTRL+X -> End -> , -> CTRL+V -> Return

I save the macro in the document. It work the first time but, when I start the macro for the next name, it copy the same name. If I have on another cell the word: Paul Dirac and I execute the macro, I get : Einstein, Albert

Not very practical. Can you help me? Keep in mind that this is the first time I do a macro.

Thank you in advance for your help.

edit retag flag offensive close merge delete


The "macro" does'nt work the first time. If you had changed the content ou your target cell meanwhile, the "macro would fail the first time, too. That's because the so called recorder isn't a kind of a key logger as was the case in times of Works for DOS (TM). It will place the once copied text simply as a constant onto the 'args2(0).Value' ticket. The targe cell's address will also be a constant. Did you try to read your "macro"?

Lupp gravatar imageLupp ( 2015-06-16 00:30:34 +0200 )edit

What do you mean by "read the macro"? Remember, I am a beginner.

lemelinm gravatar imagelemelinm ( 2015-06-25 05:44:40 +0200 )edit

A recorded "macro" most likely won't work as hoped for. You have to use 'Tools' > 'Macros' > 'Organise Macros' > 'LibreOffice Basic' > choose the container where the macro was saved to. Then you can read (study) and edit the macro code. In your case you won't achieve a satisfying result. A proceeding as you tried it cannot create a useful macro. But you may find out the reasons, at least. The cell address which you expect to be a parameter is actually a constant in the macro's body. e.g.

Lupp gravatar imageLupp ( 2015-06-25 20:03:55 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2015-06-16 00:23:28 +0200

Lupp gravatar image

updated 2015-06-16 00:42:46 +0200

Everybody is asking for a "macro" for everything. The fundamental concept of spreadsheet software is using formulae polling data from cells and processing them to produce an output into exactly that cell where the formula is entered in.

Assuming your Albert Einstein is dwelling A11

=MID(A11;FIND(" ";A11)+1;999)&", "&LEFT(A11;FIND(" ";A11)-1)

placed in an otherwise unused cell is the "macro" you need.

As tasks of this kind often are to repeat for some thousand rows containing equally structured data there is also a tool named 'Find & Replace' suitable for this: Open the tool's dialogue, allow for regular expressions ('Current selection only' may apply), put (^[^ ]+) (.*$) into 'Search For' and $2, $1 into 'Replace With', and click 'Replace All'.

edit flag offensive delete link more


Where do I put that red line? And how do I use it. Remeber, I am a beginner.

lemelinm gravatar imagelemelinm ( 2015-06-25 05:47:37 +0200 )edit

The 'Edit' > 'Find & Replace' tool has two small lines accepting input. They are labelled 'Search For' and ' 'Replace With'. Under 'Options' the 'Regular expressions' must be checked. If you want to process the current selection only you have to check that, too. You also should read, at least, the help text on regular expressions.

Lupp gravatar imageLupp ( 2015-06-25 19:54:56 +0200 )edit

When I put the red line in an unused cell, It write in the cell #NAME? What should be the format of the cell. Moreover, if I use find and replace, do I need the red line anyway or do I only write the «put (^[^ ]+) (.*$) into 'Search For' and $2, $1 into 'Replace With». I am sorry for not understand it easily.

lemelinm gravatar imagelemelinm ( 2015-07-16 16:35:20 +0200 )edit

The first line of code I posted ("red line"?) pasted into a cell will surely work in an English locale.if the name to process is placed in A11. The error message "#NAME?" might indicate that a function name was misspelled.

Using 'F&R' only the two "strange" pieces of code placed in the last two lines of my answer will work. You might want to consider reading the help text about 'Find & Replace'.

Lupp gravatar imageLupp ( 2015-07-18 12:10:09 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-06-15 23:54:15 +0200

Seen: 110 times

Last updated: Jun 16 '15