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,618

    Re: VBA code to move cell (contents)

    You need .Offset for your example

    VB:
    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 20:52.
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    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:

    VB:
    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 21:06.

    Excel Video Tutorials / Excel Dashboards Reports


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

    Re: VBA code to move cell (contents)

    Try this

    VB:
    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,566

    Re: VBA code to move cell (contents)

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

    VB:
    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,618

    Re: VBA code to move cell (contents)

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

    Roy

    New users should read the Forum Rules before posting

    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
    3,066

    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, 08:30
  2. Move cell contents & delete rows
    By nmrosen in forum Excel General
    Replies: 2
    Last Post: May 23rd, 2006, 03:36
  3. User Select cell and copy/move contents
    By sjshah in forum Excel General
    Replies: 1
    Last Post: September 2nd, 2005, 23:47
  4. Code to amalgamate cell contents
    By Trickster in forum Excel General
    Replies: 4
    Last Post: July 27th, 2005, 08:43
  5. Delete cell contents based on cell contents length
    By jefsboys in forum Excel General
    Replies: 4
    Last Post: June 13th, 2004, 18: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