Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: VBA code to move cell (contents)

  1. #1
    Join Date
    11th May 2005
    Posts
    6

    VBA code to move cell (contents)

    I need a macro/code that will allow me to "find" a cell with certain contents and then move that cell up and over x number of rows/columns. I can "find" the cells easily enough, but don't know code to move the cell - for example up 1 row, over 1 column. I've tried dragging and having this recorded in the macro but it assigns the specific cell destination I used to record the macro.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,291

    Re: VBA code to move cell (contents)

    You need .Offset for your example

    Code:
    Range("a5").Offset(-1,1).Value= Range("a5").Value
    Range("a5").ClearContents 'will clear out A5
    will put A5's value into B4
    Last edited by royUK; May 11th, 2005 at 21:52.
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  3. #3
    Join Date
    11th May 2005
    Posts
    6

    Re: VBA code to move cell (contents)

    but the catch is...I'm moving a cell that was "found" (ie. using the find function). How can I specify that the cell to offset is the result of the find function. Maybe it will help to post my code thus far:

    Code:
    Sub Macro3()
    '
    ' Macro3 Macro
    ' Macro recorded 5/11/2005 by barry.boland
    '
    ' Keyboard Shortcut: Ctrl+a
    '
        Cells.Find(What:="domicile", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        Selection.Cut Destination:=Range("B37")
        Range("B37").Select
    End Sub
    Edit(WillR): added code tags... please use, thanks
    Last edited by Will Riley; May 11th, 2005 at 22:06.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,680

    Re: VBA code to move cell (contents)

    Try this

    Code:
    Option Explicit
    
    Sub MoveTheCell()
    Dim rngFound As Range
    
    Set rngFound = Cells.Find(What:="domicile", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        rngFound.Cut Destination:=rngFound.Offset(-1, 1)
    Set rngFound = Nothing
    End Sub
    Kind Regards, Will Riley

    LinkedIn: Will Riley

  5. #5
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,680

    Re: VBA code to move cell (contents)

    To add some error message to the code would also be useful

    Code:
    Option Explicit
    
    Sub MoveTheCell()
    Dim rngFound As Range
    
    Set rngFound = Cells.Find(What:="domicile", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If Not rngFound Is Nothing Then
        rngFound.Cut Destination:=rngFound.Offset(-1, 1)
    Else: MsgBox "The Value Could not be found"
    End If
    Set rngFound = Nothing
    End Sub
    Kind Regards, Will Riley

    LinkedIn: Will Riley

  6. #6
    Join Date
    11th May 2005
    Posts
    6

    Re: VBA code to move cell (contents)

    that works, thanks. One snag... my intention is to use this many times in a row, and when I execute excel "finds" the same cell that was moved and moves it again. I can manually move the active cell down a few rows to circumvent this - this will certainly save me time - but can you think of another way around this? (I apologize for my lack of excel prowess)

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,291

    Re: VBA code to move cell (contents)

    You need .Findnext. look at the VBA help files
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  8. #8
    Join Date
    11th May 2005
    Posts
    6

    Re: VBA code to move cell (contents)

    Will do. thanks and cheers!

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    20th October 2015
    Posts
    1

    Re: VBA code to move cell (contents)

    Hello, i am in also in need of a macro that moves cells i have in column two and three, to one and two respectively based on a value in column i of the same row. Is this possible? Thank you.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    14th November 2013
    Location
    Cheshire, UK
    Posts
    2,182

    Re: VBA code to move cell (contents)

    Hi RJREAD, welcome to Ozgrid.

    Please do not post your own question in another thread - this is known as "thread-hijacking" and is against the rules. You can start a new thread for your question and provide a link to this one if you feel it would help.

    Regards,

    S O

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 7
    Last Post: May 5th, 2007, 09:30
  2. Move cell contents & delete rows
    By nmrosen in forum EXCEL HELP
    Replies: 2
    Last Post: May 23rd, 2006, 04:36
  3. User Select cell and copy/move contents
    By sjshah in forum EXCEL HELP
    Replies: 1
    Last Post: September 3rd, 2005, 00:47
  4. Code to amalgamate cell contents
    By Trickster in forum EXCEL HELP
    Replies: 4
    Last Post: July 27th, 2005, 09:43
  5. Delete cell contents based on cell contents length
    By jefsboys in forum EXCEL HELP
    Replies: 4
    Last Post: June 13th, 2004, 19:49

Bookmarks

Posting Permissions

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