Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

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
    17,427

    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

    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

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

    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

    Web Presence:
    Personal: The Trouble With Data
    LinkedIn: Will Riley

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

    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

    Web Presence:
    Personal: The Trouble With Data
    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
    17,427

    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

    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


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 HELP
    Replies: 2
    Last Post: May 23rd, 2006, 03:36
  3. User Select cell and copy/move contents
    By sjshah in forum EXCEL HELP
    Replies: 1
    Last Post: September 2nd, 2005, 23:47
  4. Code to amalgamate cell contents
    By Trickster in forum EXCEL HELP
    Replies: 4
    Last Post: July 27th, 2005, 08:43
  5. Delete cell contents based on cell contents length
    By jefsboys in forum EXCEL HELP
    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