Recording a macro (Excel 2000)

Topic

New Reply

I’m trying to record a couple of macros to help me edit a document. The document is all text.

Text that should be in one cell is often divided into several adjacent cells, and I need to get all of this text into one cell. So I need two macros, one to select and cut the text from a cell, and the other to paste that text onto the end of the current contents of another cell.

The first macro I recorded was to select and cut the contents of a cell, but it didn’t work as I expected.

The keystrokes I recorded are:

F2 (to Edit the cell)
Ctrl-Shift-Home (to select the entire cell)
Ctrl-X (to cut onto the clipboard)

Now at that point, I would like to exit Edit mode and return to Ready mode (without leaving the cell). The only way I know to do this is either to press Tab or click in another cell (then move back to the original cell). But when I do that, the cell I end up in gets designated as the cell where the macro always ends. Excel thinks I always want it to end up on that particular cell, wheras I want it to end up in whatever cell I’m working on (i.e., I want it to think in relative positioning, not absolute).

I also discovered that when I run the macro a second time, it pastes the first selection rather than the current selection, i.e., the clipboard is not getting updated.

So my questions are:

1. Is there a way to exit Edit mode (and return to Ready mode) without leaving a cell?
2. How do I tell Excel to treat the macro’s keystrokes in a relative positioning sense, not an absolute sense?

Reply | Quote

Replies

About question 2: when you start recording a macro, you should see a toolbar with only 2 buttons: a “Stop Recording” button and a “Relative References” button. If you don’t see it, right click any toolbar while recording, and select “Stop Recording”. This action will be recorded too, but can be edited out of the macro.

I don’t think that what you want can be recorded; the macro will have to be written directly in the Visual Basic Editor. Here is an example. It will combine the values of all cells in the selection and place it into the active cell.

Sub CombineCells()
Dim oCell As Range
Dim strResult As String
For Each oCell In Selection
strResult = strResult & oCell.Value
Next oCell
Selection.ClearContents
ActiveCell.Value = strResult
Set oCell = Nothing
End Sub

Reply | Quote

Thanks, Hans. Yes, my Stop Recording toolbar was not visible, so I didn’t know about the Relative Reference. That fixes the relative positioning problem.

But even with Relative Reference on, the macro always pastes the original text selection, not the current one. Too bad Relative Reference doesn’t affect what the clipboard picks up each time.

I appreciate the code you wrote for me. I’ll give it a try.

Thanks for your help.

Russ

Reply | Quote

Thanks, Hans. Yes, my Stop Recording toolbar was not visible, so I didn’t know about the Relative Reference. That fixes the relative positioning problem.

But even with Relative Reference on, the macro always pastes the original text selection, not the current one. Too bad Relative Reference doesn’t affect what the clipboard picks up each time.

I appreciate the code you wrote for me. I’ll give it a try.

Thanks for your help.

Russ

Reply | Quote

Wow, your macro works perfectly! Thanks.

Reply | Quote

Wow, your macro works perfectly! Thanks.

Reply | Quote

About question 2: when you start recording a macro, you should see a toolbar with only 2 buttons: a “Stop Recording” button and a “Relative References” button. If you don’t see it, right click any toolbar while recording, and select “Stop Recording”. This action will be recorded too, but can be edited out of the macro.

I don’t think that what you want can be recorded; the macro will have to be written directly in the Visual Basic Editor. Here is an example. It will combine the values of all cells in the selection and place it into the active cell.

Sub CombineCells()
Dim oCell As Range
Dim strResult As String
For Each oCell In Selection
strResult = strResult & oCell.Value
Next oCell
Selection.ClearContents
ActiveCell.Value = strResult
Set oCell = Nothing
End Sub

Reply | Quote