Announcement

Collapse
No announcement yet.

VBA code to move cell (contents)

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    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, 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.

    Comment


    • #3
      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, 21:06.

      Comment


      • #4
        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

        Comment


        • #5
          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

          Comment


          • #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)

            Comment


            • #7
              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.

              Comment


              • #8
                Re: VBA code to move cell (contents)

                Will do. thanks and cheers!

                Comment


                • #9
                  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.

                  Comment


                  • #10


                    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

                    Comment

                    Working...
                    X