Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    L.A., California, USA
    Posts
    267
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Recording a macro (Excel 2000)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recording a macro (Excel 2000)

    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

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    L.A., California, USA
    Posts
    267
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Recording a macro (Excel 2000)

    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

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    L.A., California, USA
    Posts
    267
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Recording a macro (Excel 2000)

    Wow, your macro works perfectly! Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •