Results 1 to 7 of 7

exchange data in cells

This is a discussion on exchange data in cells within the Excel Questions forums, part of the Question Forums category; Hello! Is there a command / tool in Excel wherein you can interchange data from two cells? To illustrate.. Cell ...

  1. #1
    New Member
    Join Date
    Jan 2003
    Posts
    5

    Default exchange data in cells

    Hello!

    Is there a command / tool in Excel wherein you can interchange data from two cells? To illustrate..

    Cell A1 contains value X.
    Cell B1 contains value Y.

    What quick command would make it easy to interchange the two so that..

    Cell A1 would contain value Y.
    Cell B1 would contain value X.

    Any help would be greatly appreciated!

    Barns

  2. #2
    MrExcel MVP HalfAce's Avatar
    Join Date
    Apr 2003
    Location
    Alaska
    Posts
    9,160

    Default Re: exchange data in cells

    Hi Barns, welcome to the board.
    How's this grab ya?
    Code:
    Sub DoTheSwap()
    i = [A1].Value
    j = [B1].Value
    [A1] = j
    [B1] = i
    End Sub
    Dan
    Does anyone else find it kinda cruel that they spelled the word lisp with an s?


  3. #3
    New Member
    Join Date
    Jan 2003
    Posts
    5

    Default Re: exchange data in cells

    Hi Dan,

    Macro Works fine, but I'm limited to A1 and B1 cells only.

    Is there a way wherein I can just highlight any two cells and run a command to do the swap? (non-macro way would be better although not really necessary)

    Thanks!

    Barns

  4. #4
    Board Regular
    Join Date
    Sep 2002
    Location
    Simsbury CT USA
    Posts
    1,542

    Default Re: exchange data in cells

    Hi:

    Have a look at this nice approach from Yogi...

    http://www.mrexcel.com/board2/viewto...ht=swap+values

    plettieri

  5. #5
    MrExcel MVP HalfAce's Avatar
    Join Date
    Apr 2003
    Location
    Alaska
    Posts
    9,160

    Default Re: exchange data in cells

    I don't know of a way to do this with a formula, but then I suck at formulas.
    I also can't do that with 2 cells selected in VBA, but I can do it with one cell selected and the other always being a specified distance from the selection (ie, 1 to the right, or 7 below (or 5 rows above & 2 columns to the left), something like that, as long as it's constant. Will something like that do ya?
    Does anyone else find it kinda cruel that they spelled the word lisp with an s?


  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579

    Default Re: exchange data in cells

    Hi there

    Here is another variation if you do not want to hardwire the cells.
    Before running the macro you must select 2 separate cells using Control.

    Sub SwapSelection()
    On Error GoTo Out
    If Selection.Cells.Count <> 2 Then Exit Sub
    Dim str, str1, str2 As String
    str = Selection.Address(rowabsolute, columnabsolute)
    str1 = Left(str, WorksheetFunction.Find(",", str) - 1)
    str2 = Mid(str, WorksheetFunction.Find(",", str) + 1, 7)

    x = Range(str1).Value
    y = Range(str2).Value

    Range(str1).Value = y
    Range(str2).Value = x
    Out:
    End Sub


    MERGED CELLS. If merged cells are involved remove this line from the code:
    If Selection.Cells.Count <> 2 Then Exit Sub

    regards
    Derek

  7. #7
    New Member
    Join Date
    Jan 2003
    Posts
    5

    Default

    I've got it!

    Thanks for all your help, although the solutions provided are quite complicated. (i've no knowledge in VBA) I really appreciate it!

    Barns

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com